Reading an excel, create tables from different tabs

[#1]

Good day,

I'm reading data from the spreadsheet that has multiple tabs. The document has a naming convention NAME_MONTHYEAR (document attached).

Each tab contains different information.

Now is it possible I read the document automatically using the dates on its name, and store each tab within as a table in my model.

Your response is highly appreciated.

Regards,

Mbini

[jonathandienst]

If you simply read all the data from all the tabs into different tables, you will get witches brew of synthetic (ie composite) keys between the tables.

Rather do some analysis to understand what you need to load and how the data in each tab relates to the data in the other tabs. It may be that you need to concatenate the data into a single table, possibly with the sheet name as a derived field in that table.

[#3]

Thanks for that reply;

But now I need a mechanism to automatically consume the file regardless of the mechanics (like concatenating), and be able to go the next possible file using the file name.

[Gysbert_Wassenaar · Accepted solution]

You can find an example here that reads in all the sheets from all excel files in a directory and its subdirectories. You can modify that as needed.

[Anonymous]

Hi dok,

it seems like your four tabs contains the same information for different categories so after loading this data in to QlikView do you want four diff tables or you want to have all the information under the single table?

[#6]

I would like four different tables

[Anonymous]

Try This.....

Tab:

LOAD * INLINE [

    ID, Table

    1, 'BANK CYCLES BALANCES'

    2, 'OTHER CYCLES BALANCES'

    3, BANK_711

    4, BANK_811

    5, BANK_101

];

QUALIFY *;

UNQUALIFY portfolio;

for i=0 to 4

let vTable=if(index(Peek('Table',$(i),'Tab'),' ')>0,'['& Peek('Table',$(i),'Tab')&']', Peek('Table',$(i),'Tab')) ;

LOAD *

FROM

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

next i;

Exit Script;

output will be: