Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW

Dynamically Loading Multiple Excel Files

cancel
Showing results for 
Search instead for 
Did you mean: 
Kushal_Chawda

Dynamically Loading Multiple Excel Files

Last Update:

Sep 20, 2022 1:59:53 PM

Updated By:

Sue_Macaluso

Created date:

Jan 4, 2016 1:02:33 PM

Attachments

Hi Qlikers,

Firstly, kudos to avinashelite‌ for giving us the below method to load the all excel files with the multiple sheets.

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

Using above method we can load the multiple excel files with multiple sheets even though all the sheets have different names.

Above method  works only in below scenarios

1)  When all the files having the same number of sheets.

2)  When all the sheets of excel files having the same number of Columns with same name.

But below method works even though you don't have same number of columns and sheets in excel and having blank sheet in excel.

// Define the Path

LET vExcelFilePath = 'D:\Test';
LET vQVDFilePath='D:\Test';


SUB CreateQVDFromAllExcelFiles(vPath)

FOR EACH vFileExtension IN 'xlsx'

FOR EACH vFile IN FILELIST(vPath & '\*.' & vFileExtension);

ODBC CONNECT TO [Excel Files;DBQ=$(vFile)];

Temp:
LOAD *;
SQLtables;
DISCONNECT;

Data:
LOAD * INLINE [
junk ]
;

FOR i = 0 TO NOOFROWS('Temp')-1

LET vSheetName = PURGECHAR(PURGECHAR(PEEK('TABLE_NAME', i, 'Temp'), CHR(39)), CHR(36));

CONCATENATE(Data)
LOAD  *,
FILEBASENAME() AS FileName,
'$(vSheetName)'
AS Sheet_name
FROM $(vFile)(ooxml, embedded labels, table is [$(vSheetName)]);

NEXT i


DROP TABLE Temp;
DROP FIELD junk;

NEXT vFile
NEXT vFileExtension

Set ErrorMode=0;
Drop Field A;     // When there is blank sheet in excel file, field A is created
Set ErrorMode=1;

STORE Data into $(vQVDFilePath)\FullData.qvd;
DROP Table Data;

END SUB

CALL CreateQVDFromAllExcelFiles('$(vExcelFilePath)');

LET i = Null();

Feel free to provide your suggestions

Thanks & Regards,

Kushal Chawda

Tags (1)
Labels (1)
Comments
Kushal_Chawda

I am not sure it will work for print area or not. I think it should skip this sheet in loop but I am not sure

mambi
Creator III
Creator III

may be you can add this : ...where not WildMatch($(vSheetName),'*_xlnm#Print_Area');

Kushal_Chawda

If you can share the sample excel file I will look into that

Digvijay_Singh

Great work Kush and Avinash

Not applicable

can you use this method to load cross tables

Kushal_Chawda

I dont think so that this method is suited for cross table load. But you can use it to load the format as it is, then apply cross table on resident load

gilbertomedeiro
Contributor III
Contributor III

Hi Kushal, congrats!

It is very useful for me!

Thanks for sharing!

psankepalli
Partner - Creator III
Partner - Creator III

Great job, very useful code.. thanks for sharing

Anonymous
Not applicable

Hi i can't execute it it's throwing me the following

"Table not found

STORE Data into C:\FullData.qvd"

and then for the Drop Tables

vnelsoncrunch
Partner - Contributor
Partner - Contributor

Had the same issue...

What I found is you need to install the MS ODBC Driver for Access.  Once I did that the problem went away.

Download Microsoft Access Database Engine 2010 Redistributable from Official Microsoft Download Cent...

Version history
Last update:
‎2022-09-20 01:59 PM
Updated by: