postgresql csv copy unquoted newline found in data

postgresql csv postgresql-copy
user1475191 picture user1475191 · Jun 19, 2015 · Viewed 12.3k times · Source

I have some csv data in excel, and I'm importing it into postgresql. I'm opening the excel csv file with a notepad editor (have tried notepad, wordpad and notepad++) and am then copying/pasting into a remote desktop connection to a linux machine. Then I'm using this import statement from within the database: COPY ltg_data FROM '/home/keene/ltg_db/ltg_outbreak_jun9_15.csv' (FORMAT CSV, HEADER);

I get this error: ERROR: unquoted newline found in data HINT: Use quoted CSV field to represent newline. CONTEXT: COPY ltg_data, line 175320

Here's the link to the csv file I'm using: http://greenandtheblue.com/ltg_outbreak_jun9_15.csv
I've researched the issue a lot and tried a lot of things and must be missing something fairly simple. Any help is very much appreciated.

Answer

thinkmassive picture thinkmassive · Jun 2, 2017

The file you linked doesn't appear to have CR-LF line endings, however I suspect that could potentially be an issue since you're coming from a Windows host. You might try removing carriage returns using sed:

sed 's/\r//' ltg_outbreak_jun9_15.csv ltg_outbreak_jun9_15-noCR.csv

Then COPY FROM the resulting ...-noCR.csv file.

Related questions

How to import CSV file data into a PostgreSQL table?

How can I write a stored procedure that imports data from a CSV file and populates the table?

Read More
Save PL/pgSQL output from PostgreSQL to a CSV file

What is the easiest way to save PL/pgSQL output from a PostgreSQL database to a CSV file? I'm using PostgreSQL 8.4 with pgAdmin III and PSQL plugin where I run queries from.

Read More
How to export table as CSV with headings on Postgresql?

I'm trying to export a PostgreSQL table with headings to a CSV file via command line, however I get it to export to CSV file, but without headings. My code looks as follows: COPY products_273 to '/tmp/products_199.csv' …

Read More