ASP.Net SqlBulkCopy some text data type values converting to exponential values
bulkCopy.DestinationTableName = "TABLE NAME"; bulkCopy.ColumnMappings.Add("Mobilenumber", "Mobilenumber"); bulkCopy.WriteToServer(dr);
above code inserting excel column mobilenumbers of text data type to database column mobilenumber varchar(100)
Some numbers are inserting without any change but some numbers inserting as exponential values.
If datatype of excel column changed to Number with leading zeroes is 0 then without converting into exponential , values are inserting into database table.
My requirement is without converting any sheet datatype (To the sheet only) manually, i have to upload because i have to upload many files. Please, can any one help on this.
By using the below connection strings and changing the setting for TypeGuessRows=0 in the registry of Jet and ACE.
ExelconnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filname + ";Extended Properties=\"Excel 8.0;IMEX=1;ImportMixedTypes=Text;TypeGuessRows=0;\""; ExelconnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filname + ";Extended Properties='Excel 12.0;IMEX=1;ImportMixedTypes=Text;TypeGuessRows=0';";