Transform table into csv and back

Question

I have a table

+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| foo   | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

I'd like to define two scripts, one that can transform such table into csv and one that can transform it back.

I know that that kind of possbile with sed

sed -e '1d;3d;$d' -e 's/^|//' -e 's/|$//' -e 's/|/,/g' file

But this would be unreliable.
Reliable way would be to find positions of | character on the second line (except first and last) and on each line turn characters in this positions to , and remove whitespace around them. It can probably done with awk, but I don't know how.

Answer 1

Firstly, it's a mistake to use the pipe character to find out where the fields start and end - you already have a line that defines them precisely, without any chance of the field contents containing the same character as the field separator: the first line, which contains only column markers (+) and filler characters (-).

Here's a perl script (table-to-csv.pl) to extract the data and print it as a csv formatted file. Given that the input data is an sql table definition, it quotes every data field - a more generic version might try to determine whether quoting is necessary (i.e. if the field is numeric or not).

This script is a little more complicated than it really needs to be, e.g. there's no real need to build up the @headers and @data arrays, once the column lenghts are known, each line could just be extracted and printed as they're read in. Doing it this way makes it easier to perform further processing on either or both of the headers and the data if required.

#!/usr/bin/perl -w

use strict;

my @columns = ();
my @headers = ();
my @data = ();

sub extract;  # forward declaration of extract subroutine

# main loop
while(<>) {
  chomp;
  next if (m/^\s*$/);

  if(/^\+-/) {
    # use the '+' chars in the first line to find column positions
    next if (@columns != 0);
    my $i=0;
    while ($i >= 0 && $i < length($_)) {
      my $e=index($_,"+",$i+1);
      # store starting pos & length pair for each column
      push @columns, [ $i+2, $e-3-$i ];
      $i=$e;
    };
    pop @columns;  # last pair will always be bogus, dump it.
  } else {         # extract the headers and data
    if (!@headers) {
      @headers = extract($_,@columns);       # array of field header names
    } else {
      push @data, [ extract($_,@columns) ];  # array of arrays of field data
    };
  };
};

# output in simple csv format.
print join(',',@headers), "\n";

foreach my $l (@data) {
  print join(',',@{ $l }), "\n";
};

### subroutines
sub extract {
  my ($line,@cols) = @_;
  my @f=();

  foreach my $c (@cols) {
    my $d = substr($line,$c->[0],$c->[1]);
    $d =~ s/^\s*|\s*$//g; # strip leading & trailing spaces
    push @f, '"' . $d .'"' ;
  }
  return @f;
};

Output: (with your input table saved as table.txt)

$ ./table-to-csv.pl table.txt 
"Field","Type","Null","Key","Default","Extra"
"id","int(11)","NO","PRI","NULL",""
"foo","varchar(10)","YES","","NULL",""

The second part of your question does require the slight complexity of building the @data array. Reading and parsing CSV is easy, especially if you use a library like perl's Text::CSV module to handle quoted & unquoted fields...but to get the output format correct, you need two passes through the data. The first to find and store the largest width of each field (which is used to control the output format), and the second to print the data.

The following perl script (csv-to-table.pl) requires the Text::CSV module. On debian etc systems, that's in the libtext-csv-perl package. Other distros will have similar package names. Or you can install it yourself with cpan.

#!/usr/bin/perl -w

use strict;
use Text::CSV;

my @data;
my @lengths;

my $csv = Text::CSV->new ();

while (my $row = $csv->getline(*ARGV)) {
  my @fields = @$row;
  foreach my $i (0..@fields-1) {   # find the largest width for each field
    my $len = length($fields[$i]);
    $lengths[$i] = $len if (!defined($lengths[$i]) || $lengths[$i] <= $len);
  };
  push @data, [ @fields ];  # stuff each record into an array of arrays
};

my $hdr='+';
my $fmt='';

foreach (@lengths) {
  # build the header/separator line and the printf format string
  $hdr .= '-' x ($_+2) . '+';
  $fmt .= '| %-' . ($_) . 's ' ;
};
$fmt .= "|\n";
$hdr .= "\n";

# output the table

print $hdr;
printf "$fmt", @{ $data[0] };
print $hdr;

foreach my $i (1..@data-1) {
  printf $fmt, @{ $data[$i++] };
}

print $hdr;

Output:

$ ./table-to-csv.pl table.txt  | ./csv-to-table.pl
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| foo   | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+