Dynamically Loading Multiple Excel Files with Multiple Sheets

[Kushal_Chawda]

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

[andreicatalin]

Hi 

A couple of questions regarding this:

1. Is it applicable to Qlik Sense?

I am trying to connect to a number of excel files that all have 3 hidden identically structured tables, which I want to connect & collate and store in a new data file that will be used for creating dashboards.

2.  Could this be expanded to connecting to SharePoint?

[Rev1]

Hi Kush

I am trying to Load CSV files from a LIB and I am getting an error. something like LIB CONNECT not supported or something. I have also tried Load * from [lib://Folder/*.csv] which is creating a lot of $syn and stuck on the data load window. Can you please help?

Thanks for your help in advance.

Regards

Revanth

[Vlad_RO]

Hi, Kushal! @Kushal_Chawda I know it's an old post, but I have a similar need now:

I need to load multiple Excel Files (.xlsx) from a folder on OneDrive and save the .QVD in a lib folder "DataFiles". All the Excel have the same structures, same sheet name, same columns.

I'm trying to adapt your code to it but it doesn't seem to work.

I was thinking that a simple wildcard search * would work, but it doesn't, I'm getting the error: "Connector error: Failed on attempt 1 to GET. (400 A potentially dangerous Request.Path value was detected from the client (*).)"

Many thanks!