PostgreSQL copy from CSV with missing data values

Question

I'm trying to import a CSV file into PostgreSQL using COPY. It chokes when it hits a row where there are empty values, e.g. the second row below:

JAN-01-2001,1,2,3,4,5

JAN-02-2001,6,7,,,

I've tried this COPY statement, as well as variants using NULL and QUOTE and havent found anything that works.

COPY data FROM 'data.dat' USING DELIMITERS ',' CSV;

Any suggestions? The data file is in a massive 22GB flat file, so I'd like to avoid editing it directly.

Answer 1

I would suggest converting your numeric columns to text columns for the purposes of your import. The reason is that an empty string is not a valid numeric value. Change your numeric columns to text columns, import the CSV file, update the empty values to null or 0, and then change the column back to an integer.

Answer 2

Your statement is suspicious:

COPY data FROM 'data.dat' USING DELIMITERS ',' CSV;

DELIMITERS was used in versions before 7.3. It is still supported in order not to break old code, but don't use it any more. The proper keyword is DELIMITER. And you don't need to specify , at all as it is the default for FORMAT CSV.
Also, I quote the manual here:

filename

The absolute path name of the input or output file. Windows users might need to use an E'' string and double any backslashes used in the path name.

Bold emphasis mine. Replace 'data.dat' with something like '/path/to/data.dat' on UNIX or E'C:\\path\\to\\data.dat' on Windows.

For versions 7.3+ use:

COPY data FROM '/path/to/data.dat' CSV

For versions 9.0+ use:

COPY data FROM '/path/to/data.dat' (FORMAT CSV)

---

If you still get this error:

ERROR: invalid input syntax for type numeric:
CONTEXT: COPY data, line 13, column interval_2400:

Then, obviously, the source file does not match the structure of table data. Have a look at your source file, go to line 13 and see what value is there for column interval_2400. Chances are, it's not numeric. In particular, an empty string ('') is not allowed in columns of numeric type.

You can either fix the source file or adapt the table definition:

ALTER TABLE data ALTER COLUMN interval_2400 TYPE text;

Or whatever type is more appropriate. Might be interval, judging from the name. (But text accepts almost any input values.)

Or, better yet, create a modified temporary file, COPY to it, fix offending values, then INSERT into the target table, casting from text. See:

• How to ignore errors with psql \copy meta-command
• How to update selected rows with values from a CSV file in Postgres?

Answer 3

This is PostgreSQL bug - csv parser ignore last empty item and throw error - "PG::BadCopyFileFormat: ERROR: missing data for column".

i'm use a stupid hack:

If last item is empty, simple add a one delimiter to end of string:

1,2,3
1,2,,

This add missed last item in row to import data.

Answer 4

One additional caveat- Check the line number of the error and make sure it is not a blank row in the CSV file. That will cause postgres to throw the same error about missing values.

Answer 5

Anyone coming here with smaller files: here's the easiest fix I've found to this and inconsistent number of delimiters in a csv.

1. Open your CSV.
2. Ctrl + Shift + 8 (selects all data)
3. Ctrl + h (opens find replace)
4. Leave the find box blank so its looking for strings of 0 length.
5. Enter a space in the replace with box.

This will loop through the whole CSV and force it to have the correct column count in delimiters ( , ) even if theres no data in that column.

If you're alright with Excel you can turn this into a macro too, so my macro (Ctrl + g) does this in one go. Creating a Macro