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

[#1]

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

[sparur]

Hi, Stefan.

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

http://robwunderlich.com/Download.html

best regards

[#3]

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 · Accepted solution]

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

[#5]

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

Thanks Rob & best regards

Stefan

[#6]

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]

Stefan,

I would check with QT support on this one.

-Rob

[#8]

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]

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

[#10]

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