postgresql csv copy unquoted newline found in data

13,452

Solution 1

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.

Solution 2

You can remove the carriage returns using sed as thinkmassive said, but to save the results into a new file on Linux you need:

touch ltg_outbreak_jun9_15-noCR.csv

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

Share:
13,452

Related videos on Youtube

user1475191
Author by

user1475191

Updated on July 29, 2022

Comments

  • user1475191
    user1475191 over 1 year

    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.

    • Craig Ringer
      Craig Ringer almost 9 years
      Don't copy/paste into a "remote desktop? (ssh?) connection to the remote machine. Copy the CSV file instead. The problem is that your terminal emulator is probably introducing line breaks.
  • technogeek1995
    technogeek1995 over 5 years
    The issue was resolved by not trying to copy and paste via a terminal emulator (as seen above in the comments)
  • callmeGuy
    callmeGuy about 5 years
    or even easier sed -i 's/\r//g' ltg_outbreak_jun9_15.csv
  • mehmet
    mehmet almost 5 years
    use --quite option