Script to convert CSV to formatted plain text table

[Carlton]

Hi all,

I don't suppose anyone has a filter for converting (say) CSV into an
ASCII table do they? (I'm after the sort of thing you get from, say,
the MySQL command line client.)

My Google skills seemed to have failed me.

Thanks,
Carlton

[G. T. Stresen-Reuter]

Import into mysql and pipe query to file?

Ted

Sent from my iPhone

[Carlton Gibson]

Hi Ted, 

On 16 Jun 2009, at 09:36, G. T. Stresen-Reuter wrote:

Import into mysql and pipe query to file?

That may well work. It's a bit "sledgehammer to crack a nut" though — :-) — and I'd need a table defined already...

I was thinking that some PERL script or a clever pipeline (perhaps using `cut` and a bit of `printf`) would do it, but I thought this must have been done a thousand times. I couldn't find anything on CPAN, but I think my problem is that (for good reason) "ASCII table" doesn't turn up what I'm looking for.

I hope you're well.

Regards,

Carlton

[Carlton Gibson]

Hi All,

A bit of further browsing and it seems EMACS has a whole set of "Text
Based Tables" functionality:

http://www.impernix.org/Manuals/emacs/Text-Based-Tables.html#Text-Based-Tables

I'm wondering does anybody know if the code behind this (or
equivalent) is available as a stand-alone library that I can use as
the basic for BBEdit Filters?

Thanks,
Carlton

On 16 Jun 2009, at 09:36, G. T. Stresen-Reuter wrote:

>

[Doug McNutt]

Assuming the OP meant, by "ASCII table", a flow of text that will appear in a BBEedit window as a table the real answer is no way.

It might be possible to demand a constant width font and add space characters to make such a file appear as a table of sorts but editing the contents would be nearly impossible. There is a UNIX tool - entab - that does things a bit like that but it wouldn't be perfect.

BBEdit just does not support typewriter style tab stops for its windows. Sigh.

If the OP means an HTML table, that's another thing.

I use a spreadsheet for tab separated data. Comma separated is easy to convert to tabs unless there are commas in the data that need protection by escaping.

--
-> Stocks have been pelloreid <-

[Carlton Gibson]

Thanks for the reply...

I meant to take something like this: 

col1,col2,col3

xx,xxx,xxxxx

and turn it into something like:

***********************

* col1 * col2 * col3  *

***********************

* xx   * xxx  * xxxxx *

***********************

I guess this corresponds to your constant width and spaces option.

Obviously there'd be wiggles but it should go something like: 

    # determine each column width

    # print each row

I see these kinds of tables around from time to time and have assumed that someone's already written this code, as well as that to turn it back, and set separators etc...  

Thanks again,

Carlton

[Doug McNutt]

At 16:18 +0100 6/16/09, Carlton Gibson wrote:
I meant to take something like this:

col1,col2,col3
xx,xxx,xxxxx

and turn it into something like:

***********************
* col1 * col2 * col3 *
***********************
* xx * xxx * xxxxx *
***********************

Readers: Switch to a constant width font in your display to see what he means.

perl has an x= operator. That's a small ASCII x that creates a string of characters x items long. It's like a += operator that adds a number to the left argument and replaces it. Page 107 in the Camel book.

$string = " ";
$string x= 5;

I would think it possible to look for regular expressions like /(\w+),/ and replace with $1 concatenated with $colwidth -len($1) spaces. In English that says search for any number of word characters followed by a comma and set variable $1 to the word characters as a string. Then add spaces as required to make up the column width and replace.

As a stand alone perl script with a while loop is ought to be fairly simple. I would probably scan the entire file as a single string variable and push results, one by one, onto a new string. You would have to scan the file first to produce a list of column widths.

Making it work as a BBEdit search and replace is another thing but it might be possible.
--

--> From the U S of A, the only socialist country that refuses to admit it. <--

[Charlie Garrison]

Good afternoon,

