4

Using sed or awk, is it possible to visually align columns in a CSV file?

For example:

e.g. from:

a,b,c,some stuff,"some, other, stuff",d,2023-03-10 18:37:00
y,x,z,t,cool,thing,2022-04-12 21:44:00

to:

a, b, c, some stuff,"some, other, stuff",     d, 2023-03-10 18:37:00<EOL>
x, y, z,          t,                cool, thing, 2022-04-12 21:44:00<EOL>

There are some double quoted fields containing text and having commas.

I gave column from bsdmainutils a try but it's apparently not able to deal with such data.

7
  • Do you still need the commas to be present?
    – Kusalananda
    Mar 10, 2023 at 17:55
  • @Kusalananda you're thinking of tabulation? Mar 10, 2023 at 17:57
  • 1
    @MarcusMüller I'm thinking Miller's "pretty-print" output format would work nicely, but it does not output the original delimiters. It also supports drawing "cells" around each field with --barred. E.g. mlr --c2p --barred cat file.
    – Kusalananda
    Mar 10, 2023 at 17:58
  • 1
    In that case, I just beat you to it :D Sorry! Mar 10, 2023 at 17:59
  • 1
    You realize that the result will no longer be a proper CSV file? Most csv parser will choke on a CSV with ,[some variable number of spaces],[etc].
    – drewk
    Mar 10, 2023 at 18:49

3 Answers 3

7

A CSV file of this type:

a, b, c, some stuff,"some, other, stuff",     d, 2023-03-10 18:37:00<EOL>
x, y, z,          t,                cool, thing, 2022-04-12 21:44:00<EOL>

is not really the same data file anymore since you are modifying the fields. When parsed, what was originally "t" will now parse out as " t" because of the width of "some stuff" above (unless you use a regex to parse the non-standard ,[variable space] delimiters.)

You can force quotes on all fields to get a more csv file that shows these new fields clearly. Here is a Ruby to do that:

ruby -r csv -e '
cols={}
data=CSV.parse($<.read)
data.transpose.each_with_index{|sa,i| 
    cols[i]=sa.max_by{|e| e.length}; cols[i]=cols[i].length 
}
puts CSV.generate(force_quotes:true){|csv|
    data.each{|row|
        csv<<row.map.with_index{|e, i| e.rjust(cols[i] ) }
    }
}
' file

Prints:

"a","b","c","some stuff","some, other, stuff","    d","2023-03-10 18:37:00"
"y","x","z","         t","              cool","thing","2022-04-12 21:44:00"

Or, if you really want quoted and unquoted fields, you can do:

ruby -r csv -e '
lcl_csv_opt={:row_sep=>nil}
data=CSV.parse($<.read)
cols=data.transpose.map.with_index{|sa,i| 
    x=sa.max_by{|e| [e].to_csv(**lcl_csv_opt).length}
    [i,"#{[x].to_csv(**lcl_csv_opt)}"]
}.to_h
puts CSV.generate(){|csv|
    data.each{|row|
        csv<<row.map.with_index{|e, i| 
            [e].to_csv(**lcl_csv_opt)==cols[i] ? e : e.rjust(cols[i].length ) 
        }
    }
}
' file

Prints:

a,b,c,some stuff,"some, other, stuff",    d,2023-03-10 18:37:00
y,x,z,         t,                cool,thing,2022-04-12 21:44:00

Which also handles nasty escaped quotes within fields. Given:

$ cat file
a,b,c,some stuff,"some, other, stuff",d,2023-03-10 18:37:00
y,x,z,t,cool,"""thing"", quoted",2022-04-12 21:44:00

The second version prints:

a,b,c,some stuff,"some, other, stuff",                  d,2023-03-10 18:37:00
y,x,z,         t,                cool,"""thing"", quoted",2022-04-12 21:44:00
0
6

There are some double quoted fields containing text and having commas.

Forget about simple text parsing then. Just get something that can parse complex CSV and let that do the pretty-printing.

Miller is the tool of choice. You can specify "pretty print" as output format:

mlr --icsv --opprint cat example.csv

You can also just use Python's built-in csv module:

import csv

rows = []
maxwidths = []
with open("foo.csv") as csvfile:
    reader = csv.reader(csvfile, delimiter=",", quotechar='"')
    for row in reader:
        for column_idx, entry in enumerate(row):
            if column_idx >= len(maxwidths):
                maxwidths += [len(entry)]
            else:
                maxwidths[column_idx] = max(maxwidths[column_idx], len(entry))
        rows += [row]

for row in rows:
    print(", ".join([f"{col:<{width}}" for col, width in zip(row, maxwidths)]))
