current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
stack overflow careers
Sign up ×
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute:
up vote 10 down vote favorite

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.

share|improve this question
2  
And the error is? – Szymon Lipiński Dec 1 '11 at 20:37
1  
That's not full story. According to documentation and my check above case works well under any modern PostgreSQL (empty value with no quotes in CSV mode). Maybe you have NOT NULL modifier or invalid number of commas ? – Grzegorz Szpetkowski Dec 1 '11 at 23:43
    
ERROR: invalid input syntax for type numeric: CONTEXT: COPY data, line 13, column interval_2400: – ugh Dec 2 '11 at 19:43
    
If I edit the file and read in only the first 12 lines, the import works just fine. It only chokes when it gets to the 13th line, which is missing data values and looks like my example above. – ugh Dec 3 '11 at 3:56

3 Answers 3

up vote 3 down vote

With a question like that you should always include information about your operating system and version number of PostgreSQL.

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.

So, your 'data.dat' should be 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. You can either fix the source file or adapt the table definition:

ALTER TABLE data ALTER COLUMN interval_2400 TYPE text;

Or what ever type is more appropriate. Might be interval, judging from the name.

share|improve this answer
    
PostgreSQL 9.1.1, MacOSX 10.7.2. As I noted above, the import works just fine for the first 12 lines of the file. The columns are in fact numeric and match the table definition. It's just that some row have missing values. – ugh Dec 3 '11 at 3:53
    
@user1076175: Please edit your question with the first 15 rows of your data file. Also put the version information in your question (not in a comment to my answer). – Erwin Brandstetter Dec 3 '11 at 4:30
    
The main problem is that of corrupt csv of course. Right now I have a 50GB big csv which on every couple 1000 lines has a bad line with a field missing. So the question is could we tell postgresql to just go over the lines that throw error and continue the rest. – Smalcat Feb 19 '13 at 12:15
    
To my knowledge, that's not possible. You'll have to fix your CSV file or adapt the table definition like I outlined - possible COPY to a temporary table and insert into the main table after fixing incompatibilities. – Erwin Brandstetter Feb 19 '13 at 13:48
up vote 2 down vote

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.

share|improve this answer
    
In CSV format, nothing in between delimiters signifies NULL, not an empty string, which is valid syntax for a numeric value. Empty strings are written as empty double quotes: "". – Erwin Brandstetter Dec 3 '11 at 0:03
    
If anyone like me gets confused by Erwin's helpful comment about empty strings signifying blank in CSV files -- I was using a text (TSV) file...it's a different symbol there for blank values: '\N'. – Sigfried Nov 23 at 13:17
up vote 0 down vote

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.