Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 - Lookup type of question | Excel Worksheet Functions | |||
Type A Question | New Users to Excel | |||
Type a Question for Help | Excel Discussion (Misc queries) | |||
how get rid of "Type a question for help" box | Excel Discussion (Misc queries) | |||
Sum if type question? | Excel Worksheet Functions |