how to escape newline when importing csv file into greenplum external table?

Question

I'm trying to create external table from csv like this:

CREATE EXTERNAL TABLE hctest.ex_abs
(
a text,
b text,
c text,
d text,
e text,
f text,
g text
)
LOCATION ('gpfdist://192.168.56.111:10000/absdatasample.csv')
FORMAT 'CSV' (DELIMITER '|' HEADER);

The csv is delimited by pipe (|) and looks like this :

Employee ID|Time Type|Start Date|End Date|Number Of Days|Comment|Ration of Leave
90007507|Leave|11/27/2020|11/27/2020|1|dear mas Andria,

seek for approval for 1 day off. Thank you.|8
90007507|Leave|05/08/2020|05/08/2020|1|dear mas Andria, kindly approve 1 day leave at 8th May. Thank you.|5
90006391|Leave|04/27/2020|04/30/2020|4|Requesting leave days for new baby born|7
90006988|Leave|04/20/2020|04/21/2020|2|Dear Mas Tommy,
Herewith I would like to ask your approval for my leave which will be taken on 20 - 21 April 2020 (2 days of leave). I take this leave because of I need to attend the family wedding out of town along with visiting my extended family before Ramadhan in my hometown. 

Your approval will be highly appreciated.

Thank you,
Andrian Indrawan|2
90005573|Leave|04/09/2020|04/09/2020|1||4
90007088|Leave|04/08/2020|04/09/2020|2||9
90004055|Leave|04/08/2020|04/09/2020|2|Leave for family's reason|6

And i found the error:

ERROR:  missing data for column "g"  (seg0 slice1 192.168.56.111:6000 pid=4486)
DETAIL:  External table ex_absdata, line 2 of gpfdist://192.168.56.111:10000/absdatasample.csv: "90007507|Leave|11/27/2020|11/27/2020|1|dear mas Andria,"

How can i resolve this?

Answer 1

You have a couple of options.

1. You can use LOG ERRORS option on the External Table so that you can load the good data and reject the bad. But you have a lot of newline characters that are embedded in the file.
2. Fix the file.

I found this example.

I then took that example and your sample file.

awk -F\| '{ while (NF < 7 || $NF == "") { brokenline=$0; getline; $0 = brokenline $0}; print }' load.txt

Employee ID|Time Type|Start Date|End Date|Number Of Days|Comment|Ration of Leave
90007507|Leave|11/27/2020|11/27/2020|1|dear mas Andria,seek for approval for 1 day off. Thank you.|8
90007507|Leave|05/08/2020|05/08/2020|1|dear mas Andria, kindly approve 1 day leave at 8th May. Thank you.|5
90006391|Leave|04/27/2020|04/30/2020|4|Requesting leave days for new baby born|7
90006988|Leave|04/20/2020|04/21/2020|2|Dear Mas Tommy,Herewith I would like to ask your approval for my leave which will be taken on 20 - 21 April 2020 (2 days of leave). I take this leave because of I need to attend the family wedding out of town along with visiting my extended family before Ramadhan in my hometown. Your approval will be highly appreciated.Thank you,Andrian Indrawan|2
90005573|Leave|04/09/2020|04/09/2020|1||4
90007088|Leave|04/08/2020|04/09/2020|2||9
90004055|Leave|04/08/2020|04/09/2020|2|Leave for family's reason|6

Another possible fix is for you to create a perl, awk, sed, python, etc script (like above) to go through the file to make it a true CSV with double quotes. If you did that, you could retain the embedded newline characters. I think this would be overkill because whatever value you can get out of the unstructured data doesn't really need newline characters.