Load Multiple Sheets...sheets are Dynamic

[pra_kale]

Hi,

I have found below given script to load multiple excel sheets, but in the script I am getting error at below given positions  I do't know the reason...secondly I want to store the this data into one Table so I can use this Table as a Resident in further script.. 

tables:

SQLtables;

Full script is given below..

For Each file in FileList('E:\Qlikview\Lookin.xls');

ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];

tables:

SQLtables;

DISCONNECT;

FOR i = 0 to NoOfRows('tables')-1

LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));

Table:

Load *

From $(file)(ooxml, embedded labels, table is [$(sheetName)]);

NEXT i

Drop table tables;

Next file

Please suggest what changes I should do in the script to load sheets. Sheets are Dynamic not fixed(Every Months depending on the data excel sheets will increased)

Thanks in Advance

[avinashelite]

you could install the driver from the below URL

https://www.microsoft.com/en-us/download/confirmation.aspx?id=13255

in User DSN clicked on Add and selected the Microsoft Excel Driver. Named it as "Excel Files"

[petter]

Here is the driver-less way of getting the sheet names. This is also an Unzip-routine that does not need any special unzip program to work. It takes advantage of the unzip functionality that is present in any Windows operating system.

[pra_kale]

Thank You Very Much for your Help...Great.

I have used the  script which you have given, below are the observations..

1) Script is working for .xlsx files.

2) The excel file which I have is .xls i.e. 2003 so when I have tried this on .xls file the script is failed..it gives error on app.xml...Please suggest what and where I should make the change.

3) When I have tried on .xlsx file as well only sheets names get's imported. But I want data on that sheets to be get imported so I can use further for calculation. Means I want this data to be get saved in a Table...

Thanks in Advance.

[petter]

As for point 2:

This is not meant for XLS but only for XLSX. And you already have support for getting all the sheets with XLS natively in QlikView. Unfortunately this was left out when the XLSX-fomat was introduced. You didn't ask for a routine for XLS only for XLSX. To read all sheets of XLS-files is quite easy since with XLS-files you can write table is @1 for the first sheet and table is @2 for the second sheet and so on.

As for point 3:

That is absolutely correct - I only gave you what you need as a replacement for what you highlighted in red. The rest of what you have written with a FOR NEXT loop should work with minimal adjustments.

[petter · Accepted solution]

This is an example of how it can be done with XLS-files:

SET ErrorMode = 0;
FOR i=1 TO 100
  LOAD
      $(i) AS SheetNum,
      VOYAGE,
      FREIGHT,
      [NET FREIGHT]
  FROM

  (biff, embedded labels, table is @$(i));
  EXIT FOR When ScriptError <> 0;
NEXT

[manishchandra_j]

Hi Petter,

Thanks for sharing the qvw file. But I am getting error related with "app.xml". Could you please explain what is it and if you can share it as well?

Thanks

[pra_kale]

Thanks a ton !!!!

The given code is working for .xls file.

Thanks once again Petter for your help.

[petter]

Happy to help