topic Re: how to get excel spreadsheet tab name in QlikView

how to get excel spreadsheet tab name

Fri, 18 Jun 2010 15:09:12 GMT

Hi,

I have to load an excel file with different tabs.

Is there any function to determine each tab's name?

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.

how to get excel spreadsheet tab name

fernandotoledo — Fri, 18 Jun 2010 15:47:20 GMT

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

how to get excel spreadsheet tab name

Fri, 18 Jun 2010 15:54:37 GMT

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

how to get excel spreadsheet tab name

Fri, 18 Jun 2010 19:14:58 GMT

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

how to get excel spreadsheet tab name

fernandotoledo — Fri, 18 Jun 2010 19:23:17 GMT

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

how to get excel spreadsheet tab name

Fri, 18 Jun 2010 19:43:00 GMT

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!

how to get excel spreadsheet tab name

Wed, 11 Aug 2010 13:04:27 GMT

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) (ooxml, embedded labels, table is @1);

Re: how to get excel spreadsheet tab name

francoiscave — Mon, 15 Dec 2014 11:08:59 GMT

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;

François

Re: how to get excel spreadsheet tab name

vikraant — Fri, 16 Feb 2018 04:39:48 GMT

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