0

I get a file from another department that contains an id column. The id column is mostly numbers, but also contains some letters.

I receive the file in XLS format.

When I try to use the import function of SQL, it reads the id column as a double integer type. Is that not incorrect? Can double datatype have letters? Either way, it is correctly importing the number values for the id, but not the values that begin with letters. It also does not advise of any errors or problems.

I have tried to convert it to a flat file and import it that way, but I am fighting with the date column data types as well. (They are in 1/13/2011 format - I don't know why the import wizard can't import this either).

I would like the end result to be a nvarchar. How can I go about getting this file to convert properly?

2 Answers 2

0

The ID should definitely be an NVARCHAR and not a double, you can change the type it thinks it is in the mappings. Depending on your data validation settings it may be truncating or nullifying your non-double IDs to fit them.

As for the date issue, I can't seem to get that particular style of date to fail an import. If there is more to that field than just the date portion it's possible the time portion is causing the problem.

1
  • Thanks for the reply. I am trying to get the field to be a nvarchar. How can I change the data type it thinks it is? I am using the import wizard, set to the excel connector, and I can't change the column types.
    – Chance
    Commented Mar 2, 2016 at 17:14
0

I found another post that ended up solving my problem. My process is usually handled through SSIS, which I know I didn't mention in my original post.

The end solution would be to edit the properties of excel to sample more rows so it could more easily determine the data type. But this solution worked for me.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.