Using OLEDB to read data from Excel

[cloudie]

Hie,

I've been trying to read an excel file using OLEDB. So far so good, I must say. However, problem arises when there are slashes or spaces in my headers. How should I construct my queries so that I can read the data under these headers?

Example
ID Name Location Name Price/Unit
22 Cat Food Cat Store 2.00
25 Dog Food Dog Store 2.50

Getting the IDs and the Names is easy.
SELECT ID, Name FROM [worksheet1$]

But how do I get the Location Names and the Price/Unit?

Thanks in advance

[jhardman]

Hie,

I've been trying to read an excel file using OLEDB. So far so good, I must say. However, problem arises when there are slashes or spaces in my headers. How should I construct my queries so that I can read the data under these headers?

Example
ID Name Location Name Price/Unit
22 Cat Food Cat Store 2.00
25 Dog Food Dog Store 2.50

Getting the IDs and the Names is easy.
SELECT ID, Name FROM [worksheet1$]

But how do I get the Location Names and the Price/Unit?

Thanks in advance

SELECT * FROM [worksheet1$]

you should be able to refer to all fields with the field name within square brackets, although generally you don't need to. When the field name has certain special characters (I always noticed that a hyphen caused this problem) you need to enclose the name in square brackets:

response.write rs("[Price/Unit]")
 

or when you make the query you can have the recordset use a different name:

query = "SELECT [Price/Unit] AS pU ..."
'...
 
response.write rs("pU")

Let me know if this helps

Jared