loading data from excel to sql

I am loading data from excel sheet to sql using SSIS package. When I load data into table1 using the datatype varchar(255) for all fields, I had no problem. But, when I tried to load data from table1 to table2 it showed an error : can not convert datatype varchar to numeric.

All of the fields in table2 have valid datatypes. Now when I look at the data in table1 for the field (its datatype is decimal(5,2) in table2) which was giving me that error, I saw one of the record had a value of "2.9999999999999999E-2" in table1. The same record in the excel sheet is 0.03.

In the same column there is a record with a v开发者_运维技巧alue of 0.01. Why did it change the value for 0.03? Do I have to convert the data in excel sheet? I want to load records from excel sheet the way they are. I am using sql server 2005.

Thanks

Odds are, that the 0.01 value is in a text cell in excel, while the 0.03 is in a numeric cell. Your library sees that it has a number, and tries to import it numerically. Floating point numbers can't represent certain numbers perfectly, which leads to your error.

One way to solve this is to mark the cells in the original spreadsheet as text, or if your library has the capability, mark them as text when importing the values.

Have you considered checking the connection string, particularly IMEX=1? This worked for me when I was facing similar issues in the past.

http://www.connectionstrings.com/excel

Sorry for the digging up of a 5 year old post, but it looks like a lot of people have looked at it.

I ran into a similar issue with numbers from excel coming over in the Scientific Notation format

Try:

CONVERT(FLOAT, [Your Column Here], 2)

Then wrap it into whatever type you need

Apparently when you get the Scientific Notation format don't work as expected.

This is how I found the answer see Quassnoi's post