Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have to load an excel file with different tabs.
Is there any function to determine each tab's name?
I saw a similar post http://community.qlik.com/forums/p/16690/65254.aspx#65254
but function tabbasename() doesn't exist.
Example:
C:\Files\abc.xls has tab Region, Store, and Customer.
I want a new field/variable called "Region" when I load in tab Region.
Please help and thank you in advance.
This is from a comment from Rob Wunderlich:
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
I don´t know a way to get the tab names from a excel, but if it is known before you run the script, you can use a variable in the "from" statement
FROM file.xls (biff, no labels, table is Fev05$);
becomes
FROM file.xls (biff, no labels, table is $(TABNAME)$);
and TABNAME should store the tab you want to load from.
Best regards
Fernando
Thanks Fernando,
but does that mean I have to determine the variable first by hard coding?
Can you tell me more about it?
This is what I want to do :
The columns in each tabs are the same.
QV will pick up whatever tabs in the excel file, and give it a column specifying the source(tab name).
Please tell me how do I achieve this by your method?
Thank you very much!! 🙂
This is from a comment from Rob Wunderlich:
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
I didn´t know that you can access a sheet by its number! Very nice tip, thanks.
Hey ptung17, can you use it this way?
In the case shown in the blog post you must create an ODBC connection with the datafile. The possibilites are infinite, but i´d rather simplify the XLS structure in this case than create a script too complex to read data from an xls file.
Thanks for the tip Tim!
Best regards,
Fernando
Thanks Tim and Fernando,
The link Tim provided is almost what I am looking for, except ODBC connection.
Same as Fernando, I think it's too complicated for a xls file. I guess for now I will hard code the sheet names but this could be a good practice.
Thanks again guys!
Hi Tim,
I very much like this suggestion but this does not work with Excel 2007 (XLSX). Do you know of anything for these files rather than enumerate the sheet names etc..?
eg.
FROM
$(vSourceFile) $(vSourceFile)$(vSourceFile) (ooxml, embedded labels, table is @1);
Hi Ptung 17,
Please, see the link below to have a good solution (by Massimo Grossi):
http://community.qlik.com/message/589469#589469
directory;
For Each vFile in FileList('Load_Test.xlsx')
ODBC CONNECT32 To [Excel Files;DBQ=$(vFile)];
Sheets:
SQLTABLES;
DISCONNECT;
For i = 0 To NoOfRows('Sheets')-1
Let vSheet = left(Peek('TABLE_NAME', i, 'Sheets'), len(Peek('TABLE_NAME', i, 'Sheets'))-1);
LOAD '$(vSheet)' as [Tab Name], *
From [$(vFile)]
(ooxml, embedded labels, table is $(vSheet));
Next;
Next;
François
In Qlik View 11.2 , this sql part(SQLTABLES) of the script was not getting highlighted. But it worked. Thanks