On 16/06/09 at 9:51 AM +0100, Carlton Gibson <carlton...@gmail.com> wrote:

>I was thinking that some PERL script or a clever pipeline (perhaps
>using `cut` and a bit of `printf`) would do it, but I thought this
>must have been done a thousand times. I couldn't find anything on
>CPAN, but I think my problem is that (for good reason) "ASCII table"
>doesn't turn up what I'm looking for.

If you're happy with Perl, then the Text::Table module will do what you want:

<http://search.cpan.org/perldoc?Text::Table>

Charlie

--
Charlie Garrison <garr...@zeta.org.au>
PO Box 141, Windsor, NSW 2756, Australia

O< ascii ribbon campaign - stop html mail - www.asciiribbon.org
http://www.ietf.org/rfc/rfc1855.txt

[Carlton Gibson]

Hi Charlie, 

On 17 Jun 2009, at 06:51, Charlie Garrison wrote:

If you're happy with Perl, then the Text::Table module will do what you want:

<http://search.cpan.org/perldoc?Text::Table>

Thanks that looks like it will do it! 

Regards,

Carlton

[steveax]

There's entable:

http://entable.s3.amazonaws.com/index.html

HTH,

[Carlton Gibson]

Brilliant! Thanks.

(I knew someone had done this before.)

[pete boardman]

On 16 Jun 2009, at 09:15, Carlton wrote:

> I don't suppose anyone has a filter for converting (say) CSV into an
> ASCII table do they?

Nice to see some home-brewed solutions, and not all in Perl :)!

Here's a short and simple script in newLISP:

#!/usr/bin/env newlisp
(set 'file (open ((main-args) 2) "read"))
(set 'column-widths (dup 0 80))
(while (read-line file)
(push (set 'line (parse (current-line) ",")) content -1)
(unless (empty? line)
(set 'column-widths (map max (map length line) column-widths))))
(set 'format-string (join (map (fn (s) (string "%-" (inc s) "s"))
column-widths)))
(map (fn (l) (unless (empty? l) (println (format format-string l))))
content)

which turns this input:

----------------
Heading,Title,Value,Extremely Long Column,Notes
1,12312312,,1231241231231,12312
a,b,c,d,e

f,g,c,,

,,,,this is a note
----------------

into this output:

----------------
Heading Title Value Extremely Long Column Notes
1 12312312 1231241231231 12312
a b c d e
f g c
this is a note
----------------

The great thing about BBEdit's Unix filters is that you can apply them
to just the selected text...

cheers

[Robert A. Rosenberg]

At 16:18 +0100 on 06/16/2009, Carlton Gibson wrote about Re: Script to convert CSV to formatted plain text table:

Thanks for the reply...

I meant to take something like this:

col1,col2,col3

xx,xxx,xxxxx

and turn it into something like:

***********************

* col1 * col2 * col3  *

***********************

* xx   * xxx  * xxxxx *

***********************

I am popping in a little late to this thread but I just saw an announcement for something that might meet your needs.

The description is:

Spread2Web 2.0 It converts a Comma Separated Values (.csv) file to a simple HTML table structure. It also remembers files previously converted for easier updates. This release now works with Tab Delimited (.tab) files and with CSV files that have strings encapsulated inside of quotes. Spread2Web is free (donations welcome) for Mac OS X (Universal Binary) or Windows.

The site is http://saltybytes.com/SaltyBytes/Downloads.html.

I hope this helps (the price is right if it is what you are looking for).

[Rodney Schmidt]

Excel does the same, if you already own it.

Sincerely,

Rodney Schmidt

---

From: "Robert A. Rosenberg" <rar...@banet.net>
Organization: RAR Programming Systems Ltd.
Reply-To: <bbe...@googlegroups.com>
Date: Tue, 23 Jun 2009 23:38:40 -0400
To: <bbe...@googlegroups.com>
Cc: Carlton Gibson <carlton...@gmail.com>
Subject: Re: Script to convert CSV to formatted plain text table