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?
Add a comment
|
3 Answers
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!
Comments
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
5 Comments
Fire Hand
one thousand above! the value before is something like "DCI2WAUDMYR01062009" and it's OK in the SQL Server
Cédric Boivin
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
I know ther a registry key to change that feature, i try to find it
Cédric Boivin
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.
Fire Hand
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sSvrFile & ";Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;TypeGuessRows=0;ImportMixedTypes=Text;""" IS THIS THE WAY ON SETTING THE REGISTRY?
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.