Skip to main content
Announcements
Save $600 on Qlik Connect registration! Sign up by Dec. 6 to get an extra $100 off with code CYBERSAVE: REGISTER

Dynamically Loading Multiple Excel Files with Multiple Sheets

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

Dynamically Loading Multiple Excel Files with Multiple Sheets

Last Update:

Sep 25, 2024 2:20:24 PM

Updated By:

Kushal_Chawda

Created date:

Jan 4, 2016 1:02:33 PM

Attachments

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

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:
‎2024-09-25 02:20 PM
Updated by: