OpenDataSource excel

[zzfive03]

I have this query:

SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Sample.xls";
Extended properties=Excel 8.0')...aco$

It spits everthhing out just fine, EXCEPT.. it has NULL for all numbers and dates (Any non alpha character), is there anythign i can do to my query to give me everything "as text"???

Thank you

 

[iBe]

In the query, identify the columns specifically in the SELECT statement and use the CAST or CONVERT functions to change the date time field to a char data type.

Such as,

 SELECT CAST(datefield AS varchar) AS convertedDateField
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
  'Data Source="c:\Sample.xls";
    Extended properties=Excel 8.0')...aco$

You may have to modify this a bit depending on the tools you are using, but this is a good place to start.

good luck ^_^

 

[zzfive03]

iBe, thank you,
I tryed that but i still get the same thing. I even tryed casting to binary, and the fields are simply NULL.

Its weird, like theres something wrong with my Excel file, because if I create a new xls, it works ok, but when i copy the cells out of this one, into my new working one, the new one stops working too. I wonder if theres some switch or proerty in Excel that causes these fields to not be viewable.

HUM...

 

[iBe]

What are the numbers/dates... what is their format in Excel?

Also, what are you using to execute the query?

 

[zzfive03]

Fixed, it, Looks like the first few lines had image objects and it was messing something up.

I wrote a query as so:
select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;DATABASE=c:\Sample.xls', 'Select * from [aco$A6:Z64000]')

and selected the range I wanted, and now it works.

MH