Bulk Insert Type Mismatch T-SQL

Question

Am using this BULK INSERT command to insert a csv file in SQL Server 2012 Express.

BULK INSERT dbo.TS_FX
   FROM 'c:\fxRates.csv'
   WITH 
   (
      FIELDTERMINATOR =',',
      ROWTERMINATOR =',\n'
   );

The First Row on the fxRatesCSV file looks like this:

101,20130430,20130531,1.5307

The error I get is:

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 4 (FX_RATE).

My table (dbo.TS_FX) has 4 columns in it defined as follows

CURRENCY_ID (Numeric(3,0), null)
BEGIN_DATE (date, null)
END_DATE (date, null)
FX_RATE (float, null)

Clearly the error is the way I have defined the FX_RATE column, I have tried various other types (Decimal, Numeric) but I still get the same error

Any help is much appreciated.

Thanks

Can't reproduce - works just fine for me (SQL Server 2012 Developer). I wouldn't use `float` myself - it's not precise - use `decimal(10,4)` or something like that instead — marc_s, Jun 15 '13 at 15:50

score 0 · Accepted Answer · answered Jun 15 '13 at 15:55

0

Remove the comma from ROWTERMINATOR

Your BULK INSERT statement should look like this:

BULK INSERT dbo.TS_FX
FROM 'c:\fxRates.csv'
WITH (
     FIELDTERMINATOR =','
    ,ROWTERMINATOR ='\n'
);

answered Jun 15 '13 at 15:55

Bill Stidham

1,460
13
8

Bulk Insert Type Mismatch T-SQL

1 Answers1