sed replace new line character inside each line

user_4945102 0
Viewed 80

I have a huge csv file from an export delimited by pipe

8CDAC2EF-9261-4B9F-9D41-87B460C467B0|SAMPLE1|SAMPLE 2|SAMPLE 3|2020-08-03 00:00:00
8CDAC2EF-9261-4B9F-9D41-87B460C467B0|SAMPLE1|SAMPLE 

2|SAMPLE 3|2020-08-03 00:00:00
8CDAC2EF-9261-4B9F-9D41-87B460C467B0|SAMPLE1|SAMPLE 2|

|2020-08-03 00:00:00
8CDAC2EF-9261-4B9F-9D41-87B460C467B0|SAMPLE1|SAMPLE 2||2020-08-03 00:00:00
8CDAC2EF-9261-4B9F-9D41-87B460C467B0|SAMPLE1||SAMPLE 3|2020-08-03 00:00:00

Ideally, the first line is the expected format, however, since its raw data, there seems some new line character in the fields SAMPLE 2, SAMPLE 3. I would like to replace the new line or breaking characters in these fields with empty character so it becomes the format of the first line.

PS: The SAMPLE 2 and SAMPLE 3 could be null as well.

I wanted to make this to the below format, ie, double quote enclosed as below.

"8CDAC2EF-9261-4B9F-9D41-87B460C467B0"|"SAMPLE1"|"SAMPLE 2"|"SAMPLE 3"|"2020-08-03 00:00:00"

I tried the below code, it works fine, but breaks if there are any new line characters each line.

sed  -e 's/^\|$/"/g' -e 's/|/"|"/g'  input.csv

Edit: The accepted solution works fine, but since I have some kind of raw data, I had do follow some additional steps since some of the values contain | and other special strings which shouldn't be there.

What I did was following.

  1. I was able to control the delimiter from the export, I used € since we find that it's not used in the data in any of our samples.
  2. Then I replaced ", ', |, with a space character. finally, the delimiter of € was replaced with |.
  3. Then used the accepted answer to filter out line breaks
  4. Finally piped them to make those are enclosed in quotes.
sed ubuntu shell command-line bash
2 Answers

Perl to the rescue!

perl -pe '$c += tr/|//; if ($c == 4) { $c = 0 } else { chomp }' -- file.csv
  • -p reads the input line by line, prints each line after running the code
  • tr/// is normally used for transliteration, but here, it's just the fast method to count how many vertical bars are there on the current line. We add the count to the variable $c.
  • If $c is four, the line is complete, so we clear the variable.
  • if $c isn't four, the line is not yet complete (it can also be too long if the data are wrong, but let's ignore this now). So, we run chomp to remove the newline.
user_10306750

With sed:

sed '
    :a
    /\([^|]*|\)\{4\}/!{
      N
      s/\n//
      ba
   }' input.csv
user_138090010
Related