topic Re: Load Multiple Sheets...sheets are Dynamic in QlikView

Load Multiple Sheets...sheets are Dynamic

pra_kale — Tue, 09 Feb 2016 16:05:18 GMT

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

Re: Load Multiple Sheets...sheets are Dynamic

Tue, 09 Feb 2016 16:23:46 GMT

What error you are getting while execution ?

Re: Load Multiple Sheets...sheets are Dynamic

sathishkumar_go — Tue, 09 Feb 2016 16:29:23 GMT

please share your error message

-Sathish

Re: Load Multiple Sheets...sheets are Dynamic

settu_periasamy — Tue, 09 Feb 2016 16:42:25 GMT

if it is 'xls',

Need to use biff..instead of ooxml

And sheetname should end with $ symbol..so you can remove one purgechar from your variable like below

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

and Change this

(biff, embedded labels, table is $(sheetName));

Re: Load Multiple Sheets...sheets are Dynamic

pra_kale — Tue, 09 Feb 2016 17:22:39 GMT

Hi,

I am getting below error message..

Script Error

SQL##f - SqlState: IM002, ErrorCode: 0, ErrorMsg: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

ODBC CONNECT32 TO [Excel Files;DBQ=E:\Qlikview\Lookin.xls]

Re: Load Multiple Sheets...sheets are Dynamic

pra_kale — Tue, 09 Feb 2016 17:23:46 GMT

I am getting below error message..

Error Message

SQL##f - SqlState: IM002, ErrorCode: 0, ErrorMsg: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

ODBC CONNECT32 TO [Excel Files;DBQ=E:\Qlikview\Lookin.xls]

Re: Load Multiple Sheets...sheets are Dynamic

petter — Tue, 09 Feb 2016 19:29:38 GMT

Are you sure that you have the Excel ODBC-driver installed on the computer you are running the script on? This is a symptom of not having the driver installed.

Re: Load Multiple Sheets...sheets are Dynamic

petter — Tue, 09 Feb 2016 20:25:18 GMT

It think you have the same problem as this thread already has solved:

Issue with deploying code reading multitab excel‌

You must download and install the drivers.

Re: Load Multiple Sheets...sheets are Dynamic

pra_kale — Wed, 10 Feb 2016 01:31:30 GMT

Hi,

Thanks for your Help..

But, is there any option by which without installing ODBC Driver I can achieve the desired output or I have to install ODBC Driver. Means by making changes in the script itself can I achieve this..

Please suggest..

Thanks in advance.

Re: Load Multiple Sheets...sheets are Dynamic

petter — Wed, 10 Feb 2016 02:49:34 GMT

Yes I recently found a way to extract the sheet names of an XLSX-file without needing to use the Excel ODBC-driver. I will share the solution once I get back at the computer I developed it on.

Re: Load Multiple Sheets...sheets are Dynamic

avinashelite — Wed, 10 Feb 2016 05:59:29 GMT

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"

Re: Load Multiple Sheets...sheets are Dynamic

petter — Wed, 10 Feb 2016 07:00:02 GMT

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.

Re: Load Multiple Sheets...sheets are Dynamic

pra_kale — Thu, 11 Feb 2016 15:33:44 GMT

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.

Re: Load Multiple Sheets...sheets are Dynamic

petter — Sun, 14 Feb 2016 23:38:54 GMT

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.

Re: Load Multiple Sheets...sheets are Dynamic

petter — Sun, 14 Feb 2016 23:59:01 GMT

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

Re: Load Multiple Sheets...sheets are Dynamic

manishchandra_j — Mon, 15 Feb 2016 16:04:08 GMT

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

Re: Load Multiple Sheets...sheets are Dynamic

pra_kale — Wed, 17 Feb 2016 15:45:48 GMT

Thanks a ton !!!!

The given code is working for .xls file.

Thanks once again Petter for your help.

Re: Load Multiple Sheets...sheets are Dynamic

petter — Wed, 17 Feb 2016 17:10:00 GMT

Happy to help