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?
Hi Niclas, below is the code:
FOR Each file in FileList('..\..\..\xxx\xxxx\xxxxxx\Data\20*.xlsx');
ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];
for i = 0 to NoOfRows('tables')-1
let sheetname =PurgeChar(PurgeChar(peek('TABLE_NAME',i,'tables'),chr(39)),chr(36));
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)]);
drop table tables;
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;
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.
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
Hi can you try to create the Table first using a
Load * Inline [
And then inside your loop replace the Table header with
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.