3

In the simplest form, let's say I have an auto-generated file called file.txt. The contents of file.txt appears as follows:

 Source                    Destination                Maximum To  Maximum From Average Total   Average To           Average From
(192.168.1.1)   (192.168.1.2)       202.89 Kbps    0 bps         645 bps 645 bps 0 bps

I have tried multiple variations of the column command, all to no avail. How can I make this output look like this:

Source         Destination     Maximum To     Maximum From     Average Total     Average To     Average From
(192.168.1.1)  (192.168.1.2)   202.89 Kbps    0 bps            645 bps           645 bps        0 bps

I feel like I should know how to do this, but I am drawing a blank at the moment and haven't found anything thus far that is working as expected.

EDIT: The comment and answer below using sed work great for my original example (and I actually prefer the solution in the comment because it is simpler and doesn't involve piping to tr). That being said, both solutions perform exactly the same on a file with multiple lines. The actual file.txt will include hundreds of lines of IPv4 addresses of varying lengths. Both sed solutions thus far have returned the following result on the original (and more accurate) file.txt:

Original file.txt:

Source                    Destination                Maximum To  Maximum From Average Total   Average To           Average From
(10.10.10.21)     (192.168.123.122)      18.90 Kbps     0 bps         131 bps 131 bps 0 bps
(10.10.10.22)     (192.168.123.122)       10.88 Kbps     0 bps         23 bps 23 bps 0 bps
(10.10.10.23)     (192.168.123.123)       10.88 Kbps     0 bps         23 bps 23 bps 0 bps
(192.168.123.123) (192.52.168.123)       0 bps          22.84 Kbps    1.17 Kbps 0 bps     1.17 Kbps
(192.168.123.124)  (192.52.168.123)       0 bps          10.87 Kbps    19 bps 0 bps  19 bps

Updated file.txt (after using both suggested solutions thus far):

Source                              Destination        Maximum To  Maximum From  Average Total  Average To  Average From
(10.10.10.21)                       (192.168.123.122)  18.90 Kbps  0 bps         131 bps        131 bps     0 bps
(10.10.10.22)                       (192.168.123.122)  10.88 Kbps  0 bps         23 bps         23 bps      0 bps
(10.10.10.23)                       (192.168.123.123)  10.88 Kbps  0 bps         23 bps         23 bps      0 bps
(192.168.123.123) (192.52.168.123)  0 bps              22.84 Kbps  1.17 Kbps     0 bps          1.17 Kbps
(192.168.123.124)                   (192.52.168.123)   0 bps       10.87 Kbps    19 bps         0 bps       19 bps

Is there an updated solution that would account for this offset caveat?

5
  • It's not a total solution, and given your username you may not like a perl solution, but I would tackle this with perl. The script needs to know which fields to group under which columns, and then to align them with spacing. I'm sure someone else will come with a fancy awk solution, though :)
    – Jeff Schaller
    Oct 6, 2015 at 19:24
  • 1
    sed 's/\s\s\+/:/g;s/\([a-z]\)\s\([0-9A]\)/\1:\2/g' file.txt | column -s: -t
    – Costas
    Oct 6, 2015 at 19:33
  • @Costas - see my updated (and more detailed) situation that includes a caveat that your particular solution doesn't account for. Not your fault, because the original example I provided did not account for this either. Great initial solution, by the way! Oct 6, 2015 at 20:40
  • 1
    sed 's/\s\s\+/:/g;s/\([a-z)]\)\s\([(0-9A]\)/\1:\2/g' file.txt | column -s: -t
    – Costas
    Oct 7, 2015 at 8:55
  • @Costas - Why don't you post this as an answer, rather than a comment? This is exactly what I was looking for, and I would gladly accept your answer. Thank you so much for this, by the way. Oct 8, 2015 at 0:25

3 Answers 3

3

Script is based on OP's sample data.

sed '
    s/\s\s\+/:/g
    s/\([a-z)]\)\s\([(0-9A]\)/\1:\2/g
    ' file.txt | 
column -s: -t
  • first change easy found separator (2 or more \spaces) by :
  • second find remaining possible separators:
    • between low letter and digit
    • after )
    • before A
  • format string with the column's separator :
3

The following perl script converts the input to tab-separated fields, relying on the knowledge that the first two fields have only one "word" each and the remaining fields have two "words" each. The output from this is then piped into column -s $'\t' -t

It's a pretty clumsy and brute-force method, but it works.

#! /usr/bin/perl 

use strict;

while(<>) {
    my (@F, @fields, $i);

    @F=split;
    $fields[0] = $F[0] ;
    $fields[1] = $F[1] ;
    for $i (0..4) {
      $fields[$i + 2] = $F[$i*2 + 2] . ' ' . $F[$i*2 + 3];
    }

    print join("\t",@fields),"\n";
}

It's used like this:

