Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Load Excel tab name as a field

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.

3 Replies
lfetensini
Partner
Partner

Try: How to load EXCEL worksheets and retrieve tab names

Support your colleagues. Remember to "like" the answers that are helpful to you and flag as "solved" the one that helped you solve. Cheers.
zhadrakas
Specialist II
Specialist II

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
Partner
Partner

Hi John,

The best way to explain please provide sample data.

thanks

Arvind Patil