I am in troubles with a regexp to remove some \n

Im trying to define a regexp to remove some carriage return in a file to be loaded into a DB.

Here is the fragment

200;GBP;;"";"";"";"";;;;"";"";"";"";;;"";1122;"BP JET WASH IP2 9RP
";"";Hamilton;"";;0;0;0;1;1;"";

This is the regexp I used in https://regex101.com/

(;"[[:alnum:] ]+)[\n]+([[:alnum:] ]*)"

Which should get two groups, one before and one after some newline.

Looking at regexp101, it informs that the groups are correctly captured

enter image description here

But the result is wrong, because it still introduce an invisible new line as follow

enter image description here

I also try to use sed but the result is exactly the same.

So, the question is: Where am I wrong?

4 answers

  • answered 2022-05-04 10:50 choroba

    sed is line based. It's possible to achieve what you want, but I'd rather use a more suitable tool. For example, Perl:

    perl -pe 's/\n/<>/e if tr/"// % 2 == 1' file.csv
    
    • -p reads the input line by line, running the code for each line before outputting it;
    • The /e option interprets the replacement in a substitution as code, in this case replacing the final newline with the following line (<> reads the input)
    • tr/"// in numeric context returns the number of matches, i.e. the number of double quotes;
    • If the number is odd, we remove the newline (% is the modulo operator).

    The corresponding sed invocation would be

    sed '/^\([^"]*"[^"]*"\)*[^"]*"[^"]*$/{N;s/\n//}' file.csv 
    
    • on lines containing a non-paired double quote, read the next line to the pattern space (N) and remove the newline.

    Update:

    perl -ne 'chomp $p if ! /^[0-9]+;/; print $p; $p = $_; END { print $p }' file.csv
    

    This should remove the newlines if they're not followed by a number and a semicolon. It keeps the previous line in the variable $p, if the current line doesn't start with a number followed by a semicolon, newline is chomped from the previous line. The, the previous line is printed and the current line is remembered. The last line needs to be printed separately as there's no following line for it to make it printed.

  • answered 2022-05-04 13:20 Tux

    perl -MText::CSV_XS=csv -wE'csv(in=>csv(in=>shift,sep=>";",on_in=>sub{s/\n+$// for@{$_[1]}}))' file.csv
    

    will remove trailing newlines from every field in the CSV (with sep ;) and spit out correct CSV (with sep ,). If you want ; in to output too, use

    perl -MText::CSV_XS=csv -wE'csv(in=>csv(in=>shift,sep=>";",on_in=>sub{s/\n+$// for@{$_[1]}}),sep=>";")' file.csv
    

  • answered 2022-05-04 13:39 ikegami

    It's usually best to use an existing parser rather than writing your own.

    I'd use the following Perl program:

    perl -MText::CSV_XS=csv -e'
       csv
          in             => *ARGV,
          sep            => ";",
          blank_is_undef => 1,
          quote_empty    => 1,
          on_in          => sub { s/\n//g for @{ $_[1] }; };
    ' old.csv >new.csv
    

    Output:

    200;GBP;;"";"";"";"";;;;"";"";"";"";;;"";1122;"BP JET WASH IP2 9RP";"";Hamilton;"";;0;0;0;1;1;"";
    

    If for some reason you want to avoid XS, the slower Text::CSV is a drop-in replacement.

  • answered 2022-05-04 17:13 HatLess

    Using sed

    $ sed '/^[0-9]/{h;d};x;G;s/\n//' input_file
    200;GBP;;"";"";"";"";;;;"";"";"";"";;;"";1122;"BP JET WASH IP2 9RP";"";Hamilton;"";;0;0;0;1;1;"";
    

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum