topic Re: Multiple Excel Sheets in QlikView

Multiple Excel Sheets

psk180590 — Wed, 25 Nov 2020 16:16:04 GMT

Hallo All,

I have few Excel Sheets with multiple files with Month_Year as the Sheet Name.

How do i load all the sheets once and also save the Sheet Name as Date.

Ex: Data.xlsx has the Following Sheets with some data

Jan 12, Feb 12.....Dec 13

I want to load all the sheets and save the Sheet Name as Jan 2012, Feb 2012...Dec 2013.

Found few links in the community but somehow was not successful. Any pointers would be appraciated.

TIA!!

Re: Multiple Excel Sheets

Anil_Babu_Samineni — Mon, 28 May 2018 03:29:58 GMT

What are these? Jan 12 and Feb 12 ...??

Re: Multiple Excel Sheets

jonathandienst — Mon, 28 May 2018 05:05:33 GMT

This technique will load all the sheets from the matching files:

For Each vFileName In FileList(....)

ODBC CONNECT32 TO [Excel Files;DBQ=$(vFileName)];

SpreadsheetData:

SQLTABLES;

DISCONNECT;

For i = 0 to NoOfRows('SpreadsheetData') - 1

Let zSheet = Peek('TABLE_NAME', $(i), 'SpreadsheetData');

LOAD .... FROM $(vFileName)

(biff, table is [$(zSheet)]);

Next

Fill in the missing details. Hopefully that gets you going.

Re: Multiple Excel Sheets

jonathandienst — Mon, 28 May 2018 05:09:50 GMT

To include the date modified as requested, use this code

For i = 0 to NoOfRows('SpreadsheetData') - 1

Let zSheet = Peek('TABLE_NAME', $(i), 'SpreadsheetData');

Let zDate = Num(Date#(zSheet, 'MMM yy'));

LOAD ...,

Date($(zDate, 'MMM yyyyy'),

....

FROM $(vFileName)

(biff, table is [$(zSheet)]);

Next

Re: Multiple Excel Sheets

psk180590 — Mon, 28 May 2018 12:05:28 GMT

These are the Sheet Names

Re: Multiple Excel Sheets

psk180590 — Mon, 28 May 2018 12:08:23 GMT

Hi Jonathan,

Thanks for the suggestion. I'm able to connect to the Excel folder and the script also runs successfully but, i don't see any fields after the reload.

Re: Multiple Excel Sheets

psk180590 — Mon, 28 May 2018 12:34:26 GMT

Also, Attaching a sample excel.

Re: Multiple Excel Sheets

jonathandienst — Mon, 04 Jun 2018 15:12:20 GMT

I suggest you post the script you used. What I supplied was a script pattern to help you get to the correct script.You need to adapt the pattern to your specific environment and requirements.