Removing new line character from a column in a CSV file

Question

We are getting new line character in one the column of a CSV file. The data for the column is coming in consecutive rows. Eg:

ID,CODE,MESSAGE,DATE,TYPE,OPER,CO_ID
12202,INT_SYS_OCS_EX_INT-0000,"""OCSSystemException: HTTP transport error: java.net.ConnectException: Tried all: '1' addresses, but could not connect over HTTP to server: '10.244.166.9', port: '8080'
 failed reasons:
  [0] address:'/10.244.166.9',port:'8080' : java.net.ConctException: Connection refused
""",06-09-2021 05:52:32,error,BillCycle,6eb8642aa4b
20840,,,06-09-2021 16:17:18,response,changeLimit,1010f9ea05ff

The issue is for column Messageand id 12202 , in which data is coming in triple quotes and in consecutive rows.

My requirement is that for the column Message, the data should come in a single row rather than multiple rows, because my etl loader fails to import an embedded newline.

Answer 1

If your CSV was generated by MS tools such as Excel then the "newline" in the middle of the field is a LF alone while the "newline" at the end of each record is a CRLF like this (note the LF $ mid-field vs the CRLF ^M$ at the end of the records):

$ cat -Ev file
ID,Code,Message,date^M$
1244,,"""Exception error : java connection error$
:8080 Connection refused""",01-09-2021^M$

and, if so, you can just tell GNU awk (for multi-char RS) that records end in CRLF and to replace mid-record LFs with blanks:

$ awk -v RS='\r\n' '{gsub(/\n/," ")} 1' file
ID,Code,Message,date
1244,,"""Exception error : java connection error :8080 Connection refused""",01-09-2021

If you only have a POSIX awk and no tools that work on CSVs, then see whats-the-most-robust-way-to-efficiently-parse-csv-using-awk for how to handle them or if CR doesn't appear anywhere else in your file you can do this with any awk:

$ awk -v RS='\r' 'NR>1{print prev} {sub(/^\n/,""); gsub(/\n/," "); prev=$0}' file
ID,Code,Message,date
1244,,"""Exception error : java connection error :8080 Connection refused""",01-09-2021

Answer 2

One simple way is to just remove newline characters on lines with only 3 fields:

$ perl -F','  -pane 's/\n// if $#F==2' file 
ID,Code,Message,date
1244,,"""Exception error : java connection error:8080 Connection refused""",01-09-2021
1245,,"""Exception error :""",01-09-2021
1246,,"ffadsdasd",01-09-2021

Of course, this assumes you can never have a , inside a field, something that is allowed in CSV files. So if your Message field contains something like """foo,bar""", it can fail. This is why it is always better to use a dedicated parser.

This approach should work for any valid CSV file:

$ perl -MText::CSV -le '$csv = Text::CSV->new({binary=>1}); while ($row = $csv->getline(STDIN)){ $row->[2]=~s/\n//; $csv->print(STDOUT,$row)}' < file
ID,Code,Message,date
1244,,"""Exception error : java connection error:8080 Connection refused""",01-09-2021
1245,,"""Exception error :""",01-09-2021
1246,,ffadsdasd,01-09-2021

Answer 3

If you have csvkit utilities you can fix up lines with embedded newlines, for example by transforming the newline into the literal two characters \n:

csvformat -M $'\r' datafile |                    # temporarily end lines with $'\r'
    sed -e ':a' -e 'N;$!ba' -e 's/\n/\\n/g' |    # transform $'\n' into '\n'
    tr '\r' '\n'                                 # convert the line endings back to $'\n'

---

_{Thanks to a poster on the csvkit github for their solution, which in turn links back to an answer on StackOverflow}

Answer 4

Using Raku (formerly known as Perl_6)

raku -MText::CSV -e 'my $csv=Text::CSV.new;  .perl.put for $csv.getline_all(open($*ARGFILES, :r, :!chomp));'

Sample Input:

ID,Code,Message,date
1244,,"""Exception error : java connection error
:8080 Connection refused""",01-09-2021

Sample Output:

$["ID", "Code", "Message", "date"]
$["1244", "", "\"Exception error : java connection error\n:8080 Connection refused\"", "01-09-2021"]

You can accomplish what you hope by using a dedicated module (e.g. Text::CSV) and the Raku programming language. I've added a call to .perl so you can visualize the \n character (FYI, .raku also works). Once you get the data structure above it's simple enough to alter the code, mapping into the fields to eliminate embedded newlines:

raku -MText::CSV -e 'my $csv=Text::CSV.new;  .put for $csv.getline_all(open($*ARGFILES, :r, :!chomp)).map(*.subst("\n"," ", :g));'

Updated Output:

ID Code Message date
1244  "Exception error : java connection error :8080 Connection refused" 01-09-2021

https://modules.raku.org/dist/Text::CSV:cpan:HMBRAND
https://github.com/Tux/CSV
https://raku.org

Answer 5

As first step run through your CSV file on and convert to Unix line endings:

dos2unix your_csvfile

This changes \r\n to \n Then in the next step :

Using GNU sed we keep track of even/odd numbers of double quotes as shown here:

sed -Ee '
  h;s/[^"]*//g
  /^(..)*$/!{
    z;G;N;D
  }
  g;s/\n//g
' your_csvfile

---

perl -pe 's/\n/<>/e while y/"// % 2' your_csvfile

---

We can use the awk utility to count the number of double quotes and keep accumulating lines until they become even.

awk '{
  t = $0
  while ((gsub(/"/,"&",t) ~ /[13579]$/) && (getline nxt > 0)) 
    t = t nxt
  print t
}' your_csvfile

---

Output:

     1  ID,Code,Message,date$
     2  1244,,"""Exception error : java connection error:8080 Connection refused""",01-09-2021$

Answer 6

Assuming the input is a Unix text file and that you want to replace literal newlines in the MESSAGE field with the string \n:

mlr --csv put '$MESSAGE = gsub($MESSAGE,"\n","\\n")' file

The output, given the CSV in the question:

ID,CODE,MESSAGE,DATE,TYPE,OPER,CO_ID
12202,INT_SYS_OCS_EX_INT-0000,"""OCSSystemException: HTTP transport error: java.net.ConnectException: Tried all: '1' addresses, but could not connect over HTTP to server: '10.244.166.9', port: '8080'\n failed reasons:\n  [0] address:'/10.244.166.9',port:'8080' : java.net.ConctException: Connection refused\n""",06-09-2021 05:52:32,error,BillCycle,6eb8642aa4b
20840,,,06-09-2021 16:17:18,response,changeLimit,1010f9ea05ff

The same thing using csvformat from csvkit to replace all record-ending newlines with the "RS" character (octal ASCII code 036, a character that does not exist in the document), then swap the remaining literal newlines with this "RS" character, and then finally replace all "RS" characters with the string \n:

csvformat -M $'\036' file | tr '\n\036' '\036\n' | sed $'s/\036/\\\\n/g'

The result is the same as shown above.