5
  • 2
    --c2p for short. Also mlr --c2t cat file | column -t -s $'\t' (transforming it to TSV and then using column as the user was initially trying)
    – Kusalananda
    Mar 10, 2023 at 18:01
  • you can ask. But it's a bit strange, because I said so: "Miller". The installation instructions are linked in my answer. Mar 10, 2023 at 18:06
  • @Kusalananda converting comma separators to tabs then piping to column to format based on tab separation would fail if the input contained tabs so it'd just be shifting the problem.
    – Ed Morton
    Mar 11, 2023 at 12:12
  • @EdMorton Absolutely, I should have said that I assumed the data looked like what it did in the question (which does not look like it contains embedded tabs).
    – Kusalananda
    Mar 11, 2023 at 14:34
  • @Kusalananda yeah, I get it, but tabs are pretty common characters and the OP shows strings like "some, other, stuff" in their input.... I had actually looked up the man page for column hoping to find a -z or similar option that'd let us use \ns as the field separators and \0s as the record terminators (assuming mlr can output such records, which I don't know if it can or not) but no such luck.
    – Ed Morton
    Mar 11, 2023 at 16:15
3

Using GNU awk for FPAT and a 2 pass approach:

$ cat tst.awk
BEGIN {
    FPAT = "([^,]*)|(\"([^\"]|\"\")*\")"
    OFS = ", "
}
NR==FNR {
    for ( i=1; i<=NF; i++ ) {
        wid = length($i)
        wids[i] = ( wid > wids[i] ? wid : wids[i] )
    }
    next
}
{
    for ( i=1; i<=NF; i++ ) {
        printf "%*s%s", wids[i], $i, (i<NF ? OFS : ORS)
    }
}

$ awk -f tst.awk file file
a, b, c, some stuff, "some, other, stuff",     d, 2023-03-10 18:37:00
y, x, z,          t,                 cool, thing, 2022-04-12 21:44:00

Alternatively, the same approach using any awk where you just have to write the code yourself to split each record into fields using a loop calling match() on FPAT and store those fields in an array rather than the above where gawk does that for you as part of normal field splitting:

$ cat tst.awk
BEGIN {
    FPAT = "([^,]*)|(\"([^\"]|\"\")*\")"
    OFS = ", "
}
{
    nf = 0
    rec = $0
    while ( (rec != "") && match(rec,FPAT) ) {
        flds[++nf] = substr(rec,RSTART,RLENGTH)
        rec = substr(rec,RSTART+RLENGTH+1)
    }
}
NR==FNR {
    for ( i=1; i<=nf; i++ ) {
        wid = length(flds[i])
        wids[i] = ( wid > wids[i] ? wid : wids[i] )
    }
    next
}
{
    for ( i=1; i<=nf; i++ ) {
        printf "%*s%s", wids[i], flds[i], (i<nf ? OFS : ORS)
    }
}

$ awk -f tst.awk file file
a, b, c, some stuff, "some, other, stuff",     d, 2023-03-10 18:37:00
y, x, z,          t,                 cool, thing, 2022-04-12 21:44:00

Instead of reading the input twice you could alternatively store the whole of the input in memory and then print it all in the END section which has the advantage that it'd work on input coming from a pipe and the disadvantage that it would fail if your input file was too large to fit in memory. Here's the GNU awk version of that:

$ cat tst.awk
BEGIN {
    FPAT = "([^,]*)|(\"([^\"]|\"\")*\")"
    OFS = ", "
}
{
    for ( i=1; i<=NF; i++ ) {
        flds[NR,i] = $i
        wid = length($i)
        wids[i] = ( wid > wids[i] ? wid : wids[i] )
    }
}
END {
    for ( rowNr=1; rowNr<=NR; rowNr++ ) {
        for ( i=1; i<=NF; i++ ) {
            printf "%*s%s", wids[i], flds[rowNr,i], (i<NF ? OFS : ORS)
        }
    }
}

$ awk -f tst.awk file
a, b, c, some stuff, "some, other, stuff",     d, 2023-03-10 18:37:00
y, x, z,          t,                 cool, thing, 2022-04-12 21:44:00

and the any awk version:

$ cat tst.awk
BEGIN {
    FPAT = "([^,]*)|(\"([^\"]|\"\")*\")"
    OFS = ", "
}
{
    nf = 0
    rec = $0
    while ( (rec != "") && match(rec,FPAT) ) {
        fld = substr(rec,RSTART,RLENGTH)
        flds[NR,++nf] = fld
        wid = length(fld)
        wids[nf] = ( wid > wids[nf] ? wid : wids[nf] )
        rec = substr(rec,RSTART+RLENGTH+1)
    }
}
END {
    for ( rowNr=1; rowNr<=NR; rowNr++ ) {
        for ( i=1; i<=nf; i++ ) {
            printf "%*s%s", wids[i], flds[rowNr,i], (i<nf ? OFS : ORS)
        }
    }
}

$ awk -f tst.awk file
a, b, c, some stuff, "some, other, stuff",     d, 2023-03-10 18:37:00
y, x, z,          t,                 cool, thing, 2022-04-12 21:44:00

You must log in to answer this question.

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