Qlik Community

QlikView Deployment

Discussion Board for collaboration related to QlikView Deployment.

Not applicable

Best way to read excel sheet with many tab ?

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

1 Solution

Accepted Solutions
salto
Valued Contributor II

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

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.

27 Replies
salto
Valued Contributor II

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

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.

Not applicable

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

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

datanibbler
Esteemed Contributor

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

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

Not applicable

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

Hi Friedrich,

You could try this alternatively:

Below is the XL:

Business_Transactions:

LOAD B,

     C ,

     D ,

     E ,

     F ,

     G,

   

     D &'_'& E as TKey

FROM

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

(ooxml, no labels, table is Sheet1);

Concatenate

LOAD B,

     C,

     D,

     E

FROM

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

(ooxml, no labels, table is Sheet2);

Concatenate

LOAD B,

     C,

     D,

     E,

     F

FROM

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

(ooxml, no labels, table is Sheet4);

Concatenate

LOAD B,

     C ,

     D ,

     E ,

     F,

     G,

   

     D &'_'& E as TKey

FROM

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

(ooxml, no labels, table is Sheet1);

Concatenate

LOAD A,

     B,

     C ,

     D ,

     E ,

     F ,

     G,

    

     D &'_'& E as TKey

FROM

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

(ooxml, no labels, table is Sheet2);

concatenate

LOAD B,

     C,

     D,

     E,

     F,

     G,

     

      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

Not applicable

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

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

Not applicable

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

Hi Fri

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

Paul

Not applicable

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

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

Not applicable

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

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

salto
Valued Contributor II

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

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.

Community Browser