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: 
vishus913
Partner - Creator
Partner - Creator

How to extract multiple excels, through loop in qlik sense

Hi all,

I have multiple excels and each excel is having at least two files in it,

is there a way i can do it in Qlik sense

Thanks

Best Regards,

VIkas

5 Replies
undergrinder
Specialist II
Specialist II

hi Vikas,

my answer in other thread may help you

https://community.qlikview.com/thread/217365

"sub ReadMultipleExcel (Root)

       For Each File in filelist (Root&'\*.xlsx')

            YourTable:

            Load  * FROM [$(File)] (ooxml, embedded labels, table is YourSheet)

       Next File

End Sub

Call ReadMultipleExcel ('lib://YourFiles')

It will loop through every xlsx file and read it's content."

you have multiple sheet, if you have a rules for sheet names, you can build in the script logic, the snippet above could be a template for it.

G.

vishus913
Partner - Creator
Partner - Creator
Author

Hi Gabor,

thanks for the response.

Bt is there a way i can put the names of the tables dynamically in the above script.

thanks

Regards,

Vikas

undergrinder
Specialist II
Specialist II

So, in this script you can use variable in sheet name as well.

Load  * FROM [$(File)] (ooxml, embedded labels, table is $( YourSheet))


One pitfall is you should know the name of the sheets.


----

If you know exactly the file names - sheet names, you can create a table with tablename-sheetname and use it in variable.


table  | sheet

a.xlsx | sheet1

b.xlsx | sheet1

......


LET NumRows=NoOfRows('tables_sheets');


FOR i=0 to $(NumRows)-1

  LET vtableName=Peek('table',$(i));

  LET vSheetName=peek('sheet',$(i));

 

   Load  * FROM [$(vtableName)] (ooxml, embedded labels, table is $(vSheetName))

 

NEXT;


----

If you need that dynamically, without knowledge of sheetname, try this:

Loading Multiple Excel Sheets Dynamically along with file name and sheet name


It's QlikView, I never tried it in Sense.


G.

balabhaskarqlik

May be like this:

This document helps you in loading multiple excels and excel sheets with the name of the sheet and data.

//to read each file from the specified folder

FOR EACH file in FileList('filepath\*.xlsx');

//In order to get the file information from SQLtables command making use of the ODBC connection format
ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];


tables:
SQLtables;
DISCONNECT;

FOR i = 0 to NoOfRows('tables')-1
LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));
Table:
Load * ,
FileBaseName()as FIle,
FileDir() as Dir,
FileName() as File_Name,
'$(sheetName)'
as Sheet_name
From $(file)(ooxml, embedded labels, table is [$(sheetName)]);

NEXT i

Next

vishus913
Partner - Creator
Partner - Creator
Author

hi,

It works fine in qlikView but not in qliksense

best,

Vikas