i'm trying to import an excel file into SQL Server but if any of the column containing number value like "12355484645" and when it's inserted into SQL Server, the value will become "1.05e+010". Beside the methods of adding "'" in front the value or formatting the excel column, how do i convert the format in asp.net code-behind?
A:
The reason is because ADO.net guessing of the data type with the first 8 rows, and more.
There is more explication about the problem and how to fix it
Cédric Boivin
2009-07-23 04:41:10
one thousand above! the value before is something like "DCI2WAUDMYR01062009" and it's OK in the SQL Server
WeeShian
2009-07-23 04:44:06
Because i know that excel try to determine the data type of the colum in the first 8 row value of the same column
Cédric Boivin
2009-07-23 04:47:05
gt any idea how to fix this?
WeeShian
2009-07-23 04:49:28
I know ther a registry key to change that feature, i try to find it
Cédric Boivin
2009-07-23 04:50:36
There an example what i talk about. http://blog.lab49.com/archives/196
Cédric Boivin
2009-07-23 04:55:53
Is it help you ?
Cédric Boivin
2009-07-23 04:58:46
So there is your problem, i already having this trouble .. I change the registry key and set to 1 so analyse the first row to determine the data type.
Cédric Boivin
2009-07-23 05:05:12
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text;""" IS THIS THE WAY ON SETTING THE REGISTRY?
WeeShian
2009-07-23 05:30:00
+1
A:
i have tried several solutions and none of them were work out! Finally, i have my column formatted as General in excel file and it solved my prob!
WeeShian
2009-07-23 08:47:48
A:
You can convert your file in xml with xsd.exe tools in visual studio, and deserialize your xml file into a class and work with the class.
Cédric Boivin
2009-07-23 12:38:20