Best way to read excel sheet with many tab ?

[#1]

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

[salto]

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.

[#3]

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]

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

[#5]

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

[#6]

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

[#7]

Hi Fri

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

Paul

[#8]

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

[#9]

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 · Accepted solution]

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.