How can I remove a new line character in a particular column in a CSV file?

You could use sed to merge the next line into the current one as long as the current line doesn't contain 4 | characters:

<file sed -e :1 -e 's/|/|/4;t' -e 'N;s/\n/ /;b1'

Some sed implementations have -i or -i '' to edit files in-place (-i.back to save the original with a .back extension), so with those, you could do:

sed -i -e :1 -e 's/|/|/4;t' -e 'N;s/\n/ /;b1' ./*.csv

To edit all the non-hidden csv files in the current directory.

The same with comments:

<file sed '
   :1
     s/|/|/4; # replace the 4th | with itself. Only useful when combined with
              # the next "t" command which branches off if the previous
              # substitution was successful
     t
     # we only reach this point if "t" above did not branch off, that is
     # if the pattern space does not contain 4 "|"s
     N; # append the next line to the pattern space
     s/\n/ /; # replace the newline with a space

   # and then loop again in case the pattern space still does not contain
   # 4 "|"s:
   b1'

Relying on 1st field's format (assuming each line should start with a number):

awk 'NR == 1{ printf $0; next }
     { printf "%s%s", (/^[0-9]+/? ORS : ""), $0 }
     END{ print "" }' file.csv

The output:

001|Baker St.London|3|4|7
002|Penny LaneLiverpool|88|5|7

Another GNU awk solution relying on 4 times | per record:

awk -v RS='([^|]+\\|){4}[^|]+\n' '{gsub(/\n/," ",RT); print RT}' file

RS is set such the that the record contains the 4 separators (even with a newline).

RT catches the record set by RS. gsub removes the newline on the record.

if the first row of your CSV is correct, the following code will work.

awk  'NR==1{printf "%s",$0; gsub(/[^|]/,""); nlast=n=length($0); next;} nlast==n{printf "\n";nlast=0} {printf "%s",$0; gsub(/[^|]/,""); nlast+=length($0)} END{print ""}'  file_201805072030.csv > temp.csv && mv -f temp.csv file_201805072030.csv

if none of the rows are correct, and if you want to rearrange with 5 columns

awk  'NR==1{printf "%s",$0; gsub(/[^|]/,""); nlast=n=4; next;} nlast==n{printf "\n";nlast=0} {printf "%s",$0; gsub(/[^|]/,""); nlast+=length($0)} END{print ""}' file_201805072030.csv > temp.csv && mv -f temp.csv file_201805072030.csv

If we can assume that any line with only 2 fields should have its trailing newline removed, you can do the following in Perl:

$ perl -F"\|" -lane '$#F==1 ? printf : print' file.csv 
001|Baker St.London|3|4|7
002|Penny LaneLiverpool|88|5|7

Important Disclaimer: as pointed out in the comments by Stéphane Chazelas, this assumes that your input doesn't contain any % characters since, if it does, those will be taken as the format specifier for printf. This could have unintended consequences ranging from simply printing wrong output to eating loads of RAM, if your input has something silly like %02147483600f%02147483600f%02147483600f%02147483600f.

Explanation

• -a : makes perl act like awk, splitting each input line on the character given by -F (so, a | here; which needs to be escaped as \| since | means OR in perl regular expressions) and saving the result as the array @F.
• -l : this removes trailing newlines from each input line and adds a 'n t each print call.
• -ne : read the input file line by line and apply the script given by -e to each line.
• $#F==1 ? printf : print' : The $#F variable is the number of elements in the array @F, so the number of fields. This, therefore, means if the number of fields is 1, then printf (which will print the current line without a newline character since the existing one was removed by -l and printf doesn't add one). If the number of fields is not exactly 1, print the line (which, because of the -l will add a newline).

The same thing can be expanded to:

$ perl -e 'while($line=<STDIN>){
            chomp $line; 
            @fields=split(/\|/,$line); 
            if(scalar(@fields) == 2){
                print "$line";
            } 
            else{
                print "$line\n"
            }
           }' < file.csv 
001|Baker St.London|3|4|7
002|Penny LaneLiverpool|88|5|7

And an even shorter version suggested by @Sundeep in the comments:

perl -F'\|' -ape 'chomp if $#F==1'

With the sed tool, you can do like shown:

sed  -i  -e '
             /^\(\([^|]*|\)\{2\}\)*[^|]*$/b
             N;s/\n/ /
             s/^/\n/;D
   '     . /*.csv

Explanation:

1. Branch to end of sed code processing for the current pattern space data when an even number of pipes are found.

2. Or, go fetch the next line and append it to the pattern space.

3. Now reapply the sed code all over again on the pattern space.

    perl -i.BAK    -lpe '
        $\ = ( $k += tr/|/|/ ) =~ /[24680]$/ ? "\n" : " ";
     '     . /*.csv 
   

4. $k is a running counter of number if pipes seen at any time.

5. For even we print with a newline, otherwise we join the lines with a space.

awk '/Baker/{printf "%s ",$0; getline; printf "%s\n", $0} \
/Penny/{printf $0; getline; printf "%s\n", $0}' file

001|Baker St. London|3|4|7
002|Penny LaneLiverpool|88|5|7