Openrowset query

Question

Post reply

Openrowset query

Pavas Malviya

Ten Centuries

Points: 1109
More actions
June 27, 2005 at 12:08 am

#66662

Hi i have a excel sheet called as Data.xls.
Data in xls file is as follows
ReportDisplayname       SETORDER        SubSetOrder         Definition
REP1                                                                             DEF1
REP2                                                                             DEF2
REP3                             2.0                   1.0                     DEF3
No when i execute below query i get all NULL in place of set order and subset order.
select *
from OpenRowSet (
    'Microsoft.Jet.OLEDB.4.0'
    , 'Excel 8.0; DATABASE=c:\Data.xls'
    , 'select ReportDisplayname,SETORDER,SubSetOrder,Definition from [Sheet1$]'
    )
Resule from above query
ReportDisplayname       SETORDER        SubSetOrder         Definition
REP1                             NULL               NULL                      DEF1
REP2                             NULL               NULL                      DEF2
REP3                             NULL               NULL                     DEF3
Now if i change the same xls file to csv file i get correct data.?But it is not feasible to change the xls file to csv file.
I want to know what is the reason and how can i get correct values?

Kindest Regards,
Pavas
Dream The Dream
Explore the World
Experince The Exhilaration

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Pavas Malviya Ten Centuries Points: 1109 More actions · Answer 1

If we convert it into csv also it doesn't come.....

Please ignore the csv part of the aboove question.

I will again retype the question

My question is :-

Hi i have a excel sheet called as Data.xls.

Data in xls file is as follows

ReportDisplayname SETORDER SubSetOrder Definition

REP1 DEF1

REP2 DEF2

REP3 2.0 1.0 DEF3

No when i execute below query i get all NULL in place of set order and subset order.

select *

from OpenRowSet (

'Microsoft.Jet.OLEDB.4.0'

, 'Excel 8.0; DATABASE=c:\Data.xls'

, 'select ReportDisplayname,SETORDER,SubSetOrder,Definition from [Sheet1$]'

)

Result from above query

ReportDisplayname SETORDER SubSetOrder Definition

REP1 NULL NULL DEF1

REP2 NULL NULL DEF2

REP3 NULL NULL DEF3

I want to know what is the reason and how can i get correct values?

Kindest Regards,

Pavas

Dream The Dream
Explore the World
Experince The Exhilaration

David Burrows SSC Guru Points: 65142 More actions · Answer 2

see

http://www.sqldts.com/default.aspx?254

whilst it refers to DTS the problem is the same

Far away is close at hand in the images of elsewhere.
Anon.

Pavas Malviya Ten Centuries Points: 1109 More actions · Answer 3

Thanks,Let me have alook on the link u send me.

I have another issue i want the top header to be populated in a table.

If we dio it from below query top row is treated as aColumn Name.

Can any one suggest me on this

select *,Sheet1$

from OpenRowSet (

'Microsoft.Jet.OLEDB.4.0'

, 'Excel 8.0; DATABASE=c:\MyData.xls'

, 'select * from [Sheet1$],Sheet1$'

)

Kindest Regards,

Pavas

Dream The Dream
Explore the World
Experince The Exhilaration

David Burrows SSC Guru Points: 65142 More actions · Answer 4

Add extra property to connection string, e.g.

'Excel 8.0; DATABASE=c:\MyData.xls;HDR=YES'

will use first line of worksheet as column names

'Excel 8.0; DATABASE=c:\MyData.xls;HDR=NO'

will process the first line the same as the rest (as data) and you get default column names of F1, F2, F3 etc

Far away is close at hand in the images of elsewhere.
Anon.

Pavas Malviya Ten Centuries Points: 1109 More actions · Answer 5

Thanks a lot it worked!

Kindest Regards,

Pavas

Dream The Dream
Explore the World
Experince The Exhilaration