Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
What error you are getting while execution ?
please share your error message
-Sathish
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));
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]
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]
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.
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.
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.
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.