33226

RedShift - CSV load with line Break

Question:

Im trying to load a CSV file while loading I got the error

<blockquote>

Delimited value missing end quote

</blockquote>

<strong>CSV file Error line</strong>

<a href="https://i.stack.imgur.com/YCGBJ.png" rel="nofollow"><img alt="enter image description here" class="b-lazy" data-src="https://i.stack.imgur.com/YCGBJ.png" data-original="https://i.stack.imgur.com/YCGBJ.png" src="https://etrip.eimg.top/images/2019/05/07/timg.gif" /></a>

<strong>REDSHIFT ERROR:</strong>

line_number | 13 colname | Comment col_length | 250 raw_line |"123"|"123"|"xyz"|"2009-06-25 21:00:14.660000000"|"0"|""|""|""|""|""|"HI, raw_field_value | HI, err_code | 1214 err_reason | Delimited value missing end quote

Answer1:

You'll need to escape the newline characters in the source data and then specify the ESCAPE option in your COPY statement. Please have a look at <a href="http://docs.aws.amazon.com/redshift/latest/dg/r_COPY_command_examples.html#r_COPY_preparing_data" rel="nofollow">"Preparing Files for COPY with the ESCAPE Option"</a> for more details.

Answer2:

You could use CSV option in COPY command. Don't use REMOVEQUOTES or ESCAPE with it. CSV handles everything so in my opinion it's the best solution.

<a href="http://docs.aws.amazon.com/redshift/latest/dg/copy-parameters-data-format.html#copy-csv" rel="nofollow">http://docs.aws.amazon.com/redshift/latest/dg/copy-parameters-data-format.html#copy-csv</a>

Recommend