Solved: Load Multiple Sheets...sheets are Dynamic - Qlik Community

pra_kale · ‎2016-02-09

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

petter · ‎2016-02-14

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

View solution in original post

Report Inappropriate Content · ‎2016-02-09

What error you are getting while execution ?

sathishkumar_go · ‎2016-02-09

please share your error message

-Sathish

settu_periasamy · ‎2016-02-09

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

pra_kale · ‎2016-02-09

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]

pra_kale · ‎2016-02-09

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]

petter · ‎2016-02-09

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.

petter · ‎2016-02-09

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.

pra_kale · ‎2016-02-09

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.

petter · ‎2016-02-09

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.