How to use OLEDB and Reserved Chars

[Tyler Davey]

All,

I have used OLEDB to gather the data out of Excel using
simple queries such as select * from [Sheet1$]; However,
if my excel sheet has a name like "Test Sheet" or my
columns have titles like "Test.Column", it gets returned
to my dataset with a table name 'Test Sheet' (displaying
the single quotes) and a column name as Test#Column .

How can I fix this, or is there even a fix for it?

Thanks,

Tyler

 

[Jamie Collins]

...

I have used OLEDB to gather the data out of Excel using
simple queries such as select * from [Sheet1$]; However,
if my excel sheet has a name like "Test Sheet" or my
columns have titles like "Test.Column", it gets returned
to my dataset with a table name 'Test Sheet' (displaying
the single quotes) and a column name as Test#Column .

Have you tried aliasing them e.g.

SELECT
[Test#Column] AS [Test Column]
FROM
['Test Sheet$'] AS [Test Sheet]
;

Jamie.

--

 

[Guest]

Cannot do it that way as I don't know what the column
names are ahead of time. Has to be dynamic.

Thanks though,

Tyler

-----Original Message-----
...

I have used OLEDB to gather the data out of Excel using
simple queries such as select * from [Sheet1$]; However,
if my excel sheet has a name like "Test Sheet" or my
columns have titles like "Test.Column", it gets returned
to my dataset with a table name 'Test Sheet' (displaying
the single quotes) and a column name as Test#Column .

Have you tried aliasing them e.g.

SELECT
[Test#Column] AS [Test Column]
FROM
['Test Sheet$'] AS [Test Sheet]
;

Jamie.

 

[Jamie Collins]

SELECT
[Test#Column] AS [Test Column]
FROM
['Test Sheet$'] AS [Test Sheet]
;

Cannot do it that way as I don't know what the column
names are ahead of time. Has to be dynamic.

Are you using ADO? If so, you can use the OpenSchema method to get the
names of the Excel tables their columns. You can use this information
to construct a select query using appropriate aliases you require.
This is what is done for you under the covers anyhow when you use

SELECT * FROM ...

except *you* would be in control of the aliasing.

Otherwise, if your app need to be *that* dynamic them you must take
whatever you get.

Jamie.

--