LOAD MULTIPLE EXCEL FILES WITH MULTIPLE SHEETS

[Anonymous]

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

[niclaz79]

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]

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]

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]

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]

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]

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.