Issue:
Unable to import float data type values from excel to sql server though the text in the column contains string values.
Description:
The issue with JET Engine is that it guesses the datatype of the column based on the majority of the type of values in that column.
After a long research i could come across a work around. Basically there is no direct solution as it seems that this is a bug with JET engine.
Use OpenRowSet command with IMEX=1 in connection string, to import the data to sql server as it is in the excel.
Process below:
First check if "Ad Hoc Distributed Queries" setting is enabled in sql server.
Run sp_configure in query window and check the list of the configuration. If there are 14 list items and there is no Ad Hoc Distributed Queries then it should be enabled.
To enable "Ad Hoc Distributed Queries" follow below steps:
Run
sp_configure 'show advanced options', 1
reconfigure
Then run
sp_configure 'Ad Hoc Distributed Queries', 1
reconfigure
Now run below query to get the result set from excel
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Test.xls;IMEX=1;', 'SELECT * FROM [Sheet1$]')
Most important is that you should give IMEX=1 in the connection string since this is the one setting that is going to help us solve the issue. This command tells JET not to guess the datatype of the columns in the excel.
Now that you get proper data, you can insert the data into the required table using INSERT INTO ......... SELECT ...FROM format of sql
