topic Re: Loop and Load excel files in a folder in reverse order in QlikView

Loop and Load excel files in a folder in reverse order

prees959 — Wed, 27 Sep 2017 09:42:10 GMT

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

Re: Loop and Load excel files in a folder in reverse order

Frank_Hartmann — Wed, 27 Sep 2017 10:46:15 GMT

quite not sure if i really understood your requirement.

But you could try replacing parts of the locations by a variable (vPath) in order to dynamically catch the correct daily excel file

and combining this with incremental Load e.g.

Let vPath = 'DataRun_'&date(date#(Today()),'YYYYMMDD')&'_'&date(date#(Today())+60,'YYYY')&date(date#(Today())+60,'MM')&date(date#(Today()),'DD');

//historicData:
//LOAD ID, 
//     DIM
//FROM
//(qvd);
//Concatenate

actualData:
LOAD *
FROM

(ooxml, embedded labels, table is Tabelle1);


Store historicData into (qvd);

hope this helps

Re: Loop and Load excel files in a folder in reverse order

prees959 — Wed, 27 Sep 2017 14:53:14 GMT

Thanks for you reply.

To explain it a bit better, Every day the number of excel files grows by one which I need to loop through eg,

As of yesterday's , I had these files in a folder :

data_20170921_20171121.xlsx

data_20170922_20171122.xlsx

data_20170923_20171123.xlsx

data_20170924_201711214xlsx

data_20170925_20171125.xlsx

data_20170926_20171126.xlsx

Today a 'data_20170927_20171127.xlsx' file will be dumped into the folder...

Which I have been loading one-by-one into a single qvd called [all_data.qvd]. I have been doing it in reverse order 26th, 25th etc to get the most upto date data by using where not exists' on a key ID field.

Is there a automatic way I can loop through the folder ensuring that I pick the latest file first before the next one and using Where Not Exists on the key ID field

Phil

Re: Loop and Load excel files in a folder in reverse order

marcus_malinow — Wed, 27 Sep 2017 15:04:18 GMT

Hi Phil,

something like this would work.

Marcus

--------------------------------------------

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]

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;

Re: Loop and Load excel files in a folder in reverse order

prees959 — Wed, 27 Sep 2017 17:59:02 GMT

Hi Marcus,

Thanks for this - it seems to start well then fails on :

Data:

    LOAD

    [field list]

    FROM [$(vFolder)$(vCurrentFile)]

    WHERE NOT Exists(PrimaryKeyField)

next n

and produces the following error :

Cannot open file 'C:\QVAPPS\Data\DataRun_20170926_20171126.xlsx

WHERE NOT EXISTS' The filename, directory name, or volume label syntax is incorrect.

Data:

LOAD

[field list]

FROM

WHERE NOT EXISTS([ID])

I have exit'ed the script just before this step and the vFolder and vCurrentFile have the correct path and filename of the most recent file.

Any ideas?

Really grateful for this!

Phil

Re: Loop and Load excel files in a folder in reverse order

rwunderlich — Wed, 27 Sep 2017 23:31:28 GMT

It looks to me like you are missing a ']' closing brace. What does the log file show?

-Rob

Re: Loop and Load excel files in a folder in reverse order

prieper — Thu, 28 Sep 2017 07:18:28 GMT

Hi Phil,

think that you need a further specifyer for the excel, like

Data:

LOAD

ID,

......

FROM

(biff, embedded labels, table is Sheet1$)

WHERE NOT EXISTS([ID])

Re: Loop and Load excel files in a folder in reverse order

marcus_malinow — Thu, 28 Sep 2017 07:45:53 GMT

Hi Phil,

my bad,

you need to add the format specification in the xlsx load. This will be sopmethign like this:

FROM [$(vFolder)$(vCurrentFile)]

(ooxml, embedded labels, table is 'Sheet1')

WHERE NOT Exists(PrimaryKeyField)

I'd suggest building the xlsx part of the load script by setting up a load using the wizard, then copying into my code (substituting in variables where relevant)

Marcus

Re: Loop and Load excel files in a folder in reverse order

prees959 — Thu, 28 Sep 2017 09:14:07 GMT

Hi Marcus,

Much appreciated for this - I didnt spot that ! Is there a way of leaving out the ", table is 'Sheet1'" section as I have noticed that the sheet names never have the same name ?

Many thanks!!

Phil,

Re: Loop and Load excel files in a folder in reverse order

marcus_malinow — Thu, 28 Sep 2017 09:58:18 GMT

Hi Phil,

yes there is a way. You'll need the Excel ODBC driver installed though.

Just prior to the Excel load, try something like this:

ODBC CONNECT TO [Excel Files;DBQ=$(vFolder)$(vCurrentFile)];

//use SQLTables to assemble a list of all the sheets in this Excel file

XLSSheetList:

SQLTables

;

DISCONNECT; //from excel

LET vXLSXSheet = peek('TABLE_NAME', 0, 'XLSSheetList');

DROP TABLE XLSSheetList;

Then substitute in your vXLSXSheet in your subsequent load statement

Marcus

Re: Loop and Load excel files in a folder in reverse order

prees959 — Mon, 02 Oct 2017 09:43:18 GMT

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.

Re: Loop and Load excel files in a folder in reverse order

marcus_malinow — Mon, 02 Oct 2017 11:03:46 GMT

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

Re: Loop and Load excel files in a folder in reverse order

prees959 — Mon, 02 Oct 2017 11:05:57 GMT

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

Re: Loop and Load excel files in a folder in reverse order

prees959 — Tue, 12 Dec 2017 09:59:15 GMT

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

Re: Loop and Load excel files in a folder in reverse order

marcus_malinow — Wed, 13 Dec 2017 13:18:35 GMT

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

Re: Loop and Load excel files in a folder in reverse order

prees959 — Thu, 14 Dec 2017 13:28:00 GMT

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

Re: Loop and Load excel files in a folder in reverse order

marcus_malinow — Thu, 14 Dec 2017 14:45:42 GMT

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