Skip to main content
Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

extra field using the name of the excel sheet

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

2 Replies
swuehl
MVP
MVP

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

Not applicable
Author

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