extra field using the name of the excel sheet

[#1]

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

[swuehl]

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

[#3]

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