How to access excel files with ODBC? 2

[Xantix]

I have a simple excel file with 2 columns and I would like to access it with the odbc.
I tried to configure the odbc for this task but when I want to perform a query I have a message telling me that there is no table to add to my query.
So I assume that there is a way to define tables in a excel file. Could you tell me how to do this?
Thanks.

 

[luceze]

What database software are you using?

 

[Xantix]

Impromptu from Cognos.

 

[Xantix]

Now that I think about it, the software is not an issue.
What matters is that even with a basic SQL query there is always that table problem : "select * from ???"...

 

[luceze]

This is kind of an obtuse way around it but try linking a MSAccess table to the spreadsheet then try your SQLQuery with MSAccess ODBC.

 

[xlbo]

You could try setting up range names in excel - most query software seems to have the capability to look at range names in excel.....
Or, within a new excel , try using Data>Get External data and pulling data from your excel worksheet - this GUI interface will create the SQL for you so it may give you some help in writing your own.....

HTH Rgds
~Geoff~

 

[Xantix]

Ok, I found it!

You must select the columns you want to appear as table.
Then you go in INSERT -> NAME -> DEFINE, there you enter a name, table1 for example.
Now, when you link to this excel file with the odbc, a table named table1 appears and you can query in it.

Thanks anyway for your help...

 

[xlbo]

Isn't that what I said....Range Names ??????
;-) ;-) ;-) Rgds
~Geoff~

 

[beniez]

Xantix

You're right (so, of course is xlbo!) - use range names. Just be careful about the end of your range. I've come unstuck by defining the range to end where my data does; then wondered why records added at the end aren't picked up by the query! Either include some empty rows at the end, or only add records by inserting within the range, or check periodically that your range is long enough.

HTH

Ben

 

[xlbo]

Or use dynamic ranges:
For a single column:
=OFFSET(SheetName!$A$2,0,0,COUNTA(SheetName!$A:$A)-1,1)
This will create a dynamic range name for column A

For multiple columns:
=OFFSET(SheetName!$A$1,0,0,COUNTA(SheetName!$A:$A),COUNTA(SheetName!$1:$1))
This will create a dynamic range for however many columns have data in row 1 and for however many rows there are

Dynamic ranges expand as data is added to the end of a column so you never need to reset them. The only thing you have to watch out for is that if there are EMPTY cells the counta doesn't work properly and you will get a truncated range

To create the ranges, enter the formulae above into the refers to box

HTH Rgds
~Geoff~

 

[beniez]

xlbo

That's really useful - solves a problem that's been nagging at me.... Thanks

Ben

 

[DeLaMartre]

Great advice, Geoff! Thanks a million! Worth a dozen stars! -Bob in California