Need Help with loading multiple sheets of an Excel file

[ramyasaiqv]

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.

[techvarun]

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)]);

NEXT

Source: Load Multiple excel sheets using For loop

[its_anandrjs]

Hi,

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

Regards,

Anand

[tripatirao · Accepted solution]

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;

[tripatirao]

Hi,

Close the thread by making an answer correct.

So that it  will help to others.

Regards

[ramyasaiqv]

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