Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi people
I know a way to create an extra field using the name of the file: subfield(FileBaseName(),'_',2) as Date
Does anyone know how to do kind of the same thing but using the name not of the file but of the actual excel sheet?
Thank you
How do you specify the sheet name in the format spec (table is ...) of your LOAD ... FROM statement? Can't you just use this identifier as field value, i.e. hardcoded?
If you loop through the sheets based on a SQLTables call, you can use the loop variable:
'$(vTable)' as SheetName
Sample script
FOR EACH file in FileList('*.xlsx');
ODBC CONNECT64 TO [Excel Files;DBQ=$(file)];
Temp:
SQLtables;
DISCONNECT;
Let vNoofRows = NoOfRows('Temp');
FOR i = 0 to vNoofRows -1
LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME',$(i),'Temp'), chr(39)), chr(36));
Tab1:
Load
Col1
,Col2
,Col3
,'$(sheetName)' as SheetName
From $(file)
(ooxml, embedded labels, header is 1 lines, table is [$(sheetName)])
NEXT
NEXT
Regards,
KKR