Several months ago a reader asked if there was any way for a QlikView document to know the names of the tabs or sheets within an Excel spreadsheet without using VBA or macro code. Well, I still don't know the answer to that question. But, there is a way for a loadscript to load data from an Excel spreadsheet without knowing the tab or sheet name -- it just loads from the "first" or leftmost tab if you don't specify the table parameter.
For example, these lines in your loadscript will load from the first tab of an Excel spreadsheet named aaadata.xls:
XLTBL:
LOAD *
FROM [aaadata.xls] (biff, embedded labels);
When I have time I'll see if I can combine that with a macro that does a browse-for-file dialog and also automatically builds a table-box object and then we'll have a general purpose QlikView document that loads almost any spreadsheet in a useful way.
Make sure to read the comment from Rob Wunderlich. Thanks, Rob!
QlikviewNotes has moved!
-
My QlikviewNotes blog has relocated to the Qlikview Cookbook site. All
links to the qlikviewnotes.blogger.com site have been automatically
redirected to th...
10 years ago
7 comments:
Qlikview can read from sheet by number. For example:
FROM [myfile.xls] (biff, no labels, table is @2);
That will read from the second sheet.
For more sophitiscated sheet selection, like selecting sheets that contain a specific string, you can enumerate the sheets using the SQLTables script statement. See this blog post for examples.
http://qlikviewnotes.blogspot.com/2008/09/loading-multiple-excel-sheets.html
Do you know if there is a possibility to load from the sheet-number if you are having Excel 2007/2010 ?
I have made a lot of tests, but did not succeed with Excel-versions higher than 2003 with
FROM [ExcelFile.xls] (biff, no labels, table is @2);
I have already reported this to QlikTech-Support, but did not really get an answer ... :(
Best regards
Stefan WALTHER
Hi Stefan,
You wouldn't specify biff for the newer version of Excel. Try building the Load statement using the wizard or you can try something like (ooxml, no labels, table is @2)
For Excel 2010 the syntax is with ooxml instead of biff.
For example:
FROM [ExcelFile.xls] (ooxml, embedded labels, table is @2)
This seams not to work fro ooxml, you will get an error that it cannot find the embedded labels. (at least in QV10SR1)
Look like it is still not working in ver 11.2
If you just leave out the "table is xxx" clause then QlikView will load from the first (or leftmost) worksheet tab in the Excel file.
Post a Comment