Error converting varchar to number 123,456

Question

I am trying to do a bulk insert from csv into a SQL Server database. Here is a snippet of a part of the query.

The data for this column in the CSV is in the format 123,345 with commas (it could also be null, or without any digit separator), I extract it as a string, and I am trying to replace the comma with nothing and cast it to a integer, in the format file the column is

<COLUMN SOURCE="10" NAME="abc" xsi:type="SQLVARYCHAR"/>

And the query is of the form

SELECT 
    CAST(REPLACE(abc, '','') AS NUMERIC) abc
FROM 
    OPENROWSET(BULK ''' + @myfile + ''',' +
               'FORMATFILE = ''' + @fomatfile + ''',' +
               'FIRSTROW = 0,' +
               'ROWS_PER_BATCH =' + CAST(@size as VARCHAR(4)) + '

But I get this error

Error converting varchar to numeric

for this particular column..

Remove the commas (`,`) first. You may find you need to `INSERT` the data into a staging table first, and then `INSERT` that data into your production table. *Instead of a ETL (Extraction, Transform, Load) process you have a ELT process.* — Thom A, Mar 08 '22 at 16:36

score 1 · Answer 1 · answered Mar 08 '22 at 22:25

You have an error in your REPLACE function, you should be replacing a ',' with an empty string '', whereas you are replacing an empty string '', with an empty string ''.

DECLARE @s varchar(10) = '123,456';

SELECT  CAST(REPLACE(@s, ',', '') AS NUMERIC) abc
GO

|    abc |
| -----: |
| 123456 |

db<>fiddle here

score 0 · Answer 2 · answered Mar 08 '22 at 17:17

One option is to change your import column to MONEY. It tends to be a little more forgiving.

Example

Select AsMoney  = try_convert(money,'123,345')
      ,AsInt    = try_convert(int,'123,345')
      ,AsDecimal= try_convert(decimal(15,2),'123,345')

Results -- Notice MONEY will convert

AsMoney     AsInt   AsDecimal
123345.00   NULL    NULL

Error converting varchar to number 123,456

2 Answers2