tab base name

[#1]

Hi,

I tried to find the previous discussions on FileBaseName for tabs but the links no longer work to old discussions since the forum was updated.

Does anyone know how to do FileBaseName() for tabs in a single spreadsheet?

David

[manishkumar75]

Hi,

FileBasename( ), Returns a string containing the name of the text file currently being read without path and extension.

Example:
Load *, filebasename() as X from
C:\UserFiles\abc.txt;

will return 'abc' in field X in each record.

Refer - QlikView Reference Manual

Regards,

- Manish

[#3]

Hi,

You mean to say, Your requirement is to get the name of the sheet from excel work book..?

If you want to do the above thing you need to use ODBC connection to Excel work book.

Please check the attached doc.

- Sridhar

[#4]

Hi Manish

I understand the FileBaseName function.

My question was actually if you could do the same thing but instead for multiple tabs on a single spreadsheet

E.g.

with multiple spreadsheets in a folder I use:

right(FileBaseName(),6) as Period

FROM (biff, embedded labels, table is Sheet1$);

which loads all of the spreadsheets in the folder "NewFolder" and uses the last 6 digits of each spreadsheets name as the period number for data in that spreadsheet. I use the naming convention where 10901 equals FY09 July 08, 10902 is FY09 August 08 which matches how our database numbers periods.

What I want to do is similar but:

right(TabBaseName(),6) as Period

FROM (biff, embedded labels, table is *$);

where the function is TabBaseName() (looking up the tab name so that a tab named Tab 10901 marks the data on that tab as being in period 10901) and the * is where the tab name usually appears so it loads all tabs in the spreadsheet and takes the last 6 digits of the tab name as the period.

I cant get TabBaseName to work as a function so I think that it needs to be something else. Also I dont know if placing a * in the tab name will load all tabs

So I am trying to achieve two things

- using the tab base name as a function

- loading all tabs in a single spreadsheet using *

Thanks

Dave

[rwunderlich]

As Sridhar suggested, you can get the sheet name by using SQLTABLES with an ODBC or OLEDB connection. For another example that uses OLEDB (no datasource required) , see the QV Cookbook example "Load multiple sheets from Excel workbook'. You can download the cookbook from http://robwunderlich.com/Download.html.

-Rob

[#6]

Hi Sridhar,

I have been trying to modify what you have done to work on my data set

Apolagies but im not an IT guy just the end user stuck with writing reports so Im not familiar with ODBC but I thought I'd first try and figure it out

From looking at your code, I tried to create an ODBC connection selecting the Qlikview ODBC Driver in ODBC Administration (Qlikview is installed on the server). It asked for a database so I chose the Qlikview report I was creating. I then changed your reference of:

ODBC CONNECT TO [Test_Comm_ODBC_Excel;DBQ=C:\DOCUMENTS AND SETTINGS\MYUSER\Desktop\New Microsoft Excel Worksheet.xls];

to:

ODBC CONNECT TO [test_270509;DBQ=Z:\Salary Claims July 2008 to June 2009 test.xls]

test_270509 is the name Qlikview ODBC driver and the Z:\Sala.... is the file path with the multiple tabs.

this gives me a Garbage after ODBC CONNECT TO [test_270509;DBQ=Z:\Salary Claims July 2008 to June 2009 test.xls] error.

Am I even on the right track?

Hi Rob, I downloaded the cookbook but was very confused not knowing if I have access to OLEDB, thought I'd try Sridhar's code first

Thanks

David

[#7]

Normal 0 false false false MicrosoftInternetExplorer4

HI,

No Worries David. Qlikview is very simple tool; even end user can develop their reports by them self.

If you still stuck, we (Forum ppls) are here to help you. Follow the below steps to create ODBC.

Control Panel --> Administrative Tool --> User DSN --> ADD --> Select Driver do microsoft excel(*.xls) form the list.

Say Finish. It will take to another window where you need to define your data source name and Discp. You can find the button select Work book to select your work book.

Once you done with these steps say ok and come out.

Go to Qlikview and select ODBC connect and connect your DSN.

After connecting qlikview with ODBC connection, Use my code and reload.

I believe this will help you.

Cheers

- Sridhar

[#8]

Thanks, i have it working

I have 3 tabs called 10901,10902,10903 when I bring them in I get this result

TABLE_NAME	record
'10901$'	1
'10901$'Print_Area	2
'10902$'	3
'10902$'Print_Area	4
'10903$'	5
'10903$'Print_Area	6

I dont know why mine is different from yours and there is a double up with "Print_Area" at the end

Anyway how to I start to load the fields now it is picking up all the columns?

Thanks,

David

[#9]

Hi Rob,

Was just playing with your code to try and understand it and got it working with my data.

Sridhar pointed me in the right direction for the ODBC connection which helped.

I used the connection "ODBC CONNECT TO [SalaryClaimsFY09;DBQ=Z:\Salary Claims July 2008 to June 2009 test.xls];" and it linked all the data in.

I am going to try and study the code to know exactly how it works.

Feel a little like a monkey who dropped a coconut on a rock and cracked it open my mistake, what luck. sometimes right before you go home for the day it all just works

Thanks for all the assistance, anymore on this topic would be appreciated.

Dave