topic LOAD MULTIPLE EXCEL FILES WITH MULTIPLE SHEETS in QlikView

LOAD MULTIPLE EXCEL FILES WITH MULTIPLE SHEETS

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

Hi ,

I have multiple files (File names like 2010Data,2011Data etc) and each file with sheets from Jan till Dec. I have tried the load multiple files with multiple sheets dynamically link which was posted earlier but script is executed and all files with sheets are loading in the execution window but the progress does not stop in execution window and even after all files are looped and loaded. i think there is some issue in the code, can any one suggest the error in the code please?

Loading Multiple Excel Sheets Dynamically along with file name and sheet name

Regards

Re: LOAD MULTIPLE EXCEL FILES WITH MULTIPLE SHEETS

niclaz79 — Mon, 26 Mar 2018 14:01:37 GMT

Hi,

Can you provide the code you are using so we can look into it directly? It's hard to find faults on your script based on the link to another code you provided

Re: LOAD MULTIPLE EXCEL FILES WITH MULTIPLE SHEETS

Anonymous — Mon, 26 Mar 2018 14:27:42 GMT

Hi Niclas, below is the code:

FOR Each file in FileList('..\..\..\xxx\xxxx\xxxxxx\Data\20*.xlsx');

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

tables:
SQLtables;

for i = 0 to NoOfRows('tables')-1

let sheetname =PurgeChar(PurgeChar(peek('TABLE_NAME',i,'tables'),chr(39)),chr(36));

Table:

load *,
FileBaseName() as File,
FileDir() as Dir,
FileName() as File_Name,
'$(sheetname)' as Sheet_Name
from $(file) (ooxml, embedded labels, header is 2 lines, table is [$(sheetname)]);
next i
drop table tables;

EXIT Script;

The code executes and i can see 12 lines per table loading in the execution window , 12 months per excel table but the loop does not break . script execution needs to be aborted .

Thanks for the help;

Re: LOAD MULTIPLE EXCEL FILES WITH MULTIPLE SHEETS

olivierrobin — Mon, 26 Mar 2018 14:43:09 GMT

hello

can you trace the name of the file ?

i mean is it always the same of do you read each file in turn

and does it end ?

Re: LOAD MULTIPLE EXCEL FILES WITH MULTIPLE SHEETS

Anonymous — Mon, 26 Mar 2018 14:48:10 GMT

Hi Olivier, i can trace the filename , like : 2012Data 12 lines (Jan to dec sheets) and followed by 2013Data 12 lines(sheets) and so on after reading all the data from all the excel tables the script should end executing , but not in my case.

Re: LOAD MULTIPLE EXCEL FILES WITH MULTIPLE SHEETS

olivierrobin — Mon, 26 Mar 2018 14:51:12 GMT

if you see all files, but the script doesn' and, it's probably because QV is creating synthetic keys

try 2 things :

- load only one sheet of 1 excel

- try to force the loading into the same table by using concatenate

Re: LOAD MULTIPLE EXCEL FILES WITH MULTIPLE SHEETS

niclaz79 — Tue, 27 Mar 2018 07:46:24 GMT

Hi can you try to create the Table first using a

Table:

Load * Inline [

tempcolumn

];

And then inside your loop replace the Table header with

concatenate(Table).

I believe you are creating 12 identical tables per excel sheet which causes your application to try and make synthetic keys between them which is why it appears to not exit. Your computer is actually working really hard to make sense of all the synthetic keys, which tends to take a while, especially if you have a lot of identical tables.