Loading Multiple Excel Files and Multiple Excel sheets

[#1]

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.

[#2]

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]

Are your file a xls or a xlsx?

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

Regards,

Gabriel

[#4]

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]

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.

[#7]

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]

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]

Gysbert,

this is super-versatile. Thanks!