CODEWITHSUNDEEP
sql-servercsvbulkinsert
Tim
Tim

Reputation: 952

Bulk Insert Formatting Issue from CSV File

I am doing a bulk insert from a CSV file.

In one of my columns, I am using a colon such as this 36:21.0. For every row in this column I am getting the following error: 

"Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 11, column 3 (MyColumnName)."

Does anyone know a workaround to this so that I will be able to bulk insert the columns that have a colon in the data along with the rest of my columns?

Here is my query if you are interested: 

BULK INSERT dbo.[PropertyDefinition] FROM 
'//MY CSV FILE PATH HERE'
WITH(
        FIRSTROW = 2,
        DATAFILETYPE ='char',
        FIELDTERMINATOR = ',',
        ROWTERMINATOR = '\n'            
    )

Upvotes: 0

Views: 313

Answers (1)

Vivek Salvi
Vivek Salvi

Reputation: 87

Your query is correct.

I don't think that colon is causing the problem because the field-terminator and row-terminator does not include colon.

This problem is usually caused due to data type miss-match in the file and the table.

Just make sure that the datatype you are giving for column 3 is matching with the datatype of data in the file at row 11, column 3.

Upvotes: 1

Related Questions