Load Excel tab name as a field

[#1]

I have an excel workbook with about 80 spreadsheets in it; each spreadsheet contains data for a specific date and is named for the date on which the data is gathered. Thus the sheet containing data for 01 January 2017 will be named '01-Jan-2017'. The structure of the data in each sheet is identical and does not include the date; thus the data in the sheet named '01-Jan-2017' does not include that date

So far so good.

I now need to load all of the data resident in all of the 80 spreadsheets and in order to differentiate the data by date I need to load the name of each sheet as an additional field within the data and I don't want to have to write 80 load scripts to make this happen.

Can anyone suggest a (relatively) easy way of doing this.

[lfetensini]

Try: How to load EXCEL worksheets and retrieve tab names

[zhadrakas]

try this template

For Each vFile in FileList('Load_Test.xlsx')

     ODBC CONNECT32 To [Excel Files;DBQ=$(vFile)];

     Sheets:

     SQLTABLES;

     DISCONNECT;

     For i = 0 To NoOfRows('Sheets')-1

          Let vSheet = left(Peek('TABLE_NAME', i, 'Sheets'), len(Peek('TABLE_NAME', i, 'Sheets'))-1);

          LOAD '$(vSheet)' as [Tab Name], *

          From [$(vFile)]

          (ooxml, embedded labels, table is $(vSheet));

     Next;

Next;

[arvind_patil]

Hi John,

The best way to explain please provide sample data.

thanks

Arvind Patil