Copy Command rejects columns with newline character
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??
The exception I get is shown below. The next row has integer data as first column, so the data gets rejected. 
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).
Or you could also escape your newline character (\\n instead of \n).
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!!
Thank you!!
+1Hi!
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):
1. PIPE as delimiter
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:
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,eggNow 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|egg2. Define a delimiter as '~'
$ cat data.csv | python ./remove_newline.py 3 "~"
one~two~three four
foo~bar~eggPS:
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:
- new line MUST be enclosed
- doesn't support for TRAILING NULLCOLS
Sign up
Already have an account? Login
Welcome to the Rocket Forum!
Please log in or register:
Employee Login | Registration Member Login | RegistrationEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.
77 4th Avenue
Waltham, MA 02451 USA
