Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Loading Multiple Excel Files and Multiple Excel sheets

Hi All,

I need help in loading Multiple Excel Files and Multiple Excel sheets.

I am developing an application on my local machine . I have a folder on my local machine(Not on server).

I have few excel files with different names. Each Excel file has different sheets(Each sheet name represents each table name) and with different names(No pattern).

Now I need to load all these excel files.

Can any one help me with proper code?

Please don't provide any links.. I have tried few codes but not working so please provide the code which can satisfy my requirement.

Appreciate for your response.

17 Replies
Not applicable
Author

Hi gabriel_kirst ,

Everything is fine. I have made it proper

let vDataFolder = 'C:\Users\Axd\Sample\';

But still it is not working.

Can I get the Sample application with code ?? I have attached the sample Excel.

Anonymous
Not applicable
Author

Are your file a xls or a xlsx?

'Cause the for each is configured to read .xlsx files.

Regards,

Gabriel

Not applicable
Author

Hi gabriel_kirst ,


I had tried that as well: Look below for the script:


let vDataFolder = 'C:\Users\Axd\Sample\';

for each vFile in filelist('$(vDataFolder)*.xls')

  OLEDB CONNECT32 TO [Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DSN=Excel Files;DBQ=$(vFile);DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"];

  Tables:

  SQLTables ;

  let vFileName = mid(vFile, index(vFile, '\', -1) + 1);

  for iSheet = 0 to NoOfRows('Tables') - 1

  Data:

  LOAD *

// '$(sheetName)' as Sheet

  FROM [$(vFile)]

  (ooxml, no labels, table is [$(vSheetName)]);

  next

  DROP TABLE Tables;

next

I am getting Error which I have attached.:

So I have changed SQL Tables; to SQLTables;

Then I am getting BadZip File

Data:

Load *

From  [C:\Users\Axd\Sample\Data.Xls];

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

According to the log it processed no files. It looks like you are missing the \ at the end of "Sample\".

-Rob

Gysbert_Wassenaar

You can find a working example in the attached zip file.


talk is cheap, supply exceeds demand
Not applicable
Author

Hi gwassenaar


Is it possible to attach the files Individually?? I am not able to download the ZIP file(Have problem in downloading ZIP files)


Thanks in advance



laujerry
Creator
Creator

hello, may i know how is this connection string generated?

i want to create a 64 bit connection and i have installed Microsoft ACE OLEDB 12.0

but which oledb provider should i choose?

thanks

andreas_koehler
Creator II
Creator II

Gysbert,

this is super-versatile. Thanks!