2

I'm in a situation where I want to be able to take SQL query results from SQuirreL and paste them as ascii text into various forms and reports. With SQuirreL, I can export results as Excel or a CSV, but I'd like to eventually format the results in something like this format;

+---------+--------+-------+
|  header | header | header|
+---------+--------+-------+
|  value  | value  | value |
+---------+--------+-------+

I would be amazed if this hasn't been done before, but my searches for this have come up empty.

Update:

Note that I saw https://stackoverflow.com/questions/10518207/tool-to-convert-csv-data-to-stackoverflow-friendly-text-only-table , but those are all web site solutions. I'd like something I can run on the command line.

  • Does this boil down to converting csv to this graphical box format? Do you know in advance how many columns there will be? – Jeff Schaller Sep 19 '16 at 23:41
  • No, I want a general solution, so it will have to determine max value in each column also. I've started to step through a Perl solution using Text::CSV, but I'm surprised a command-line (Perl?) solution hasn't been implemented already. – David M. Karr Sep 20 '16 at 0:58
  • What do you mean by "as a breadcrumb"? The first looks like exactly what I need, although the the documentation is very hard to understand. – David M. Karr Sep 20 '16 at 15:15
  • just that it's a comment and not an Answer – Jeff Schaller Sep 20 '16 at 15:44
2

I ended up looking at three possible solutions, all in Perl.

All three use Text::CSV as a base, to read in the CSV.

Before I settled on a prepared Table package, I just tried doing it manually. This was straightforward, but a little tedious. I had to manually determine max field lengths and draw the required pieces. Even so, a well-spaced Perl script doing this only took 78 lines.

I then looked at the Perl Text::Table package, at http://search.cpan.org/~shlomif/Text-Table-1.131/lib/Text/Table.pm . From a very quick look, this looked like what I needed. However, what I discovered is that I really didn't understand the documentation, and the examples didn't help. I gave up on this.

I then discovered Text::ASCIITable, at http://search.cpan.org/~lunatic/Text-ASCIITable-0.20/lib/Text/ASCIITable.pm . There were enough examples showing what I needed to do so that it was easy to build the script I needed. The only problem I ran into was that the "draw()" method didn't appear to work (did nothing), although just "print $table" worked fine. The resulting solution was only 31 lines long.

This is the entire script:

#! /bin/perl
use strict;
use Text::CSV;
use Text::ASCIITable;

my $csv = Text::CSV->new();
my $filename = shift @ARGV;
my $fh;
if (defined $filename) {
    open $fh, "<$filename";
}
else {
    $fh = *STDIN;
}

my $asciitable  = Text::ASCIITable->new();
$asciitable->setOptions('reportErrors', 1);

my $firstRow = $csv->getline($fh);
my @firstRowFields = @$firstRow;
my $numColumns = $#firstRowFields + 1;

$asciitable->setCols(@firstRowFields);

while (my $row = $csv->getline($fh)) {
    $asciitable->addRow($row);
}

print $asciitable;

exit(0);

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.