Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
lalitkgehlot89
Creator II
Creator II

How to pick the data From multiple sheet dynamically in Qliksense.

Hi,

Hi,

In Attached sample data file I want to pick all the data from all sheet of the excel.

But my no of sheet is not defined. It could be increased in future. but data format will be the same on all sheet.

Please let me know how to pic all the data with sheet name as column in data.

Thanks,

Lalit Kumar

3 Replies
adityaakshaya
Creator III
Creator III

Hi Lalit,

Please follow the below steps to pick data from all sheets at one go.

Step 1: Create a Connection and select the folder where your file reside and name it as "Data"

Step 1: Create a ODBC connection with same Excel file.

Step 2 : Count the number of Tables(Sheets) by use below Script

Tables:

SQLTABLES;

let vNoOfTables=NoOfRows('Tables');

Step 3: Run the Loop to pull data from all the sheets by using code below

For i = 0 to $(vNoOfTables)-1

Let vSheetName=subfield(peek('TABLE_NAME', i,'Tables'),'$',1);

  $(vSheetName):

LOAD *
FROM
[Lib://Data/Sample Data File.xlsx]
(ooxml, embedded labels, table is '$(vSheetName)')

;
  NEXT i

Regards,

Akshaya

PS - if you find this response as Correct and helpful, Please mark it as Correct and Helpful.

lalitkgehlot89
Creator II
Creator II
Author

Hi Akshaya,

Its working fine. As you mention in Step1. Suppose I have N numbers of excel file with N numbers of sheet . In that case I'll have to make connection with all the excels files. or Is there any option to pick the data from all files including all sheet at a single time.

adityaakshaya
Creator III
Creator III

Hi Lalit,

While working with Qliksense, if you can change Legacy Mode to 0, you can do the looping and achieve it but I will not suggest that as Managing Paths at QMC level become a challenge.

If you have fixed number of files, then I will suggest you to create ODBC connection and do as we discussed above or if you have fixed number of sheets and N number of Files, then you loop to find the number of files and within it run a another loop to fetch data from different sheets.

Regards,

Akshaya