question about the data type in excel
 

Hi, I encounter a problem when doing some data transition work from excel to
sql server, there is a column in the excel sheet in which most contents are
integer but still some are words, after the transition I found in the
coresponding table of sql that the rows whose value is not integer in excel
are set as null, seems the excel treat the column as int type so the words
are omitted, I tried to change the class of cell into "text" the column's
property the still the same thing happens. I tried both DTS in sqlserver and
write c# code through OleDb to do this job and got the same result. I just
want get the values as varchars with non-integer ones reserved. What should I
do ?

p.s in some test I tried I found that if a column contains mostly alphabet
words and few integers , the integers will be set as null after the
transitions, so looks like the excel will give the column a type according to
the type of its most rows. All I want to to get all the values as varchars,
please helpl, thanks

question about the data type in excel
 

I can't tell if the problem is occuring in excel or when the data is stored
on the SQL server.

Try these two things.

1) Declare a variable as a string in excel and store the data into the
string variable in excel before storing the data in the SQL server.

2) Put the string in single quotes before saving on the SQL Server

Dim MyString as String

MyString = range("A1")
MyString = chr(39) & MyString & chr(39)


"fairyvoice" wrote:


Hi, I encounter a problem when doing some data transition work from excel to
sql server, there is a column in the excel sheet in which most contents are
integer but still some are words, after the transition I found in the
coresponding table of sql that the rows whose value is not integer in excel
are set as null, seems the excel treat the column as int type so the words
are omitted, I tried to change the class of cell into "text" the column's
property the still the same thing happens. I tried both DTS in sqlserver and
write c# code through OleDb to do this job and got the same result. I just
want get the values as varchars with non-integer ones reserved. What should I
do ?

p.s in some test I tried I found that if a column contains mostly alphabet
words and few integers , the integers will be set as null after the
transitions, so looks like the excel will give the column a type according to
the type of its most rows. All I want to to get all the values as varchars,
please helpl, thanks