article Dynamically Loading Multiple Excel Files with Multiple Sheets in Member Articles

Dynamically Loading Multiple Excel Files with Multiple Sheets

Kushal_Chawda — Wed, 25 Sep 2024 18:20:24 GMT

I 'd like to thank avinashelite‌ for sharing the method to load the multiple excel files with the multiple sheets. You can find the article at the link below.

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

Unfortunately, this method has some limitations. It only works when all files have the same number of non-blank sheets, and all sheets must have the same number of columns with identical names.

I’d like to share a method below that works effectively without these restrictions

1) Define some variables to automate process.

/* Change the below variable with actual folder path */ LET vExcelFilePath = '..\Files'; // Root folder path on which all the excel files are stored. It might contain sub folders as well. LET vFileExtension = 'xlsx'; LET vQVDFilePath='..\QVD'; // Path on which a QVD is stored which contains data from all the excel files LET vQVDName ='Sales_data'; // Name of a physically stored QVD File

2) Define function to get the list of all the files located inside the folder. It also scans sub folders inside the parent folder.

/* Below subroutine is created to get the lis of excel files stored in specific folder and sub folder within that */ sub ScanFolder(Root) for each vFile in filelist( Root & '\*.' & vFileExtension) FileList: LOAD '$(vFile)' as Files AutoGenerate 1; next vFile for each SubDirectory in dirlist( Root & '\*' ) call ScanFolder(SubDirectory) next SubDirectory end sub Call ScanFolder('$(vExcelFilePath)') ; let vFile = Null();

3) Define a function to loop through the list of Excel files generated by the previous function. Let’s take a look at the connection string below. I’ve used CONNECT64 because I’m working with a 64-bit ODBC driver and Excel application. If you’re using 32-bit ODBC drivers and Excel, you may need to use CONNECT32 instead. The 'Excel Files' DSN is created on the machine using either 64-bit or 32-bit ODBC drivers. Typically, when Office drivers are installed, the 'Excel Files' DSN is created automatically, but in some cases, it may need to be created manually. If your DSN name differs, be sure to update it in the connection string below."

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

/* Below subroutine is created to load all the excel file and store it into the QVD. */ SUB load_all_excel_files_and_store_in_qvd(LoadData) FOR EACH vFile IN FieldValueList('Files'); ODBC CONNECT64 TO [Excel Files;DBQ=$(vFile)]; Temp: LOAD *; SQLtables; DISCONNECT; [$(vTableName)]: LOAD * INLINE [ junk ]; FOR i = 0 TO NOOFROWS('Temp')-1 LET vSheetName = PURGECHAR(PURGECHAR(PEEK('TABLE_NAME', i, 'Temp'), CHR(39)), CHR(36)); Set ErrorMode=0; // Disable error mode to avoid error while loading blank sheet CONCATENATE([$(vTableName)]) LOAD *, SubField('$(vFile)','\',-1) AS FileName, '$(vSheetName)' AS Sheet_name FROM $(vFile)(ooxml, embedded labels, table is [$(vSheetName)]); if len(FieldName(FieldNumber('A','Data'),'A'))>0 THEN Drop Field A; // When there is a blank sheet in the excel file, field A is created which we don't want ENDIF Set ErrorMode=1; NEXT DROP TABLE Temp; DROP FIELD junk; NEXT let vFile = NULL(); if NoOfRows('$(vTableName)')>0 THEN STORE [$(vTableName)] into $(vQVDFilePath)\$(vQVDName).qvd(qvd); // store into the QVD file ELSE TRACE "Data is not available"; ENDIF END SUB CALL load_all_excel_files_and_store_in_qvd(LoadData); DROP Table FileList;

Note: This Script is best suited for QlikView

Please feel free to offer any suggestions to improve this document.

Thanks & Regards,

Kushal Chawda

Re: Dynamically Loading Multiple Excel Files

Tue, 05 Jan 2016 23:08:34 GMT

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.

Re: Dynamically Loading Multiple Excel Files

Wed, 06 Jan 2016 06:40:16 GMT

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?

Re: Dynamically Loading Multiple Excel Files

Kushal_Chawda — Wed, 06 Jan 2016 06:44:07 GMT

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

Re: Dynamically Loading Multiple Excel Files

Wed, 06 Jan 2016 06:57:48 GMT

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.

Re: Dynamically Loading Multiple Excel Files

Kushal_Chawda — Wed, 06 Jan 2016 07:05:43 GMT

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

Re: Dynamically Loading Multiple Excel Files

beat_roos — Wed, 06 Jan 2016 08:33:11 GMT

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!

Re: Dynamically Loading Multiple Excel Files

Kushal_Chawda — Wed, 06 Jan 2016 17:14:54 GMT

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

Re: Dynamically Loading Multiple Excel Files

Wed, 06 Jan 2016 17:19:46 GMT

Excellent

Re: Dynamically Loading Multiple Excel Files

john9inno — Wed, 06 Jan 2016 23:28:50 GMT

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

Re: Dynamically Loading Multiple Excel Files

mambi — Thu, 07 Jan 2016 09:57:26 GMT

Good job,

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

Re: Dynamically Loading Multiple Excel Files

Kushal_Chawda — Thu, 07 Jan 2016 10:24:23 GMT

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

Re: Dynamically Loading Multiple Excel Files

mambi — Thu, 07 Jan 2016 11:05:29 GMT

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

Re: Dynamically Loading Multiple Excel Files

Kushal_Chawda — Thu, 07 Jan 2016 11:08:21 GMT

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

Re: Dynamically Loading Multiple Excel Files

Digvijay_Singh — Sat, 19 Mar 2016 10:51:57 GMT

Great work Kush and Avinash

Re: Dynamically Loading Multiple Excel Files

Sat, 19 Mar 2016 12:09:46 GMT

can you use this method to load cross tables

Re: Dynamically Loading Multiple Excel Files

Kushal_Chawda — Mon, 21 Mar 2016 06:37:17 GMT

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

Re: Dynamically Loading Multiple Excel Files

gilbertomedeiro — Mon, 16 May 2016 20:44:23 GMT

Hi Kushal, congrats!

It is very useful for me!

Thanks for sharing!

Re: Dynamically Loading Multiple Excel Files

psankepalli — Tue, 17 May 2016 14:41:44 GMT

Great job, very useful code.. thanks for sharing

Re: Dynamically Loading Multiple Excel Files

Anonymous — Fri, 19 Jan 2018 12:54:17 GMT

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