topic Re: Multiple file load - loading old files in QlikView

Multiple file load - loading old files

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi!

I am trying to load multiple numbered sheets from multiple excel files. The problem is that Qlik is loading old versions of the same files. I even renamed the sheets and removed all of the files from the vDataPath folder but I am still getting the same old values. What might be the problem and how to fix it?

This is the script I am working with:

SET vDataPath='C:\Users\user1\Desktop\folder1';

LET vStartSheetNumber = 1;
LET vEndSheetNumber = 3;

FOR Each vFile in FileList(vDataPath & '\*.xlsx')

ODBC CONNECT32 TO [Excel Files;DBQ=$(vFile)];

Temp:
LOAD *;
SQLtables;
DISCONNECT;

NEXT vFile

temp2:
LOAD * INLINE [junk];

Set ErrorMode=0;
FOR index = vStartSheetNumber TO vEndSheetNumber
Concatenate(temp2)
LOAD
filename() as FileName,
*
FROM [$(vDataPath)\*.xlsx]
(ooxml, embedded labels, header is 1 lines, table is [$(index)]);
NEXT index
Set ErrorMode=1;

Thank you!

Re: Multiple file load - loading old files

Anil_Babu_Samineni — Wed, 20 Sep 2017 09:13:07 GMT

Why are you using Error Handling over Script?

Re: Multiple file load - loading old files

Anonymous — Wed, 20 Sep 2017 10:00:29 GMT

Otherwise I am getting an error because some of the files contain only one or two of the numbered sheets.

Re: Multiple file load - loading old files

prieper — Wed, 20 Sep 2017 10:02:39 GMT

As Anil stated, you shouldnot switch off the error-handlings. Instead you know out of the SQLTABLES, which are the sheetnames (TABLE_NAME) and may check against your variables, if match.

Also the full qualified filename is there (TABLE_CAT).

Thus would suggest to construct a loop around the Temp-Table

Not quite clear, which might be the sheetnames, will it be 1, 2, 3 or should be the first, second etc regardless the name?

FOR i = 0 TO NOOFROWS ('Temp')

LET sSheet = PEEK('TABLE_NAME', i, 'Temp');

// now any clever check on the sheetname

LOAD
filename() as FileName,
*

FROM [$(vFile)]
(ooxml, embedded labels, header is 1 lines, table is [$sSheet)]);

NEXT i

Re: Multiple file load - loading old files

Anil_Babu_Samineni — Wed, 20 Sep 2017 10:04:56 GMT

Not even sure, where you are throwing and ignoring the errors and Why?

Perhaps you could use

For Vamit = 0 TO NoOfRows('Temp2')

LET vSample = Peek('Tname', $(Vamit), 'Temp2');