Implicit conversion of data type

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

Implicit conversion of data type

Author

Topic

jgzabala@yahoo.com
Starting Member

18 Posts

Posted - 2006-10-03 : 21:52:02

Dear all,

Hi, I'm using this code to export record from sql to excel and i got this error message "Implicit conversion from data type text to nvarchar is not allowed. use the convert function to run this query"

Excel file is already created columns in the view and excel file are the same and cell format of the excel is converted to text.

--- code used
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=c:\filename.xls;',
'SELECT * FROM [filename$]') select * from ViewName

thanks,

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-10-04 : 01:34:06

I think SQL server has pretty nicely pointed out the error and its resolution. I think ViewName table(or view ?) contains some text column which you wish to write to excel file...instead of select *, pls. mention individual columns and explicitly all text columns to nvarchar like this:

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=c:\filename.xls;', 
'SELECT * FROM [filename$]') 
select col1, col2, convert(nvarchar(2000),textcol) as textcol from ViewName

Harsh Athalye
India.
"Nothing is Impossible"

jgzabala@yahoo.com
Starting Member

18 Posts

Posted - 2006-10-04 : 05:22:30

thanks, actually I already corrected the error, I already remove the convertion of the fields (to text) in the view that I'm using, but my problem now is that when the record is exported to excel some of the cell type was change to "General", it seems that when the field is null or no value the next cell type in excel and the succeding cell type will be type as "General".

Also is there a way to retain the cell type in cell to text? or once record is exported to excel from sql cell type will be equal to text?

thanks again :-)

webalchemy
Starting Member

4 Posts

Posted - 2006-10-30 : 15:00:46

I'm having the same problem, posted here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74032

I can't get numbers to export as numbers to excel, dates as dates, etc.

Any help would be appreciated!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-10-31 : 10:29:03

convert them all to text and fix the formatting in the excel file.

Go with the flow & have fun! Else fight the flow