topic Re: Best way to read excel sheet with many tab ? in QlikView

Best way to read excel sheet with many tab ?

Mon, 26 Jan 2026 18:19:17 GMT

Hi All

I have a excel file as per enclosed. I like to ask in order for me to read the raw data into QV ,there must be best approach. hope some one can advise me.

Instead of i read individual Tab one by one which is very trouble some , it there a better approach on short cut ?

Paul

Re: Best way to read excel sheet with many tab ?

salto — Wed, 09 Oct 2013 06:53:28 GMT

Hi,

maybe you could join all the sheets in a single sheet, appending the data in a sheet after the data in the previous sheet in Excel, before you read it in Qlikview.

Probably there is an auntomated way to do this in Excel.

Regards.

Re: Best way to read excel sheet with many tab ?

Wed, 09 Oct 2013 06:54:01 GMT

Hi Paul,

If I understood correctly, you want to read the data that is currently present of different tabs into one single qv?

And you want the data in multiple objects(grids-tableboxes to be specific) or just one huge table box containing all the data irrespective of the tabs in you XL? Please elaborate..

Rochelle

Re: Best way to read excel sheet with many tab ?

datanibbler — Wed, 09 Oct 2013 06:59:57 GMT

Hi John,

do all the tabs have the same data in them (for different days/ weeks)?

A colleague of mine tried that a while ago - I think he chose a different approach in the end and made another sheet just to combine all the data, but you could try building a LOOP over the tab_names.

The tab names "January, February etc." are quite difficult for a loop - Month-1, Month-2, Month-3... would be better if you can make it - that's easier with the counter you need for a loop.

HTH

Best regards,

DataNibbler

Re: Best way to read excel sheet with many tab ?

Wed, 09 Oct 2013 07:15:57 GMT

Hi Friedrich,

You could try this alternatively:

Below is the XL:

Business_Transactions:

LOAD B,

C ,

D ,

E ,

F ,

D &'_'& E as TKey

FROM

C:\Users\91014371\Desktop\Format1.xlsx

(ooxml, no labels, table is Sheet1);

Concatenate

LOAD B,

FROM

C:\Users\91014371\Desktop\Format1.xlsx

(ooxml, no labels, table is Sheet2);

Concatenate

LOAD B,

FROM

C:\Users\91014371\Desktop\Format2.xlsx

(ooxml, no labels, table is Sheet4);

Concatenate

LOAD B,

C ,

D ,

E ,

D &'_'& E as TKey

FROM

C:\Users\91014371\Desktop\Format2.xlsx

(ooxml, no labels, table is Sheet1);

Concatenate

LOAD A,

C ,

D ,

E ,

F ,

D &'_'& E as TKey

FROM

C:\Users\91014371\Desktop\Format2.xlsx

(ooxml, no labels, table is Sheet2);

concatenate

LOAD B,

D &'_'& E as TKey

FROM

C:\Users\91014371\Desktop\Format2.xlsx

(ooxml, no labels, table is Sheet3);

This option will work provided the columns are same.

By this load, data will just get appended into one single huge tablebox,and subsequently decrease your load time considerably.

Then further you will have to link this to your data model, by defining the uniqueness of every key by some combination(as Ive done in mine).This is based on your data.You will have to see that.

Im sure this will help.

If you need data to be stored in different qvds and subsequently different objects(tableboxes/pivots etc).remove the concatenate statement and give different names to every tab of your XL while loading,and then join all of then using the same key.

This should work in your case,cause Ive done it multiple times and its failproof.

Let me know if you face any issues..

Rochelle

Re: Best way to read excel sheet with many tab ?

Wed, 09 Oct 2013 07:16:53 GMT

Hi John

what i want is i need all the user input data into 1 QV Table.

meaning Jan till Oct Quotation all into 1 QV Table.

in order for me to analyse :-

- Per month how many quotation from each sales staff ?

- How many quotation by month ?

- How many quotation already win / lost / open ?

Paul

Re: Best way to read excel sheet with many tab ?

Wed, 09 Oct 2013 07:24:48 GMT

Hi Fri

I like to use the loop to read all the TAB raw data , it is possible to give me an example ?

Paul

Re: Best way to read excel sheet with many tab ?

Wed, 09 Oct 2013 07:30:35 GMT

Hi John

you approach is good too , ( i will make the header for every month same , so that it will put all in one table ).

Only Con is compare to loop :-

i need to maintenance, every month i still need to add script , for loop i don't need to maintenance in future. if i can have loop like :-

201201

201202

201203

201212.

201301

201302

201303

201310

In case you have loop example , kindly share with me.

Paul

Re: Best way to read excel sheet with many tab ?

Wed, 09 Oct 2013 07:34:36 GMT

Hi Paul,

In the above example the concatenate statement works as a loop,Im not sure of what you mean when you say "loop".Please elaborate.

Rochelle

Re: Best way to read excel sheet with many tab ?

salto — Wed, 09 Oct 2013 07:35:15 GMT

Hi,

you can make the loop creating an Inline table with the names of the sheets:

InTable:

LOAD * INLINE [

SheetNo, SheetName

1, January

2, February

3,March

4,April

5,May

6,Chr_June

7,JULY

8,August

9,September

10, October

];

Then the loop:

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

LET vSheet = peek('SheetNo', $(i), 'InTable');

AllData:

LOAD *,

FROM ExcelSheetName.xlsx (biff, embedded labels, table is $(vSheet));

next i

Hope this helps.

Re: Best way to read excel sheet with many tab ?

Wed, 09 Oct 2013 07:38:37 GMT

Hi SALTO,

Absolutely,

Then Paul can use the concatenate statement to keep appending data from multiple tabs in the XL

Paul:

Please try and tell us if this worked for you..

Regards,

Rochelle

Re: Best way to read excel sheet with many tab ?

Wed, 09 Oct 2013 07:43:22 GMT

Hi John

Mr Salto already give me example using loop approach , which is the most effective way i think so.

Paul

Re: Best way to read excel sheet with many tab ?

Wed, 09 Oct 2013 07:45:17 GMT

Hi John

Sure i will try it , and i believe it will work fine. but i need to make the all sheet header same for all old data.

Paul

Re: Re: Best way to read excel sheet with many tab ?

CELAMBARASAN — Wed, 09 Oct 2013 07:46:10 GMT

You can use something like this

List the sheets in one new sheet

Sheets://Load the Sheet names from the "Sheets" Sheet

LOAD SheetName

FROM

temp1.xlsx

(ooxml, embedded labels, table is Sheets);

LET vNoofSheets = NoOfRows('Sheets') - 1;

LET vSheetName = Peek('SheetName', 0, 'Sheets');

TableName:

LOAD *,

'$(vSheetName)' AS SheetName

FROM

temp1.xlsx

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

For i=1 to $(vNoofSheets)

LET vSheetName = Peek('SheetName', $(i), 'Sheets');

Concatenate(TableName)

LOAD *,

'$(vSheetName)' AS SheetName

FROM

temp1.xlsx

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

Hope the attachment helps

Re: Re: Best way to read excel sheet with many tab ?

Wed, 09 Oct 2013 08:11:19 GMT

Hi Sir

I try the loop script as mention on forum , I get below error :-

Field not found - <Date>

AllData:

LOAD

Date

FROM

[quotation list 2013.xlsx]

(ooxml, embedded labels, table is 10)

Enclsoed my QV doc.

Right now no error , because i comment the script on TAB=LOOP , if you un-comment it will see error. ( now my script read raw data with loop )

Paul

Re: Best way to read excel sheet with many tab ?

salto — Wed, 09 Oct 2013 08:32:54 GMT

Hello,

So sorry!

There was en error in the script I sent: instead of SheetNo it should say SheetName (in the loop):

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

LET vSheet = peek('SheetName', $(i), 'InTable');

AllData:

LOAD *,

FROM ExcelSheetName.xlsx (biff, embedded labels, table is $(vSheet));

Regards.

Re: Re: Best way to read excel sheet with many tab ?

Wed, 09 Oct 2013 08:33:57 GMT

Hi Cel

you are good. because your script very short. what i need to do is make my header all same. all data will come in.

Paul

Re: Re: Best way to read excel sheet with many tab ?

Wed, 09 Oct 2013 08:57:58 GMT

Hi Salto

i still cannot figure out where go wrong , can you attach the file for me to check thru ?

Re: Re: Re: Best way to read excel sheet with many tab ?

Wed, 09 Oct 2013 09:02:27 GMT

Hi Sir

i use my actual raw data ( because the sample raw data have few row of data only) , i change the file name = temp2 ( so that your temp1 still there )

then i use your qvw file = excel , i change to excel1 ( so that your excel.qvw still there )

I also notice you add new sheet at temp1 , i also add new sheet at temp2 , which have all the month name inside

then i go to excel2.qvw to change the loop script look for temp1 change to temp2

why i get below error.

Field not found - <SheetName>

Sheets:

LOAD SheetName

FROM

temp2.xlsx

(ooxml, embedded labels, table is Sheets)

Enclosed 2 file from you working , and 2 file from me not working. may be i misss out some thing.

Re: Re: Re: Best way to read excel sheet with many tab ?

Wed, 09 Oct 2013 09:12:15 GMT

Hi Cel

I found the mistake , Tab name instead of sheets , i enter as sheet.

Now working fine.

Thank