Import from .xls converts numerics to exponential form

[devRyan]

Hi All,

I was hoping someone could help me find the answer to a problem I'm having with an Excel data import.

I've got a field that is used for HR UserIDs, there is no rhyme or reason to the way these IDs are created, so some are numeric, some are alphanumeric.

I dealt with a problem that on import the datapump was trying to cascade the data type from what it was reading in the first few rows, by adding IMEX=1 to the Excel ->OLE DB ->Extended Properties section in the Disconnected Edit dialog, which did a wonderful job of just copying everything over as text.

But now, and probably before also, some of the longer numeric values, ie; between 10-20 chars, are placed into exponential form, then the text is copied as the number in exponential form. I don't want that. I want the full number. How can I get the import to not shorten the number string?

Thanks

Ryan

 

[AlexCuse]

What is the data type for the column you are importing to?

Ignorance of certain subjects is a great part of wisdom

 

[devRyan]

I am importing to a nvarchar field.

It's seems to have stopped making the unwanted conversion. Not sure what I've done differently, but I hope I don't change it back.

Thanks for the help.