Dynamically Loading Multiple Excel Files

[Kushal_Chawda]

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

[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!