4

I have a CSV file with 150+ columns, with the new line character as a record separator. The problem lies in one of the columns getting new line characters. For this, I want to remove those.

Input:

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

Output:

001|Baker St. London|3|4|7
002|Penny Lane Liverpool|88|5|7
  • 2
    Note that your sample suggests that you want to replace those newlines with a space character as opposed to just delete them. – Stéphane Chazelas May 7 '18 at 14:46
  • @StéphaneChazelas AFAIK trying to select the text there is a final whitespace before the newline, so it actually looks just removing the newline. – Bakuriu May 7 '18 at 20:08
7

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'
3

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
  • Thanks for your reply, your command works fine. But using this command we need to write the output to another file, but my case i don't know file name my file is like file_*.csv EG: file_201805072030.csv and i want to do above action in the same file. – Vicky May 7 '18 at 11:35
  • @Vicky, is there only one file in format file_*.csv? What should be the name of another file? – RomanPerekhrest May 7 '18 at 12:15
2

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.

1

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
  • Thanks Siva, but my input file name is dynamic and i want to do this action on the same file. – Vicky May 7 '18 at 11:56
  • 2
    try my edited answer. – msp9011 May 7 '18 at 12:23
  • In my case i don't know the file name, my file name will be like file_*.csv, so if i use this comment its not working – Vicky May 7 '18 at 13:13
  • do u want to do it for all CSV files in the directory? – msp9011 May 7 '18 at 13:14
1

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'
  • you could also use perl -F'\|' -ape 'chomp if $#F==1' – Sundeep May 7 '18 at 14:17
  • 1
    @Sundeep ooh, nice one! I should have thought of that, dammit! Thanks :) – terdon May 7 '18 at 14:42
  • The first argument of printf is the format, it should not be arbitrary data. – Stéphane Chazelas May 7 '18 at 14:50
  • Note that it wouldn't work if the address was on 3 or more lines – Stéphane Chazelas May 7 '18 at 14:50
  • @StéphaneChazelas no, it assumes the data are as shown by the OP. As for the argument being the format, that's true, of course, but is there anything wrong with doing it this way? Defaulting to $_ is standard practice in Perl, what corner case are you thinking of where this would be a problem? – terdon May 7 '18 at 14:52
0

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.

-1
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
  • Why not just say printf "001|Baker St. London|3|4|7\n002|Penny Lane Liverpool|88|5|7\n"? The objective is not just to produce the sample output; the objective is to solve the described problem, where the given files are just an example. – Scott May 11 '18 at 1:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.