sql-servert-sqlcsvssmsbulk-load

Bulk Load Data Conversion Error - Can't Find Answer


For some reason I keep receiving the following error when trying to bulk insert a CSV file into SQL Express:

Bulk load data conversion error (type mismatch or invalid character for the 
specified codepage) for row 2, column 75 (Delta_SM_RR).
Msg 4864, Level 16, State 1, Line 89
Bulk load data conversion error (type mismatch or invalid character for the     
specified codepage) for row 3, column 75 (Delta_SM_RR).
Msg 4864, Level 16, State 1, Line 89
Bulk load data conversion error (type mismatch or invalid character for the   
specified codepage) for row 4, column 75 (Delta_SM_RR).
... etc.

I have been attempting to insert this column as both decimal and numeric, and keep receiving this same error (if I take out this column, the same error appears for the subsequent column).

Please see below for an example of the data, all data points within this column contain decimals and are all rounded after the third decimal point:

Delta_SM_RR
168.64
146.17
95.07
79.85
60.52
61.03
-4.11
-59.57
1563.09
354.36
114.78
253.46
451.5

Any sort of help or advice would be greatly appreciated as it seems that a number of people of SO have come across this issue. Also, if anyone knows of another automated way to load a CSV into SSMS, that would be a great help as well.

Edits:

Create Table Example_Table

(
  [Col_1] varchar(255),
  [Col_2] numeric(10,5),
  [Col_3] numeric(10,5),
  [Col_4] numeric(10,5),
  [Col_5] date,
  [Delta_SM_RR] numeric(10,5),
                              )

GO

BULK INSERT
Example_Table
FROM 'C:\pathway\file.csv'
WITH

(
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '\n',
    FIRSTROW = 2
);

Table Schema - This is a standalone table (further calculations and additional tables are built off of this single table, however at the time of bulk insert it is the only table)


Solution

  • It's likely that your data has an error in it. That is, that there is a character or value that can't be converted explicitly to NUMERIC or DECIMAL. One way to check this and fix it is to

    1. Change [Delta_SM_RR] numeric(10,5) to [Delta_SM_RR] nvarchar(256)
    2. Run the bulk insert
    3. Find your error row: select * from Example_Table where [Delta_SM_RR] like '%[^-.0-9]%'
    4. Fix the data at the source, or delete from Example_Table where [Delta_SM_RR] like '%[^-.0-9]%'

    The last statements returns/deletes rows where there is something other than a digit, period, or hyphen.

    For your date column you can follow the same logic above, by changing the column to VARCHAR, and then find your error by using ISDATE() to find the ones which can't be converted.