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