Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Is there a way to retreive Excel sheet name from QV?

Hi:

I have several Excel files, each one containing several sheets, but all have exactly the same structure. I've done something like this:

for counter = 1 to 10

Load a,b,c

FROM [*.xls] (biff, embedded labels, table is @$(counter));

next

The problem is that I can only read a fixed number of sheets (in this case 10), which is not always true. So here are my questions:

1.- Is there a way to use a "for each" clause refering to "each tab in each excel file" ? Using the Filelist will loop through all the Excel files, but I haven't found a way to loop through the tabs. The number of tabs is different in each file. I thought of using a big enough number in the "for" statement, but doing so will raise an exception when it doesn't find such a tab in the Excel File. Is there a TabExists() function I can use?

2.- is there a function sort of "ExcelTabName()" that I can use in the Load list? The reason I need this is that I need to know which period the data belongs to (this is the tab name, example, 20090401).

Thanks.

Ruben

Tags (1)
3 Replies
Employee
Employee

Is there a way to retreive Excel sheet name from QV?

Give this a try.

/Karl

Is there a way to retreive Excel sheet name from QV?

For some additional examples of handling multiple sheets, see the QV Cookbook example "Load multiple sheets from Excel workbook". You can download the WV Cookbook from http://robwunderlich.com/Download.html.

The example deals with a single xls file. If you want to process all xls file in a directory, an interesting alternative to using filelist is using the Excel driver as discussed in the "Notes" tab in the script. The filename (minus the .xls extension) will be in the SQLTABLES TABLE_CATALOG field.

-Rob

elizabethbamber
New Contributor II

Is there a way to retreive Excel sheet name from QV?

Hi

The methods of getting Excel tab names using an OLE or MSDASQL connection and "SQLTables" to list tab names works OK with QlikView but fails when a reload is carried out using QVServer9 (64bit). This is because there is no 64bit OLEDB provider for Jet. Has anyone found an alternative way to determine tab names from excel?

Thanks

Community Browser