Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Reading from Excel-Sheets - how to define just the sheet-number

Hi,

when reading/importing from excel-sheets you normaly have to define the sheet-name.

Is there a possibility to define just the sheet-number (or index)?

Thanks in advance for your ideas!

Best regards

Stefan

1 Solution

Accepted Solutions
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

To read from a sheet number use the syntax "@n". For example, @2 to read from the second sheet.

FROM [myfile.xls] (biff, no labels, table is @2);

You are correct that this is not in the Cookbook example. I'll add it in the next version.

-Rob

View solution in original post

10 Replies
sparur
Specialist II
Specialist II

Hi, Stefan.

You find answer to your question in the cookbook created by Rob Wunderlich

http://robwunderlich.com/Download.html

best regards

Not applicable
Author


sparur wrote:
Hi, Stefan.
You find answer to your question in the cookbook created by Rob Wunderlich
http://robwunderlich.com/Download.html
best regards<div></div>


Hi,

within there is just an example "Load multiple sheets from workbook", but within this example during the loop (while loading the multiple sheets) there is also referenced the Sheet-Name and not the sheet-number!!!

Nethertheless, thanks!

Best regards

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

To read from a sheet number use the syntax "@n". For example, @2 to read from the second sheet.

FROM [myfile.xls] (biff, no labels, table is @2);

You are correct that this is not in the Cookbook example. I'll add it in the next version.

-Rob

Not applicable
Author

Argh, now I can remember ... I have had this solution but forgot it ;))

Thanks Rob & best regards

Stefan

Not applicable
Author

Hi Rob,

I have now made a demo; when using Excel 97-2003 your solution works, but with Excel 2007 this does not work; any ideas?


Best regards
Stefan

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

Stefan,

I would check with QT support on this one.

-Rob

Not applicable
Author

Hi all,

I also expierenced some difficulties during loading from Excel 2007 as Stefan did.

Next to this I am not able to load multiple sheets from one excel file.

Normally by loading FROM [myfile.xls] (biff, no labels) without specifying certain tablename all sheet should be loaded, or am I wrong here?

Or do you have another approach to load multiple tables from one excel file?

Best regards

Dominik

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

I'm not aware of an option that lets you load all sheets from an excel file with a single statement (maybe @*)?

If there isn't such an option, the QV Cookbook provides an example of how to enumerate the sheets and load them in a loop.

-Rob

Not applicable
Author

ok Rob, thanks for everything before anything,your support is vital but i have a question. How can i do if i want to retrieve the Excel Sheets sorted such as they are in the Excel? . If I retrieve the sheets with sqltables they are sorted alphabetically. is there any way,


Is there a way to achieve that?

thanks

Miguel Ramos