SQL Server string to int error when importing CSV

Question

I am trying to import a csv file into SQL Server 2017 using bulk insert. Most of the int columns, except the primary key have NULLs (as blanks in the csv file) but when I import I get an error that it can't convert a string to type int for the specified target column. Since I don't know which column it is affected, I went and replaced all the blanks with 0s, but I still get the game error.

Here is what I get after I import:

I don't know what to do to make this work, but it doesn't seem to make sense to me.

It is always better to use SSDT instead of Import/export wizard for errors like this or create a table on the fly as suggested by wizard and insert data from there — Ven
– Ven, Commented Sep 11, 2018 at 15:42
When you say it contains Nulls do you mean that the cell is blank or contains the string 'NULL'? An empty cell (NULL) and the string 'NULL' are not the same. — Thom A
– Thom A ♦, Commented Sep 11, 2018 at 15:42
Why does the error message not make sense? There is some row in your input where the value in the column to be converted to an integer does not contain integer data. See if you can find it by looking at the file in Excel (if it's not too big). Alternately, import all columns a text to a staging table, then use that to populate the destination table, fixing whatever is bad — user1443098
– user1443098, Commented Sep 11, 2018 at 15:44

Edward · Accepted Answer · 2018-09-11 16:18:24Z

1

The issue is not the NULLs. There is a column in your CSV that your are expecting to contain only integer values, but it in fact contains string values that cannot be converted to integers.

Here would be an example:

my_str_to_int_col
     1.0 
     2.0 
     3.O
     4.0 
     5.0 
     ...

Notice that 3.O should actually be 3.0

So you need to determine which column you are converting to an integer contains non-integer values.

answered Sep 11, 2018 at 16:18

Edward

7624 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Server string to int error when importing CSV

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related