0

I am loading data to Redshift using Copy. The text file has NUL.

I have looked at several options and tried using options such as:

null as '\0'  EMPTYASNULL  ACCEPTINVCHARS TRIMBLANKS TRUNCATECOLUMNS escape

However, it still errors out.

Below sample records and the error message.

NUL is after Main St|

2278|2047|5|1|1|1|18 N Main St| |Bowman|1|39|16443|15811|58623|Y|544|2018-05-21 17:29:12.000||||
2491|2047|6|1|1|1|18 N Main| |Bowman|1|39|16443|15811|58623-9613|Y|920|2018-11-26 18:28:26.000||||
2491|2047|7|1|1|1|18 N Main| |Bowman|1|39|16443|15811|58623-9613|Y|920|2018-11-26 18:28:26.000||||
2408|2154|7|1|1|1|101 Main St| |Lakota|1|39|16469|15956|58344|Y|447|2018-08-17 08:10:54.000||||


copy table1 from 's3://....txt' iam_role xx delimiter '|' null as '\0'  EMPTYASNULL  ACCEPTINVCHARS TRIMBLANKS TRUNCATECOLUMNS escape;

Missing newline: Unexpected character 0x7d found at location nn
John Rotenstein
  • 241,921
  • 22
  • 380
  • 470
dan1197
  • 13
  • 1
  • 1
  • 4
  • What makes you think it is a `NUL` in there? The error message is mentioning `0x7d`. Can you open the file in a text editor and look at the binary values? – John Rotenstein Jun 03 '19 at 21:18
  • Check `select * from stl_load_errors order by starttime desc limit 10` to more details about the error. There you should also see the `raw_line` that caused the error. – botchniaque Jun 03 '19 at 22:54

0 Answers0