Extra decimal places when importing from Excel

[mojoT]

Hi

I'm trying to import data from Excel into SQL Server via DTS. The columns in the destination tables are all varchar(255).

I have a column in Excel where the values for all rows is 3.1, however this value becomes 3.100000000001 once it's been imported into SQL Server.

Can anyone shed some light on why this is happening? I've tried formatting the cells as text and re-entering the data, but it still happens. I was hoping that including "IMEX=1;" in the connection string would fix it (I had to do that for another problem, but unfortunately it didn't.

I'm using a straight datapump task, mapping the excel columns to the table columns without specifying a query.

ANY help would be greatly appreciated.

 

[GJParker]

Change the field data type to numeric and set a precision of 1 in the SQL database.

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[mojoT]

While 3.1 is the only value for that column at the moment, it is highly likely that there will be valid alphanumeric entries, for example, 3.a.1 etc... therefore 3.1 needs to be treated as a varchar, not as a number.

The table that the data is being imported into has varchar(255) for all columns because it is being used as a staging table, so that all imported data can be validated (failing records that don't have the correct datatypes, lengths, and violate business rules, and then reporting on these errors to the business so they can tidy up their end) and then inserted into another database.

ANYWAYS, I managed to get it to work today. My original idea of including IMEX=1 in the Excel connection string was on the right track. However because all values in the column are currently 3.1 it bypassed the whole mixed mode thing that IMEX=1 deals with. To get it to work, I had to format the column as text, then re-enter just the first line so that Excel treated it as a number stored as text, therefore the column had mixed data types, and they came over properly rather than being converted to scientific numbers.

Unfortunately, I was hoping to get away with not doing any manual changes to the spreadsheet. I just wish silly DTS/Excel would do it properly.

Thanks for your friendly help though. Much appreciated