Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP

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
Not applicable

Works like a charm!! Great work with it...

note: on my machine I had to update the script connection string to CONNECT64 for it to work.

Not applicable

Hi Kush, Great work, but when i executing this script I am getting the "script Line erro" after this error script get executed sucessfully but why this script line error. Can you please help me out?

Kushal_Chawda

How you are executing the script? On which excel files you are executing this script?

Not applicable

I am using the same attached data and changed the path of the excels and QVD's in the script apart from this I have nothing changed in the script.

Kushal_Chawda

Can you post the application with path changes you have done. If you are using the same attached data and application it should work.

beat_roos
Contributor III
Contributor III

Hi

This is really a nice solution what would help me a lot. But

I have excel files with some hidden sheets inside (that happens while the source system generate and save the datas).

How can I use this code but ignore hidden sheets? Any ideas?

thx!

Kushal_Chawda

Have you tried loading the excel with hidden sheets? I have not considered this while developed the code. I will check and will get back to you on this

Not applicable

Excellent

john9inno
Creator
Creator

thanks so much for this super useful info at the beginning of the year!!

mambi
Creator III
Creator III

Good job,

but what if there's a print area (_xlnm#Print_Area' ) on a worksheet ?

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