2

I am struggling with processing a file. Normally I should have a line for each host definition. But sometimes, someone split some fields on different lines. Here is an example:

"host1","host1","linux
server",""
"host2","host2","linux server",""

Now I would like to find a way (better in bash) to fix this in to:

"host1","host1","linux server",""
"host2","host2","linux server",""

Each field should be surrounded by a double quote; if this is not the case it means that a \n was inserted, which I then want to remove in order to have always 4 fields on each line.

Note that I may have a description split into several lines like:

"host1","host1","linux
server
centos",""
"host2","host2","linux server",""

I tried several awk approaches, e.g.

awk 'BEGIN {ORS=""; RS="\"\n\""; FS="\",\""; OFS="\",\""} {if (NF == 3) print "\"" $1 "\"," $2 "\"," $3 "\"\n"; else printf "%s", $0} END {print ""}' /tmp/ngr4

but I did not succeed and I begin to reach my limit with this powerful tool.

4
  • 1
    are all fields guaranteed to be wrapped in double quotes? can double quotes show up as part of the data? will all lines have the same number of comma-delimited fields (4 in this case)?
    – markp-fuso
    Commented Apr 4 at 13:51
  • Oh I tried several things, helped with chat GPT for somes, but nothing provide the expected result, generally it didn't change anything... awk 'BEGIN {ORS=""; RS="\"\n\""; FS="\",\""; OFS="\",\""} {if (NF == 3) print "\"" $1 "\"," $2 "\"," $3 "\"\n"; else printf "%s", $0} END {print ""}' /tmp/ngr4 Commented Apr 4 at 14:10
  • Yes all fields must be surrounded by double quotes, and all host definition contains the same number of fields, but care that some fiels can contain comma themselves, but data within fields cannot contains double quote. Commented Apr 4 at 14:11
  • 1
    The simple rule is "if the current line so far has an odd number of quotes, getline and append until the number of quotes is even". That works because every complete field has an even number of quotes: zero, or two if it is quoted, plus two for every (doubled) quote inside it. You might want to single up the repeated quotes, and place a space where the newline would have been, too. Commented Apr 4 at 16:43

7 Answers 7

7

Using Miller (mlr), a CSV-aware multi-purpose processing utility for various structured document formats, to clean up the whitespace of all fields:

$ cat file
"host1","host1","linux
server",""
"host2","host2","linux server",""
$ mlr --csv -N clean-whitespace file
host1,host1,linux server,
host2,host2,linux server,

This reads the data in file as header-less CSV records and applies the clean-whitespace operation to each. The clean-whitespace operation trims flanking whitespace from each field's value and combines consecutive whitespace characters into single spaces.

To instead only replace newlines with spaces, you may iterate over the fields with a short put expression:

$ mlr --csv -N put 'for (k,v in $*) { $[k] = gssub(v, "\n", " ") }' file
host1,host1,linux server,
host2,host2,linux server,

The gssub() function acts like gsub() in Awk, but does not treat its query argument like a regular expression (Miller also has gsub()).

If you feel you need to have the fields quoted even though it's not strictly needed (Miller adds quotes automatically if a field's value requires it), then use mlr with its --quote-all option:

$ mlr --csv -N --quote-all clean-whitespace file
"host1","host1","linux server",""
"host2","host2","linux server",""
$ mlr --csv -N --quote-all put 'for (k,v in $*) { $[k] = gssub(v, "\n", " ") }' file
"host1","host1","linux server",""
"host2","host2","linux server",""
0
6

The last thing you want to do is try to do this in bash. See Why is using a shell loop to process text considered bad practice?.

Now, if what you want can be expressed as "remove any newline characters unless they come right after a " character", you could do something like this:

perl -pe 's/(?<!")\n/ /g' file

The (?<!")\n matches any newline character that is NOT preceded by a ". So given an example input like this:

$ cat file
"host0","host0","linux
server",""
"host1","host1","linux
server
centos",""
"host2","host2","linux server",""

The command above gives:

$ perl -pe 's/(?<!")\n/ /g' file
"host0","host0","linux server",""
"host1","host1","linux server centos",""
"host2","host2","linux server",""

But, really, mlr is the best approach.

5
  • 3
    If the newline occurs in an embedded quote ("text ""\n"" text"), there would be issues. But nobody ever has data like that, do they? ;-)
    – Kusalananda
    Commented Apr 4 at 15:26
  • 1
    @Kusalananda note how I first reframed the question and then answered. If the OP has data as you show, then their request cannot be expressed as "remove any newline characters unless they come right after a " character".
    – terdon
    Commented Apr 4 at 15:30
  • YESSSSS it works like a charm your perl command !! :) Many thx as it works without any special command and very simple furthermore to understand. Commented Apr 4 at 15:41
  • If the input file can be as mangled up as the OP suggests then this answer could fail. For example when a line starts with a (field-separation) comma.
    – dhm
    Commented Apr 5 at 10:08
  • 1
    @user9101329 yes, again, please note how I am reframing the question and answering only how can I "remove any newline characters unless they come right after a " character". That said, why would the comma be relevant? I am not using the comma in any way, so stray commas won't affect this. I am not treating this as field separated data so the field separator's presence or absence is irrelevant. I simply replace all \n that are not immediately after a " with a space.
    – terdon
    Commented Apr 5 at 10:16
4

Using Raku (formerly known as Perl_6)

Inspired by @terdon's excellent Perl answer:

~$ raku -ne '/ <!after \" > $/ ?? print "$_ " !! put $_;'  file

Here are answers written in Raku (a.k.a. Perl6). Raku has a new Unicode-aware regex engine, which attempts to clean up some well-known idioms. So (for example), the "Y not-after X" negative-lookbehind idiom becomes <!after X > Y in Raku, wherein <?after … > denotes a positive lookbehind and <!after … > denotes a negative lookbehind.

Because Raku regularizes line-terminator handling (auto-chomping the \n newline is now the default), we simply detect the pattern using Raku's Ternary operator Test ?? True !! False , and then output using print (does not add \n newline terminator) or put (adds \n newline terminator at end of text).


Sample Input:

"host0","host0","linux
server",""
"host1","host1","linux
server
centos",""
"host2","host2","linux server",""

Sample Output:

"host0","host0","linux server",""
"host1","host1","linux server centos",""
"host2","host2","linux server",""


Other Raku solutions

Using Raku's Text::CSV module:

