Hi experts I need help dynamically loading multiple excel files from folders with sub-folders. I also need each sheet to be loaded in to a table which would carry the name of the sheet. That is, dynamically naming each table according to the file or sheet name when loaded. The data does not have same number of fields and is not identical. These sheets or files have different delimiters spaces, ';', and table is sheet. Below is my code which is not giving me what I need. Any suggestions to fix this will appreciated.
SET vMyConnectionName = 'CC.David.Test';
SET vMySubFolderName = 'David Data';
LOAD * Inline [
FolderName
David Data
David Project
];
LET vNoOfFolders = NoOfRows('SourceFolderList') - 1;
Trace '# Folders = $(vNoOfFolders)';
Sleep 2000;
for i = 0 to vNoOfFolders
Trace 'i = $(i)';
Sleep 2000;
LET vThisFolder = Peek('FolderName', i, 'SourceFolderList');
Trace 'Reading files in $(vThisFolder)';
Sleep 2000;
Data:
LOAD
FileDir() as FileDir,
FileName() as FileName,
*
FROM [lib://$(vMyConnectionName)/$(vMySubFolderName)/$(vThisFolder)/*.xlsx]
(ooxml, embedded labels, table is Sheet1);
Next i
