topic Re: Need Help with loading multiple sheets of an Excel file in QlikView

Need Help with loading multiple sheets of an Excel file

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

Hi Qlik gurus/Experts,

I'm trying to load multiple sheets of an excel file and had no luck.So i'm here for some help please help me.

I have multiple excels, each excel has multiple sheets (same fields but different years data). I'm trying to load these multiple sheets data and need help with that. I know how to load excel's with similar naming convention if each excel has one sheet only. But here the requirement is each excel has multiple sheets. I have attached the sample excel data for your reference. Thank you in advance for our help.

Re: Need Help with loading multiple sheets of an Excel file

techvarun — Tue, 08 Aug 2017 04:04:08 GMT

Try the below script

LET vStartSheetNumber = 2016;

LET vEndSheetNumber = 2018;

LET vExcelFileName = 'Data';

// Generate Empty table

Data:

LOAD

'' AS Data.Field1,

'' AS Data.Field2

AutoGenerate(0);

FOR index = vStartSheetNumber TO vEndSheetNumber

Concatenate(Data)

LOAD

FROM

[Multiple Excel sheet.xlsx]

(ooxml, embedded labels, table is [Year_$(index)]);

Source: Load Multiple excel sheets using For loop

Re: Need Help with loading multiple sheets of an Excel file

its_anandrjs — Tue, 08 Aug 2017 06:00:17 GMT

Hi,

Go for the for loop script for this a good for multiple file loading.

Regards,

Anand

Re: Need Help with loading multiple sheets of an Excel file

tripatirao — Tue, 08 Aug 2017 07:35:36 GMT

Dear Ramya,

Please find the attached qlikview document.

I hope it is suitable for you.

ODBC CONNECT32 TO [Excel Files;DBQ=E:\NIGERA-DOCUMENT\Multiple Excel sheet.xlsx];

tables:

SQLTables;

DISCONNECT;

Tables1:

load *,

replace(TABLE_NAME,'$','') as NEW

resident tables;

DROP Table tables;

let var=NoOfRows('Tables1');

FOR i = 0 to $(var)-1

let sheetName=peek('NEW', i,'Tables1');

Data:

CrossTable(Month, Data, 3)

LOAD [Testing Area],

Reference,

SubField('$(sheetName)','_',2)as Year,

Jan,

Feb,

Mar,

Apr,

May,

Jun,

Jul,

Aug,

Sep,

Oct,

Nov,

Dec

FROM

[Multiple Excel sheet.xlsx]

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

next i

DROP Table Tables1;

Re: Need Help with loading multiple sheets of an Excel file

tripatirao — Tue, 08 Aug 2017 15:10:48 GMT

Hi,

Close the thread by making an answer correct.

So that it will help to others.

Regards

Re: Need Help with loading multiple sheets of an Excel file

ramyasaiqv — Tue, 08 Aug 2017 23:16:40 GMT

I'm sorry some reason it doesn't show close option. In the Actions only" Mark as Helpful" option available.