3

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.

  • It is possible to get the data from MySQL without the "border" using the -s (or --silent) flag. Your data is then separated with a Tab. – Tigger Jul 20 '17 at 6:23
  • Thanks, I still cannot distinguish between string and numbers, but maybe combining this with describe table I can actually try to generate delete, update and insert statements. I wanted to mirror the changes in visually edited table into mysql database. – user1685095 Jul 20 '17 at 8:12
2

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    |       |
+-------+-------------+------+-----+---------+-------+
  • I'll accept the answer, but mysql doesn't show strings any differently than integers, so it's useless. – user1685095 Jul 20 '17 at 7:41
  • the only sample you gave didn't have any non-string fields. and it doesn't matter at all whether "mysql doesn't show strings any differently than integers" or not (whatever that means) - it only matters whether perl can distinguish between a numeric value (print unquoted) and a non-numeric value (print quoted). It's a trivial change to the extract() sub to do that. My answer - any answer here - was not to just give you a program that does exactly what you want perfectly, it was to show you how it can be done so you can modify to suits your needs AND use the techniques elsewhere. – cas Jul 20 '17 at 7:52
  • mysql doesn't print quotes around strings. – user1685095 Jul 20 '17 at 7:54
  • 1
    There are numerous ways in perl to check whether a value is numeric or not. e.g. you can write a simple regexp, the Scalar::Util module has a looks_like_a_number() function and Regexp::Common has good regex definitions for ints, reals, etc that will catch all of the weird corner-cases that a simple regex written in a few minutes probably won't. – cas Jul 20 '17 at 7:55
  • Also I hoped for one liner. I can write the same thing in python. – user1685095 Jul 20 '17 at 7:55

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.