type problem of data transition between sqlserver and excel

[fairyvoice]

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

[Todd C]

SSIS treats Excel data with contempt. It is one of the biggest flaws of SSIS,
as far as I am concerned.

Try using an OLE Db connector for Jet 4.0 (NOT an Excel Connection Manager).
In there you can specify properties. Specify the Extended Properties as
"Excel 8.0;IMEX=1;HDR=Yes"

The IMEX=1 setting tells Excel to read all 'intermixed' columns as text.

HTH
=====
Todd C

Post by fairyvoice
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

[Todd C]

SSIS treats Excel data with contempt. It is one of the biggest flaws of SSIS,
as far as I am concerned.

Try using an OLE Db connector for Jet 4.0 (NOT an Excel Connection Manager).
In there you can specify properties. Specify the Extended Properties as
"Excel 8.0;IMEX=1;HDR=Yes"

The IMEX=1 setting tells Excel to read all 'intermixed' columns as text.

HTH
=====
Todd C

Post by fairyvoice
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