Issue with importing formatted numbers over 1,000 with COPY

Question

I have a Postgres database and have been trying to import a CSV file into a table with the code below. I keep getting the error

ERROR:  invalid input syntax for type numeric: " 1,183.26 "

I assume the issue is that there is a , in the value but when I go into the CSV and try to edit the value it seems like the , is added automatically by Excel.

COPY invtest5
FROM 'C:\Users\Hank\Downloads\SampleData\SampleDataCSV.csv' 
DELIMITER ',' 
CSV HEADER;

The table definition:

CREATE TABLE invtest5 (
    OrderDate date,
    Region varchar(255),
    Rep varchar(255),
    Item varchar(255),
    Units int,
    Unit_Cost numeric(15,3),
    Total numeric(15,3)
);

I am looking for a way to import the data whether or not the number has a , in it.

If you open the file with a text editor not Excel do you see the commas in the numbers or is Excel 'helpfully' adding that for you? And if it does have the , is the value enclosed in quotes? — Reeza, Aug 27 '20 at 21:29
@Reeza It still shows the comma in a text editor, in quotes, but when I click on the cell in Excel it doesn't show it as being there when I input the value. So I think Excel is adding them but also not letting me delete them. For example if I click on the cell of the `1,183.26` it shows up as `1183.26` when I edit it but afterwards it goes back to having a comma. — Hercislife, Aug 27 '20 at 22:03

Erwin Brandstetter · Accepted Answer · 2020-08-28T22:59:43.497

' 1,183.26 ' is not a valid numeric literal. COPY is fast and simple, but not fault-tolerant. Requires valid input.

Some options to fix:

Format numbers in Excel without "group separator" (that's what the noise , is).
Edit the CSV to remove group separators. (But don't remove other commas!)
If you can afford to ALTER the column type in the target table (i.e. no concurrent load on the DB, you have the necessary privileges, and no depending objects that would block), you could:

ALTER TABLE invtest5
  ALTER unit_cost TYPE text
, ALTER total     TYPE text;     -- both columns?

COPY ...

ALTER TABLE invtest5
  ALTER unit_cost TYPE numeric(15,3) USING (replace(unit_cost, ',', '')::numeric)
, ALTER total     TYPE numeric(15,3) USING (replace(total    , ',', '')::numeric);

The expression (replace(unit_cost, ',', '')::numeric) removes all commas before casting to numeric.

Leading and trailing whitespace is trimmed in the cast automatically.

If there are already some rows in the table, existing values are cast back and forth, too, which triggers a whole table rewrite and bloats the table. Not efficient for big tables.

If you cannot easily fix your CSV and cannot afford to tinker with the target table (or just don't want to bloat it), use a temporary staging table as COPY target, then INSERT from there:

CREATE TEMP tmp_invtest5 AS TABLE invtest5 LIMIT 0;  -- copy basic structure

ALTER TABLE tmp_invtest5
  ALTER unit_cost TYPE text
, ALTER total     TYPE text;     -- both columns?

COPY TO tmp_invtest5 ...

INSERT INTO invtest5 
      (orderdate, region, rep, item, units, unit_cost, total)
SELECT orderdate, region, rep, item, units, replace(unit_cost, ',', '')::numeric
                                          , replace(total    , ',', '')::numeric
FROM   tmp_invtest5
-- ORDER BY ??? -- while being at it?

The temporary table is dropped automatically at the end of the session. If you need it gone before that, DROP TABLE tmp_invtest5;.

Thank you for the help! I ended up just altering the type of the columns. Just when I started to think I knew SQL this hit me haha. I appreciate the help! — Hercislife, Aug 28 '20 at 14:55

Issue with importing formatted numbers over 1,000 with COPY

1 Answers1