How to extract multiple excels, through loop in qlik sense

[vishus913]

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

[undergrinder]

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]

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]

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]

hi,

It works fine in qlikView but not in qliksense

best,

Vikas