Using an appropriate CSV-parser (module) from the Raku ecosystem is fairly easy (see: https://raku.land/?q=CSV ). These check for RFC 4180 compliance and can give you standardized CSV output, as well as a host of customizations.

Below, Raku's Text::CSV module parses the OP's input just fine, and after removing \n newlines, will by default output only those columns with internal-whitespace as double-quoted columns (first answer). The second answer reads linewise and produces the same answer as the first:

Reading entire file into memory, default csv() output:

~$ raku -MText::CSV -e 'my @a = csv(in => "/path/to/file", sep => ",");
                        @a = @a>>.map( *.trans: "\n" => " ");
                        csv(in => @a, out => $*OUT, sep => ",");'
host0,host0,"linux server",
host1,host1,"linux server centos",
host2,host2,"linux server",

Reading linewise, then "manually"-quote output:

~$ raku -MText::CSV -e 'my $fh = "/path/to/file";  my $io = open $fh, :r, :!chomp; 
                        my $csv = Text::CSV.new;  my @data;
                        while $csv.getline($io) -> $row {
                            @data.push: $row.map: *.trans: "\n" => " "; };
                        put $_.join(",") for @data>>.map({ / \s / ?? (q["] ~ $_ ~ q["]) !! $_ });'
host0,host0,"linux server",
host1,host1,"linux server centos",
host2,host2,"linux server",

https://docs.raku.org/language/operators#infix_??_!!
https://github.com/Tux/CSV/blob/master/doc/Text-CSV.md#embedded-newlines
https://raku.org

3

Using any awk:

$ awk 'BEGIN{RS=ORS="\""} !(NR%2){gsub(/\n/," ")} 1' file.csv
"host1","host1","linux server",""
"host2","host2","linux server",""

If your CSV is actually more complex than you showed in some way (but I can't think of any way it could be right now) that makes the above not work for you then see whats-the-most-robust-way-to-efficiently-parse-csv-using-awk if you want to use awk for this.

2
  • Fails if a rogue line break lands just before a field separator.
    – dhm
    Commented Apr 7 at 7:13
  • 1
    @dhm I assume you mean mid-record as a line break wouldn't be a problem at the end of a record. There cannot be a line break before a field separator mid-record in a valid CSV. A line break before a field separator can only exist in a valid CSV if there is a null field at the start of a record because per RFC 4180 a field is only allowed to contain line breaks if they're inside quotes and so if the line break was inside quotes then it wouldn't be just before a field separator and if it's outside of double quotes then it's the end of a record. not part of a field.
    – Ed Morton
    Commented Apr 7 at 10:31
1

We assume that despite many unwanted line breaks all double quotes match and all field separators are present. In that case you could use this command:

$ sed '/^"/! s/^/ /'  infile | tr -d '\n' | sed '-e s/"/"\n/'{8..1000..8}
"host1","host1","linux server centos",""
"host2","host2","linux server",""

where:

$ cat infile
"host1","host1","linux
server
centos",""
"host2","host2","linux server",""

This should work regardless of where unwanted line breaks may occur.

The number 1000 inside the braces is an arbitrary large number and must be bigger than the total numbers of characters in the input file.

If you suspect your input lines may contain leading/trailing spaces then remove them first. For example use this command awk 'NF{$1=$1}1' infile

1

Using TXR:

$ txr fix.txr data
"host1","host1","linuxserver",""
"host2","host2","linux server",""
"host1","host1","linuxservercentos",""
"host2","host2","linux server",""

Code in fix.txr:

@(repeat)
@  (freeform "")
"@a","@b","@c","@d"
@  (do (put-line `"@a","@b","@c","@d"`))
@(end)

The freeform directive causes the following horizontal pattern to be interpreted against a fictitious version of the input in which lines are combined into one logical line. The string argument "" overrides the default separator which is "\n" (newline).

With Vim syntax highlihting:

enter image description here

1
  • Very interesting! First time I've heard of TXR! Commented Apr 6 at 10:41
0

Some modern implementations of awk actually bring a fully-fledged csv parser (I think it's GoAWK?), yours probably doesn't. GNU awk starting from 5.3 have the --csv flag. You would need to have a very recent Linux distro to have access to GNU awk 5.3 (for example, Ubuntu 24.04 doesn't ship that, and Fedora 40 will start shipping it – once it's released, Fedora 40 isn't even a beta release yet).

So, the easiest solution would probably be not to use awk to parse your CSV input. Usually, I'd recommend mlr for such tasks, but if I remember correctly, it doesn't come with support for multiline values.

So, Python would be a natural choice.

#!/usr/bin/env python3
import csv
from sys import argv

with (
    open(argv[1], "r", encoding="utf-8") as infile,
    open(argv[2], "w", encoding="utf-8") as outfile,
):
    reader = csv.reader(infile)
    writer = csv.writer(outfile, quoting=csv.QUOTE_ALL)
    for row in reader:
        writer.writerow([value.replace("\n", " ") for value in row])

You can make this executable (chmod a+x thisscript.py) and run it as /path/to/thisscript.py input.csv output.csv.

12
  • Miller supports quoted CSV fields containing embedded newlines.
    – Kusalananda
    Commented Apr 4 at 14:12
  • @Kusalananda I couldn't make mlr --csv --from test.csv cat work with the data offered Commented Apr 4 at 14:30
  • 2
    ah, I know what happened. I was using the first line as header, and it doesn't seem to work when there's newlines in the header. Commented Apr 4 at 14:31
  • 1
    Yeah, the whole tool was rewritten from scratch in Go at version 6. It was quite a big shift and I'm not at all surprised that many things are different.
    – Kusalananda
    Commented Apr 4 at 15:03
  • 1
    FYI recent releases of Kernighan's awk (aka "one true awk") and GNU awk both have --csv options to support CSV parsing. GNU awk also has other options like FPAT which may it easy to parse files that are CSV-like but aren't actually CSVs, see whats-the-most-robust-way-to-efficiently-parse-csv-using-awk.
    – Ed Morton
    Commented Apr 4 at 22:10

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .