Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

how to get excel spreadsheet tab name

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.

1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

8 Replies
fernandotoledo
Partner - Specialist
Partner - Specialist

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

Not applicable
Author

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!! 🙂

Not applicable
Author

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

fernandotoledo
Partner - Specialist
Partner - Specialist

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

Not applicable
Author

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!

Not applicable
Author

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





francoiscave
Partner - Creator III
Partner - Creator III

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;

1.png

François

vikraant
Creator
Creator

In Qlik View 11.2 , this sql part(SQLTABLES) of the script was not getting highlighted. But it worked. Thanks