how to load multiple sheets from the same excel into qlik sense

[RK007]

Hello everyone,

I have a requirement that need to load the table from Excel.

my excel contains two sheets, 1) Sales 2)Department.

my requirement is to write a single script, to load the tables as well as store them into the qvd's.

please suggest me the best solution for this.

thanks in advance.

[rwunderlich]

Do both tables have the same columns?

-Rob

[RK007]

one field in common, which is id column

[RudyKostka · Accepted solution]

Hi

I will try to briefly describe the process:
1) Prerequisites - installed Microsoft Office package with Access database or install ODBC driver package Microsoft Access Database Engine 2016 Redistributable (e.g. https://www.microsoft.com/en-us/download/details.aspx?id=54920)
2) create an ODBC connector to the selected excel file via the Microsoft Excel driver
3) create an ODBC connector in QlikSense, e.g. "Excel ODBC"
4) find out the list of sheets in excel and select the required one for load
5) Through the cyclic load FOR NEXT, load selected sheets from Excel and save them in QVD
The described process ensures loading of a dynamically changing list of sheets. The process itself needs to be debugged.

Example script for xlsx file:

LIB CONNECT TO 'Excel ODBC';
Tmp_Tables: 
SQLtables;
DISCONNECT;

Tables:
NoConcatenate LOAD
    TABLE_NAME,
    SubField(Mid(TABLE_CAT, Index(TABLE_CAT,'\',-1)+1),'.',1)	as TABLE_CAT
Resident Tmp_Tables Where TABLE_TYPE = 'SYSTEM TABLE';
DROP TABLE Tmp_Tables;

FOR i = 0 to NoOfRows('Tables')-1
LET vSheetName = purgeChar(purgeChar(peek('TABLE_NAME',i,'Tables'),chr(39)),chr(36));
LET vTabName = Peek('TABLE_CAT', i, 'Tables');

['$(vTabName)']:
LOAD
   '$(vSheetName)' as [Sheet Name],
   FileBaseName()  as [File Name],
   *
FROM [$(vFile)] (ooxml, embedded labels, table is [$(vSheetName)]);

STORE [$(vTabName)] into [$(vTabName).qvd](qvd);
DROP Table [$(vTabName)];

NEXT i

[rwunderlich]

Can you just use the script wizard twice, once selecting the first sheet and again selecting the second sheet?

-Rob

[RK007]

Yes, I can do that, but is there any possibility to load both the tables and store them into QVDs using the single script (maybe looping for or something)?

I have tried a few but it didn't work out for me, somewhere I am going wrong.

could you please suggest me way to approach this?

thanks in advance

[whiteymcaces]

It doesn't need to be so complicated.

First question is WHY must it be 1 script?

Second question is what do you mean by 'single script'?  A script can be 1 line or 1000 lines.

How many QVDs do you want to create?

How many spreadsheets are you dealing with? Do you want a 'script' that can handle multiple spreadsheets? Daily spreadsheets? What data sources do you need to cater for?

Why do you need to STORE the data? Why can't you USE the data from the spreadsheet? What are you trying to do with the data?

Simple concept is:

Sales:

Load * from [path and name of spreadsheet] \  Sales;

Store Sales Into [path and name of QVD];

Drop Table Sales:

Do the same for Department.