Skip to main content
Announcements
Meet the 2024 Qlik Luminaries and Partner Ambassadors! READ ON!
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

LOAD MULTIPLE EXCEL FILES WITH MULTIPLE SHEETS

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

6 Replies
niclaz79
Partner - Creator III
Partner - Creator III

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

Anonymous
Not applicable
Author

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;

next


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;

olivierrobin
Specialist III
Specialist III

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 ?

Anonymous
Not applicable
Author

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.

olivierrobin
Specialist III
Specialist III

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

niclaz79
Partner - Creator III
Partner - Creator III

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.