The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
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??
Or you could also escape your newline character (\\n instead of \n).
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...
Suppose we have a next data (3 columns, new line enclosed): 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 2. Define a delimiter as '~' PS:
Source code of script:
Script must get NUMBER OF COLUMNS as argument (for record validation) + optional argument: a new delimiter.
Another version (not from STDOUT but similar concept):