Store Excel sheets as QVDs

[Amit_B]

Hi all,

I have an Excel file with multiple sheets.
The sheets don’t have to be identical, and there doesn’t have to be any relationship between the sheets names. Each sheet should be treated as a separate table. The file is very dynamic; sheets can be added/removed, and existing sheets can be modified, etc.

How can I save each sheet as a separate QVD?
I tried using a For loop with Store & Drop, but couldn't find a solution.

I would appreciate help on this topic.
I need solutions for both QlikView and Qlik Sense.

Thanks.

[eddyvargas]

You need to know the list of sheets to store, example:

Sheets:
load * Inline [
Sheet
Planilha1
DESCONSIDERAR
];

for i=0 to NoOfRows('Sheets')-1
_vSheet=Peek('Sheet',i,'Sheets');

[$(_vSheet)]:
LOAD
*
FROM [$(_vFolder)/filename.xlsx]
(ooxml, embedded labels, table is $(_vSheet));

Store [$(_vSheet)] into [$(_vFolder)/$(_vSheet).qvd](qvd);

Drop Table [$(_vSheet)];
next

If you don't know the list of sheets maybe you can use Macro or Talend to split the excel and then load the first sheet:

FROM [$(_vFolder)/filename.xlsx]
(ooxml, embedded labels);

[Or]

In QlikView, you can do this using this rather nasty approach:

https://community.qlik.com/t5/New-to-Qlik-Analytics/how-to-load-multiple-sheets-from-the-same-excel-...

In Qlik Sense, you can't do this afaik.

https://community.qlik.com/t5/New-to-Qlik-Analytics/Load-multiple-excel-sheets-dynamically-without-t...

What you're trying to do sounds like a mess, so the best bet is to try and get users on board with working in a slightly-less-messy way that allows consistent data loading.