Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
ramyasaiqv
Creator II
Creator II

Need Help with loading multiple sheets of an Excel file

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.

1 Solution

Accepted Solutions
tripatirao
Creator II
Creator II

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;

View solution in original post

5 Replies
techvarun
Specialist II
Specialist II

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
Creator II
Creator II

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;

View solution in original post

tripatirao
Creator II
Creator II

Hi,

Close the thread by making an answer correct.

So that it  will help to others.

Regards

ramyasaiqv
Creator II
Creator II
Author

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