Importing large CSV file into Postgres error unquoted carriage

Question

I am trying to import a large csv file using COPY, but I keep getting this error code.

ERROR:  unquoted carriage return found in data
HINT:  Use quoted CSV field to represent carriage return.
CONTEXT:  COPY nyc_yellow_taxi_trips_2018_01, line 2
SQL state: 22P04

I know it is due to the blank row right under the header, but I tried manually deleting the space by opening through TextEdit. I also tried opening through excel, the file is too big to edit, but after deleting the space through TextEdit, there was no blank space.

I am still getting this error. Most likely an easy fix but I have been on this for awhile now.

Here is my code:

COPY nyc_yellow_taxi_trips_2018_01 
FROM '/Users/eddy/taxi/yellow_tripdata_2018-01.csv' 
WITH (FORMAT CSV, header, Delimiter ',' );

If there are no embedded commas, you could try to quote all the fields. This can be done using sed, but ignoring the embedded newlines could be tricky. [note: vi(m) allows editing of multi-GB files] — wildplasser
– wildplasser, Commented Apr 26, 2020 at 11:37

jjanes · Accepted Answer · 2020-04-26 00:16:18Z

1

It looks like you have inconsistent line endings. It has found a carriage return, but it expected (based on what ended the header line) either just a newline, or a CRNL.

You need to make the line endings consistent, which I don't know how to do using TextEdit.

answered Apr 26, 2020 at 0:16

jjanes

45k5 gold badges39 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Importing large CSV file into Postgres error unquoted carriage

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related