Hi,
I have created below query which is inserting the data into SQL from excel. Now the problem which is coming that some places it is showing data like "2.52206e+007".
Please suggest how to correct thks
SELECT [Account No],Curr Ph 1],[Curr Mobile],
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;HDR=YES;Database=E:\Upload_Files\Inventory.xls',
'SELECT * FROM [Sheet1$]')
In the Excel file, change the properties of the column to the right data type. Fix it in Excel and then it'll be fine in SQL.
I am not sure if user will do is there a way if i can do this in SQL
you may want to use SSIS for transforming the data types.
Can you setup an Excel template so that they don't have to make changes like this? Even with SSIS, I've had to fix the Excel file in the past. I am sure it can handle the 2.522e+007 number if you use a float, but you should test it.