Another Priority interface - multiple import files

I had to write another interface/importing program for Priority. In this case, an external optimising program creates several output files, each containing the id and quantity of parts (wood sheets) which have to be cut. There are three problems to be overcome (stated in order of their occurrence, not in order of importance):

1. There could be many files per day
2. The files are in Excel whereas Priority reads tab delimited files
3. An interface has to be created within Priority

Problem 3 is near enough the same problem as the one which I solved a few weeks ago. Whilst writing such interfaces are new for me, they're quite straight-forward once understood, and the new interface didn't really provide any challenges which I hadn't overcome the previous time (there are one or two innovations but very minor).

The first two problems occupied me the most. As it happens, the other day I found tucked away at the back of the Priority Programming Manual (yes, there is such a thing) a routine for finding all the files in a given directory (I remembered reading about this); what I didn't remember was that the routine shown also loads the files into an interface, which was exactly what I needed.

But before I could deal with the Priority problem, I had to deal with converting an Excel file into a tab delimited file. After looking up similar questions on the Internet, I cobbled together some code which (via automation) started Excel, loaded the required file, determined how many rows and columns the file had, then created a text file in the required format. After playing around with this for about an hour, each time receiving an obscure error message which resulted in Excel staying in memory, I realised that I had been over-engineering. I could simply tell Excel to output a tab delimited file! Then I had problems with the name of the file, but those were easily overcome.

Here is the Priority code as it was yesterday (derived almost without change from the example)

    /* load a list of files from directory DIR into table ST6 */
    EXECUTE FILELIST :DIR, :ST6, :MSG;  
    LINK STACK6 TO :ST6;
    DECLARE FILES CURSOR FOR
    SELECT NAME FROM STACK6 
    WHERE NUM <> 0;
    OPEN FILES;
    GOTO 300 WHERE :RETVAL <= 0;       
    LABEL 100;
    FETCH NAME INTO :NAME;
    GOTO 200 WHERE :RETVAL <= 0;       /* no more files */
    :MYFILE = STRCAT (:DIR, '/', :NAME);
    /* call my Delphi program to convert XLS to TAB */
    EXECUTE WINAPP 'X:\SYSTEM', '-w', 'XLS2TAB.EXE', :MYFILE;  
    /* do something with the tab delimited file */
    LOOP 100;
    LABEL 200;
    CLOSE FILES;
    LABEL 300; 

Ignoring the low level language which is required for programming in Priority, what the above code is doing is getting a list of files in a given directory, then the filter program is called for every file in that directory. I would then have to manipulate the filenames, so that instead of 'file.xls', I would have to input 'file.txt' into the interface. One way of doing this would be to delete each xls file in turn, then call the FILELIST program again to build a new list of filenames.

It occurred to me yesterday evening that there was a better way to skin a cat. This version involved writing a filter program which converts all the xls files in a directory to txt. This approach is going to be faster for two reasons:

1. the external filter program will be called only once instead of once per file
2. Excel will be invoked only once instead of once per file.

The filter program is exceedingly simple:

$APPTYPE CONSOLE}

uses SysUtils, comobj, ActiveX, variants;

const
 xlText = -4158;

var
 i: integer;
 xlApp, wb: variant;
 rec: tsearchrec;
 fn, s, dir: string;

begin
 if paramcount = 1 then
  begin
   CoInitialize (nil);
   xlApp:= CreateOleObject ('Excel.Application');
   xlApp.visible:= false;
   xlApp.displayalerts:= false;

   dir:= IncludeTrailingPathDelimiter (paramstr (1));
   if findfirst (dir + '*.xls', faAnyfile - faDirectory, rec) = 0 then
    repeat
     fn:= dir + rec.name;
     i:= pos ('.', fn);
     s:= copy (fn, 1, i) + 'txt';
     wb:= xlApp.workbooks.open (fn);
     wb.saveas (filename:= s, FileFormat:= xlText, CreateBackup:=False);
     wb:= unassigned;
     deletefile (fn);
    until FindNext (rec) <> 0;
   FindClose (rec);

   xlApp.quit;
   xlApp:= unassigned;
   CoUninitialize;
  end
end.

I still had to use the FILELIST command in Priority, but only once; the conversion from xls to txt had been moved out of the loop.

    /* call my Delphi program to convert XLS to TAB */
    EXECUTE WINAPP 'X:\SYSTEM', '-w', 'MXLS2TAB.EXE', :DIR; 
    EXECUTE FILELIST :DIR, :ST6, :MSG;  
    LINK STACK6 TO :ST6;
    DECLARE FILES CURSOR FOR
    SELECT NAME FROM STACK6 
    WHERE NUM <> 0;
    OPEN FILES;
    GOTO 300 WHERE :RETVAL <= 0;   
    LABEL 100;
    FETCH NAME INTO :NAME;
    GOTO 200 WHERE :RETVAL <= 0;       
    /* do something with the tab delimited file */
    LOOP 100;
    LABEL 200;
    CLOSE FILES;
    LABEL 300; 

This new version works like a charm - poetry in motion.

---

Overnight, I had some more ideas on how I can improve the execution speed of this program, moving operations from Priority to the Delphi program. It would be interesting to know whether it takes the same amount of time to delete a file via Priority as it does via Delphi. Unfortunately, the clock resolution in Priority is only minutes - not seconds and certainly not milliseconds. Also, one can only measure 'wall time' as opposed to CPU time, so any measurement is going to be inaccurate.

My basic idea is building one big tab delimited file with the Delphi program then passing this to the interface, as opposed to passing several small files. Technically this should reduce overhead but I don't know whether the saving will be worthwhile. Also, it forces the Delphi program to be less general than I had originally intended. 

I've just checked how long it takes to process eight small files - less than a minute. It's not worth my time making this faster. Unfortunately, I won't get the two hours of lost sleep back (I woke up at about 11:30pm with the ideas but couldn't get back to sleep till about 2am).