SQLTeam.com | Weblogs | Forums

Error in Numbers


#1

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$]')


#2

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.


#3

I am not sure if user will do is there a way if i can do this in SQL


#4

you may want to use SSIS for transforming the data types.


#5

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.