Once again, its appears Perl Monks may benefit if I link out to a useful tool that somehow has never been mentioned within our abbey.

Many of us likely have used Perl to manipulate speadsheets, either as CSV, or using one of the Spreadsheet: modules from CPAN.

But how many of us use Perl One-Liners to manipulate MS Excel Spreadsheets? Can you do this... Yes You Can, with XLSPerl http://perl.jonallen.info/projects/xlstools

While not the most elegant solution I am sure, I have worked up a short example. The code below reads "Sheet1" and prints the value of cells in column "F" each time a unique value is discovered. This has the effect of 'deduping', or removing duplicates.

xlsperl -nle "$val=$_;if($WS eq q(Sheet1) && $COL eq q(F)){unless($found{$val}){print qq($val)}$found{$val}++}" Contacts.xls

Note the use of q() and qq() to account for the requirement under Windows to use double quotes on the command line.

I was wondering if anyone else can share their XLS one-liners, particularly if my musings have prompted you to experiment with XLSperl for the first time! Those deft enough to execute the hack within cmd.exe deserve extra cudos.

Please reply with your wizardly one-liners, or if you merely came with an opinion, offer a well written one...

-- Patrick

spectre#9 -- "Strictly speaking, there are no enlightened people, there is only enlightened activity." -- Shunryu Suzuki

Replies are listed 'Best First'.
Re: MS Excel One-Liner Challenge
by shmem (Chancellor) on Apr 21, 2009 at 23:02 UTC

    Just to make your one-liner a bit more readable... 

    xlsperl -nle "$WS eq q(Sheet1) and $COL eq q(F) and print unless $foun
+d{$_}++" Contacts.xls

    [download]

    ;-)

[reply]
[d/l]
Re: MS Excel One-Liner Challenge
by graff (Chancellor) on Apr 22, 2009 at 09:11 UTC
    I'm impressed. I see that XLSperl even does the right thing when cells contain unicode characters! I managed to find one issue that seems to limit the usefulness of the "XLSprint" function: it looks like it should be easy to do one-liner editing to create a modified xls file, like this: 
    XLSperl -lane 's/fubar/foobar/ for(@F); XLSprint @F' < old.xls > new.x
+ls

    [download]
    and that does in fact work well -- except when the input xls file has two or more non-empty worksheets one or more non-empty worksheets with a name other than "Sheet1", in which case all worksheets get glommed together into "Sheet1" in the output xls file. (If there is a way to preserve worksheet names and boundaries in this sort of edit proces, someone please enlighten me.)

    I noticed the problem when trying verify the results of such an edit process, using a one-liner like this: 

    XLSperl -lne '$h{"$WS:$CELL: $_"}++;END{print for(grep {$h{$_}==1} key
+s %h)}' old.xls new.xls

    [download]
    That turns out to be very handy for listing cell-value differences between two xls files -- provided that they both contain the same worksheets and are mostly similar except for value differences in particular cells.

    But if the two files don't have the same quantity and names of worksheets, this sort of comparison is useless (or rather, it reports an awful lot of diffs, which actually makes it good for discovering problems, like the loss of worksheet boundaries caused by simple cell-editing operations.)

    UPDATE: Here's a slightly more verbose but more informative version of the one-liner for comparing two xls files: 

    XLSperl -lne '$h{"$WS:$CELL:$_"}.="in $ARGV";
  END{print "$_ ($h{$_})" for(grep{$h{$_}!~/\.xlsin /}sort keys %h)}' 
+a.xls b.xls

    [download]
    It sorts the cells in the output listing, and tells which file contained each of the unique cell values.
[reply]
[d/l]
[select]
      I truly like the 'find differences' functionality you illustrate in the second code block. This is exactly the sort of contribution I am seeking! Thank you.

      -- Patrick

      UPDATE: The use of END in the second and third examples above is something that, despite 15 years of Perl usage, I have never employed. Therefore this monk has been bless with a serendiptous enlightenment from his Meditation. Much Thanks!

      spectre#9 -- "Strictly speaking, there are no enlightened people, there is only enlightened activity." -- Shunryu Suzuki
[reply]

Back to Meditations