Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
Highlighted
Not applicable

Load multiple excel file in Qlik Sense

Hi,

Is it possible to load multiple excel files in Qlik Sense with different file names but same sheet name and field name?

Thanks

6 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: Load multiple excel file in Qlik Sense

Hi,

Yes you can use the qlikview script given in below link, it will work in Qlik Sense.

Load multiple excel files from Multiple excel sheetsinto QV

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

Regards

Kaushik Solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
Highlighted
Not applicable

Re: Load multiple excel file in Qlik Sense

Thanks for the reply, I had tried it before but it does not load anything. I am trying to Disabling standard mode so that it can run in legacy mode, however when I open the setting.ini, it is a blank page.

Highlighted
MVP & Luminary
MVP & Luminary

Re: Load multiple excel file in Qlik Sense

Hi,

In Qlik Sense you might need to modify the script little bit, due to the way the Path is defined in Qlik Sense.

Hope you have done that change. (Create a Library path which is connected to the folder from where you want to get the excel)

Regards,

Kaushik Solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
Highlighted
Not applicable

Re: Load multiple excel file in Qlik Sense

I have change to file path to lib connector, but I don't know how to change to ODBC CONNECT32, it saids "connects other than lib connect are not available in this script mode"

//to read each file from the specified folder

FOR EACH file in FileList('Lib://Transport Claim/*.xls');

//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

Highlighted
jayaseelan
Contributor III

Re: Load multiple excel file in Qlik Sense

Hi,

Try this Example to you case,

FOR EACH file in FileList('C:\Users\HP\Desktop\New folder\2700.xlsx');

ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];Temp:

SQLtables;

DISCONNECT;

FOR i = 0 to NoOfRows('Temp')-1

LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'Temp'), 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

Thank,

Highlighted
nizamsha
Valued Contributor II

Re: Load multiple excel file in Qlik Sense

Hi Kaushik Solanki

 

How to change according to Qlik Sense Script I tried many ways but it does not work

 

 

Regards

Nizam