topic Re: Load Excel tab name as a field in QlikView

Load Excel tab name as a field

Wed, 26 Jul 2017 20:51:10 GMT

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.

Re: Load Excel tab name as a field

lfetensini — Thu, 27 Jul 2017 00:17:24 GMT

Try: How to load EXCEL worksheets and retrieve tab names

Re: Load Excel tab name as a field

zhadrakas — Thu, 27 Jul 2017 05:07:11 GMT

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;

Re: Load Excel tab name as a field

arvind_patil — Thu, 27 Jul 2017 05:39:27 GMT

Hi John,

The best way to explain please provide sample data.

thanks

Arvind Patil