Data format issue with OPENROWSET

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Data format issue with OPENROWSET

Author

Topic

ruchijani
Starting Member

23 Posts

Posted - 2009-08-19 : 02:27:54

Hello

I m using openrowset to display data of excel file
col2 of excel file contain both text and number data but when i tried to display data with below query number converted to exponent format like number 20120596 converted to 2.01206e+007

I have already use IMEX=1 in my connection string but it did not work and if i use IMEX=0 then only text data of Col2 displayed.

select * from OpenRowset('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\temp\test.xls;HDR=YES;IMEX=1', 'select [col1],[col2],[col3] from [Sheet1$]')

Thanks
Ruchi

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-21 : 01:52:59

Before exporting data to EXCEL, format the cell as date

Madhivanan

Failing to plan is Planning to fail

ruchijani
Starting Member

23 Posts

Posted - 2009-08-21 : 02:21:35

Thank you Madhivanan for your reply

your answer is right but better solution i found is use of FORMAT like below
and it solve my problem

select * from OpenRowset('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\temp\test.xls;HDR=YES;IMEX=1', 'select [col1],Format([col2],''#'') As [col2],[col3] from [Sheet1$]')

Thanks
Ruchi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources