Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Not applicable

Re: Loading Multiple Excel Files and Multiple Excel sheets

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.

gabriel_kirst
Contributor III

Re: Loading Multiple Excel Files and Multiple Excel sheets

Are your file a xls or a xlsx?

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

Regards,

Gabriel

Not applicable

Re: Loading Multiple Excel Files and Multiple Excel sheets

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];

MVP & Luminary
MVP & Luminary

Re: Loading Multiple Excel Files and Multiple Excel sheets

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

-Rob

Highlighted
MVP & Luminary
MVP & Luminary

Re: Loading Multiple Excel Files and Multiple Excel sheets

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


talk is cheap, supply exceeds demand
Not applicable

Re: Loading Multiple Excel Files and Multiple Excel sheets

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
Contributor

Re: Loading Multiple Excel Files and Multiple Excel sheets

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
Contributor II

Re: Loading Multiple Excel Files and Multiple Excel sheets

Gysbert,

this is super-versatile. Thanks!