Loop and Load excel files in a folder in reverse order

[prees959]

Hi,

Every day we receive a new Excel file with the dates contained in the filename eg, DataRun_20170927_20171127.xlsx

What I need to do is loop through the folder that these files are in and load with in order of the most recent one received with a

Where Not Exists on an ID field so in effect it is bringing in the most upto date records.

Is this possible?

Thanks,

Phil

[prees959]

Hi Marcus,

I seem to be getting an error in the load again :

Field not found - <field list>

Data:

    LOAD

    [field list]

    FROM [C:\QVAPPS\Data\DataRun_20170926_20171126.xlsx]

    (ooxml, embedded labels,table is @1)

Any ideas on this one?

Thanks again,

Phil.

[marcus_malinow]

Hi Phil,

you're going to need to specify the list of fields from your data source. I included [field list] as a placeholder for you to substitute in your own fields.

Marcus

[prees959]

Aaah , Thanks Marcus.   Yes - all sorted now thanks again for your help.  Really useful script!!

[prees959]

Hi again,

Following on from this solution, I create a daily qvd  so I have a daily historical snapshot of all the records that come through the system.

At the moment I have a list of qvd's :

records_as_at_20171001.qvd

records_as_at_20171002.qvd

records_as_at_20171003.qvd

records_as_at_20171004.qvd

records_as_at_20171005.qvd

records_as_at_20171006.qvd

...

... right the way to ...

records_as_at_20171211.qvd

What I would like to do is create running qvd of records that appear in the earliest qvd (in this case 20171001) and only keep those records that are not in the following qvd (20171002).... and place these records in a 'cancellations' qvd.

Then compare the next two chronological files 20171002 and 20171003 and append those records that exist in 20171002 but not in 20171003 to the 'cancellations qvd.... and so on.... So, at the end of the load process I am left with a running file of cancellations. Could this be incorporated into the your first solution?? Or could this be an addition?

Many thanks for any help - its very much appreciated!

Phil

[marcus_malinow]

Hi Phil,

if I've understood correctly, you want your latest record to be retained, and prior records to be put into cancellations.

If so, I would continue loading in reverse date order and add the following flag (substituting in your own key field):

if(exists([Key field]), 1, 0) as [Cancellations Flag]

Once this is added it should be a simple matter to load cancellations to a new table via a resident load, and save a qvd.

Marcus

[prees959]

Hi Marcus,

Thanks for your reply again.

So, using your initial solution, would I place the new line of code here :

for each file in FileList('$(vFolder)DataRun*.xlsx')

    TRACE found file $(file);

    TempFileList:

    LOAD

        subfield('$(file)','\', -1) as tempFileName,

        '$(file)' as tempFullFileName,

        right(subfield(subfield('$(file)','\', -1), '.', 1), 😎 as tempFileDate

    AUTOGENERATE 1;

next file

OrderedFileList:

NOCONCATENATE

LOAD

    tempFileName as FileName,

    tempFullFileName as FullFileName,

    tempFileDate as FileDatev2

RESIDENT TempFileList

ORDER BY tempFileDate desc //retrieve most recent files first

;

DROP TABLE TempFileList;

for n = 1 to NoOfRows('OrderedFileList')

    LET vCurrentFile = peek('FileName', n-1, 'OrderedFileList');

    LET vCurrentFileDateStamp = peek('FileDatev2', n-1, 'OrderedFileList');

    TRACE ;

    TRACE $(vCurrentFile);

    TRACE $(vCurrentFileDateStamp);

    Data:

    LOAD

    [field list],

    if(exists([Key field]), 1, 0) as [Cancellations Flag]

    FROM [$(vFolder)$(vCurrentFile)]

    WHERE NOT Exists(PrimaryKeyField)

next n

if not isnull(QvdCreateTime('$(vQVDFolder)Data.QVD')) then

    CONCATENATE (Data)

    LOAD

    [field list]

    FROM [$(vQVDFolder)Data.QVD]

    WHERE NOT Exists(PrimaryKeyField)

end if

DROP TABLE OrderedFileList;

Regards, Phil

[marcus_malinow]

Hi Phil,

yes, however it looks like in that code there is already a check in the WHERE clause which would prevent any of the Cancellations from being loaded in the first place. So that would have to be removed.

Marcus