Copy Command rejects columns with newline character

Merlin_Arockias

I am using COPY command with data from STDIN and i have a table with newline character.The command takes the newline as the end of record and inserts the part of data from that column in another row which leads to error due to data type constraint violation. Though some suggest to replace newline character by space before running COPY command, I do not see it an optimal solution. Is there any other possible ways to overcome this issue??

Merlin_Arockias

The exception I get is shown below. The next row has integer data as first column, so the data gets rejected.

Flavien_Bessede

This can be easily fixed by enclosing your data between a specific character (usually double-quote) and then load using COPY ... ENCLOSED BY ...
Or you could also escape your newline character (\\n instead of \n).

Merlin_Arockias

I have tried using NO ESCAPE to escape all the characters but the newline is not escaped. I will try using ENCLOSED BY.
Thank you!!

Daniel_Leybovic

Hi!

You can't parse it with Vertica's COPY statement.
Read about it in this thread https://community.vertica.com/vertica/topics/failed_to_load_csv_file_which_as_line_break_in_quoted_c...

Work around:

Suppose we have a next data (3 columns, new line enclosed):

$ cat data.csv 

one,two,"three

four"

foo,bar,egg

Now will output file to STDOUT in Vertica's acceptable format (just redirect an output to Vertica COPY FROM STDIN statement):

1. PIPE as delimiter

$ cat data.csv | python ./remove_newline.py 3

one|two|three four

foo|bar|egg

2. Define a delimiter as '~'

$ cat data.csv | python ./remove_newline.py 3 "~"

one~two~three four

foo~bar~egg

PS:
Source code of script: 
http://vertica-forums.com/viewtopic.php?f=82&t=1416
Script must get NUMBER OF COLUMNS as argument (for record validation) + optional argument: a new delimiter.

Another version (not from STDOUT but similar concept):
http://vertica-forums.com/viewtopic.php?f=49&t=1273&p=4245#p4245

LIMITATIONS:

1. new line MUST be enclosed
2. doesn't support for TRAILING NULLCOLS