I've got some tab-delimited data coming out of a Unix pipe. I'd like to format this data into a compact human-readable table.

How can I expand these tabs into spaces, and automatically set the tab stops based on the width of the field data?

For example, my hypothetical generate_tsv script might produce (I'm using \t to represent actual tab characters) —


Using | expand -t 12, I'd get:

alpha       1           0.21085026  ok
beta        4096        0.0         ok
gamma                   -1.0        invalid

But I'd like something more compact (so there are exactly two spaces separating columns), like this:

alpha  1     0.21085026  ok
beta   4096  0.0         ok
gamma        -1.0        invalid

As suggested by @jw013, | column -t -s $'\t' is close, but not quite correct, as it collapses the empty cell:

alpha  1     0.21085026  ok
beta   4096  0.0         ok
gamma  -1.0  invalid
  • Just a question: do you have tab characters in your files or literal \t's?
    – jw013
    Feb 9 '12 at 5:14
  • Tab characters (I just clarified that in the question).
    – smokris
    Feb 9 '12 at 5:27

If you have column(1), an old BSD tool, try column -t, for pretty-printing tables.

To ensure empty cells are displayed, you could try the approach of inserting a single space in each empty cell (recognizable by two consecutive tabs). The idea is column(1) should give the space character its own column but being a single character in width it should not affect the table dimensions or be visible in the output to humans.

generate_tsv | 
   awk '/\t\t/ { for (i = 0; i < 2; i++) gsub(/\t\t/, "\t \t") } 1' | 
   column -t -s $'\t'

The extra awk inserted in the pipeline does the inserting of spaces into each empty cell, as described. 2 passes are necessary to handle 2 consecutive empty cells (\t\t\t).

  • Oh cool, I hadn't seen that command. | column -t -s $'\t' does almost exactly what I want, except that it loses empty cells (a\t\tc apparently gets interpreted as cell a followed by cell c, instead of cell a, empty cell, cell c as I'd prefer).
    – smokris
    Feb 9 '12 at 4:14
  • The Debian version of column(1) appears to have been patched with an additional -n flag that addresses this (runs of consecutive column delimiters). If you don't have that, I've edited an alternate approach into my answer.
    – jw013
    Feb 9 '12 at 5:28
  • Excellent. Thanks for your help, @jw013.
    – smokris
    Feb 11 '12 at 7:03

This is a roundabout way to do it, but it works and has some features not found in column.
It tabulates, taking empty lines and empty cells into consideration.
It can join multiple files, side by side. eg. Prefix rows of "F1" with rows from "Nb"
Blank lines are displayed as empty cells (for multi-file paste)
The files can be of different lengths.

# Make test data file    
echo -e "alpha\t1\t0.21085026\tok
beta\t4096\t0.0\tnext F1 line is blank

gamma\t\t-1.0\tinvalid" >F1
sed -n '=' F1 >Nb
echo -e "\tB\tC\textra F1 line" >>F1

# Set the positional parameters $1, $2
set Nb F1  

{ echo -e "<html>\n<table border=1 cellpadding=0 cellspacing=0>"
  paste "$@" |sed -e 's#\(.*\)#\x09\1\x09#
                      s#\x09# </pre></td>\n<td><pre> #g
                      s#^ </pre></td>#<tr>#
                      s#\n<td><pre> $#\n</tr>#'
  echo -e "</table>\n</html>"
}|w3m -dump -T 'text/html'

The output of the tabulated version:

│ 1 │ alpha │ 1    │ 0.21085026 │ ok                    │
│ 2 │ beta  │ 4096 │ 0.0        │ next F1 line is blank │
│ 3 │       │      │            │                       │
│ 4 │ gamma │      │ -1.0       │ invalid               │
│   │       │ B    │ C          │ extra F1 line         │

To get a non-framed version, it only requires the removal of the frame. This is easily done by piping the output one step further as shown by the following line of code, which is a replacement for the last line of the above tabulated version.

# the replacement "new" last line
}|w3m -dump -T 'text/html' |sed -r '/^┌|├|└/d; s/^│ //g; s/ │$//g; s/ │ /│/g' 

Here is the "new" out put: I've left the vertical dividers in place. If you don't want them, just change the last sed expression to whatever you like.

1│alpha│1   │0.21085026│ok                   
2│beta │4096│0.0       │next F1 line is blank
3│     │    │          │                     
4│gamma│    │-1.0      │invalid              
 │     │B   │C         │extra F1 line        

or, replacing the / │ / with / / (2 spaces), it looks this.

1  alpha  1     0.21085026  ok                   
2  beta   4096  0.0         next F1 line is blank
4  gamma        -1.0        invalid              
          B     C           extra F1 line        

And, of course, the leading numbers are there only to demonstrate the side-by-side file "paste".


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.