$ ./bandwidth.pl bandwidth.txt | column -s $'\t' -t 
Source             Destination        Maximum To  Maximum From  Average Total  Average To  Average From
(10.10.10.21)      (192.168.123.122)  18.90 Kbps  0 bps         131 bps        131 bps     0 bps
(10.10.10.22)      (192.168.123.122)  10.88 Kbps  0 bps         23 bps         23 bps      0 bps
(10.10.10.23)      (192.168.123.123)  10.88 Kbps  0 bps         23 bps         23 bps      0 bps
(192.168.123.123)  (192.52.168.123)   0 bps       22.84 Kbps    1.17 Kbps      0 bps       1.17 Kbps
(192.168.123.124)  (192.52.168.123)   0 bps       10.87 Kbps    19 bps         0 bps       19 bps

BTW, this is a good example of why it's never a good idea to use a delimiter (e.g. space) that's also in the fields being delimited. It just makes things more difficult than they need to be....and there's no reliable way to distinguish between delimiters and field contents that doesn't require advance knowledge of the file contents and structure.

3
  • btw, it's possible to do the final column formatting in the perl script too but column exists and does the job. There would certainly be a perl module which does the same job as column but i didn't search CPAN for it.
    – cas
    Oct 6, 2015 at 21:21
  • Thank you. It is indeed clumsy, and I really don't care for perl, but it does get the job done perfectly and works great when you need it. This solved my problem, and I appreciate it. Oct 6, 2015 at 21:37
  • 1
    it's a trivial script so i have no doubt that it would be trivial to rewrite in ruby. or almost any other language. all it took was seeing that the actual problem was caused by ambiguous delimiters in the input.
    – cas
    Oct 6, 2015 at 21:41
0

Updated: Using a copy of the full original, naming it in this example full_original.txt:

$ sed 's/\((\)/ \1/g;s/\(Average\)/ \1/g;s/ \([0-9]\)/  \1/g;s/\(\S\) \(\S\)/\1_\2/g' full_original.txt | column -t | tr _ ' '
Source             Destination        Maximum To  Maximum From  Average Total  Average To  Average From
(10.10.10.21)      (192.168.123.122)  18.90 Kbps  0 bps         131 bps        131 bps     0 bps
(10.10.10.22)      (192.168.123.122)  10.88 Kbps  0 bps         23 bps         23 bps      0 bps
(10.10.10.23)      (192.168.123.123)  10.88 Kbps  0 bps         23 bps         23 bps      0 bps
(192.168.123.123)  (192.52.168.123)   0 bps       22.84 Kbps    1.17 Kbps      0 bps       1.17 Kbps
(192.168.123.124)  (192.52.168.123)   0 bps       10.87 Kbps    19 bps         0 bps       19 bps

Explanation

The majority of this solution takes a "divide and conquer" approach where you have multiple separate problems, addressing them individually. Then it is assembled in the end via the magic column command, with a finishing touch of tr:

  • basic pattern is s/searchstring/replacestring/g', g for greedy/global so applies for all matches not just the first
  • we use grouping, so \(somegroup\) in the search portion, can be re-printed via \1 if it is first group, \2 if it is the second group, etc
  • semi-colon ; allows us to put multiple search and replace commands in a single sed instance, more efficient than piping and thus running multiple seds like sed command | sed command | sed command .... etc
  • s/\((\)/ \1/g deals with ...123) (19... pushing the two parenthesis-enclosed ip values more than a single space from each other to avoid an offset issue the OP discovered. It does this by matching any opening parenthesis ( and prefixing with a space so it becomes space+(
  • s/\(Average\)/ \1/g is the part that deals with how Maximum From Average Total have no special separation, making it difficult for a later search and replace, so initially we tack on an additional space before every occurrence of Average
  • s/ \([0-9]\)/ \1/g to separate the field values in original text 645 bps 645 bps 0 bps by prefixing any occurrence of space+number with a space so it becomes space+space+number, again to help a later sed command tell them apart
  • the last sed command s/\(\S\) \(\S\)/\1_\2/g is a workaround, it searches non-space+space+non-space and with grouping, changes it so that space is turned into an underscore. This keeps Maximum To together for the later column command we use, so it becomes Maximum_To
  • | column -t pipes it to column command which, by default, man column says: By default, the column command will merge multiple adjacent delimiters into a single delimiter when using the -t option so it handles the variable space between texts as a single delimiter.
  • column also performs the re-formatting to make texts align
  • finally | tr _ ' ' undoes the workaround of converting spaces to underscores (_) by using tr command, converts all _, back to space ' '.

And thus you have the output you want.

2
  • Please see my updated and clarified file.txt, which will include multiple IPv4 addresses. Your solution works for the most part, but gets thrown off by the offset caveat. Any ideas? Oct 6, 2015 at 20:41
  • PS: I never knew about the semicolon thing with sed in order to prevent piping to multiple sed processes. That is extremely useful. If I don't receive an answer that further addresses my complication, I will accept your answer. Oct 6, 2015 at 20:53

You must log in to answer this question.

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