Marco Falzone Marco Falzone - 4 months ago 23
Linux Question

How can I adjust the length of a column field in bash using awk or sed?

I've an input.csv file in which columns 2 and 3 have variable lengtt.

100,Short Column, 199
200,Meeedium Column,1254
300,Loooooooooooong Column,35


I'm trying to use the following command to achieve a clean tabulation, but I need to fill the 2nd column with a certain number of blank spaces in order to get a fixed lenght column (let's say that a total lenght of 30 is enough).

awk -F, '{print $1 "\t" $2 "\t" $3;}' input.csv


My current output looks like this:

100 Short Column 199
200 Meeedium Column 1254
300 Loooooooooooong Column 35


And I would like to achieve the following output, by filling 2nd and 3rd column properly:

100 Short Column 199
200 Meeedium Column 1254
300 Loooooooooooong Column 35


Any good idea out there about awk or sed command should be used?
Thanks everybody.

Answer

Rather than picking some arbitrary number as the width of each field, do a 2-pass approach where the first pass calculates the max length of each field and the 2nd prints the fields in a width that size plus a couple of spaces between fields:

$ cat tst.awk
BEGIN { FS=" *, *"; OFS="  " }
NR==FNR {
    for (i=1;i<=NF;i++) {
        w[i] = (length($i) > w[i] ? length($i) : w[i])
        if ($i ~ /[^0-9]/) {
            a[i] = "-"
        }
    }
    next
}
{
    for (i=1;i<=NF;i++) {
        printf "%"a[i]w[i]"s%s", $i, (i<NF ? OFS : ORS)
    }
}

$ awk -f tst.awk file file
100  Short Column             199
200  Meeedium Column         1254
300  Loooooooooooong Column    35

The above also uses left-alignment for non-digit fields, right alignment for all-digits fields. It'll work no matter how long the input fields are and no matter how many fields you have:

$ cat file1
100000,Short Column, 199,a
100,Now is the Winter of our discontent with fixed width fields,20000,b
100,Short Column, 199,c
200,Meeedium Column,1254,d
300,Loooooooooooong Column,35,e

$ awk -f tst.awk file1 file1
100000  Short Column                                                   199  a
   100  Now is the Winter of our discontent with fixed width fields  20000  b
   100  Short Column                                                   199  c
   200  Meeedium Column                                               1254  d
   300  Loooooooooooong Column                                          35  e