App-combinesheets
view release on metacpan or search on metacpan
lib/App/combinesheets.pm view on Meta::CPAN
Age Name
28 Kim
20 Katrin
30 Blanka
50 Lazy author
The output (again, depending on which input is considered a primary
input) will be (a list of included column is defined in the
configuration file - see later):
combinesheets -cfg books_to_authors.cfg -in BOOK=books.tsv AUTHOR=authors.tsv
Name Title Age Note
Blanka Book 1 30 from B1-d
Katrin Book 3 20 from B3-c
Katrin Book 2 20 from B2-e
Kim Book 1 28 from B1-a
Kim Book 2 28 from B2-b
combinesheets -cfg books_to_authors.cfg -in AUTHOR=authors.tsv BOOK=books.tsv
Name Title Age Note
Blanka Book 1 30 from B1-d
Katrin Book 3 20 from B3-c
Katrin Book 2 20 from B2-e
Kim Book 1 28 from B1-a
Kim Book 2 28 from B2-b
Lazy author 50
=head1 ADVANCED USAGE
Additionally to the merging columns from one or more spreadsheets,
this script can also add completely new columns to the resulting
spreadsheet, the columns that do not exist in any of the input
spreadsheet. Such columns are called C<calculated columns>.
Each C<calculated column> is created either by an external,
command-line driven, program, or by a Perl subroutine. In both cases,
the user must create (write) such external program or such Perl
subroutine. Therefore, this usage is meant more for developers than
for the end users.
Note that this advanced feature is meant only for new columns, not for
new rows. Therefore, it cannot be used, for example, to create rows
with totals of columns.
=head2 Calculated columns by external programs
If specified, an external program is invoked for each row. It can be
specified either by a keyword B<PROG> or by a keyword B<PROGS> - see
syntax in the I<configuration> section. In both cases, the
value of the standard output of these programs become the value of the
calculated column (a trailing newline of this standard output is
removed and other newlines are replaced by spaces).
A program defined by the B<PROGS> is called without any arguments
(C<S> in I<PROGS> stands for a I<Simple>). That's why it does not have
any knowledge for which row it has been invoked. Its usage is,
therefore, for column values that are not dependent on other values
from the spreadsheet. For example, for the C<cars.tsv> shown above,
you can add a column C<Last updated> by calling a UNIX program C<date>
- again, see an example the I<configuration>
section.
A program defined by the B<PROG> is called with one argument which is
a filename. This file contains the current row; each of its lines has
two, TAB-separated, fields. The first field is the column name and the
second field is the column value. For example, when processing the
last row of the C<cars.tsv> given above, the file will have the
following content:
Model Skoda
Year 2002
Owned by Senger
The files are only temporary and will be removed when
C<combinesheets> finishes.
=head2 Calculated columns by a Perl subroutine
If specified by the keyword B<PERL>, a Perl subroutine is called for
each row with the three arguments:
=over
=item 1
A hashref with information about the current column. Not often used
but may be handy if the same subroutine deals with more columns and,
therefore, needs to know for which column it was invoked. See the
I<flights> example in the I<configuration> section.
=item 2
An arrayref with all column names.
=item 3
An arrayref with all column values - in the same order as the column
names.
=back
Actually, depending how the subroutine is defined in the
configuration, it may get as the first argument the module/class name
where it belongs to. If you define it like this:
PERL Module::Example::test
the C<test> subroutine is called, indeed, with the three arguments as
described above. However, if your definition is rather:
PERL Module::Example->test
then the C<test> subroutine is considered a Perl method and its first
argument is the module/class name. It is up to you to decide how you
want/need to write your functions. Again, an example is available in
the I<configuration> section.
The return value of the subroutine will become a new value in the
calculated column. Do not return undef but rather an empty string if
lib/App/combinesheets.pm view on Meta::CPAN
There should be one MATCH line for each input spreadsheet. The data in
the column defined by the "column-header" will be used to find the
corresponding lines. In our example, the data in the column I<Surname>
in the C<persons.tsv> will be matched with the data in the column
I<Owned by> in the C<cars.tsv> (the rows having the same values in
these two columns will be merged into one resulting row).
B<Advanced configuration>
If you want to add so-called I<calculated columns> as described in the
L</"ADVANCED USAGE"> you need to use few additional reserved words in the
configuration file. These words are B<PROG>, B<PROGS> and/or
B<PERL>. They are used in the place where the new calculated column
should be placed. Their lines have the program name or the Perl
subroutine name in the second column, and they have mandatory third
column with the resulting name of the calculated column.
For example, we wish to add two columns to the input spreadsheet
C<cars.tsv>. The input file (the same as in the introduction) is:
Model Year Owned by
Praga 1936 Someone else
Mini 1968 Gudernova
Skoda 2002 Senger
We wish to add a column I<Car age> that shows the difference between
the actual year and the value from the I<Year> column. We have a
shell script C<age.sh> doing it:
#!/bin/bash
YEAR=`grep Year $1 | cut -f2`
NOW=`date +%Y`
echo $(($NOW-$YEAR))
The configuration file C<cars.cfg> (assuming that we want the other
columns to remain the same) is:
MATCH CAR=Owned by
CAR Owned by
CAR Model
CAR Year
PROG age.sh Car age
When we run:
combinesheets -config cars.cfg -inputs CAR=cars.tsv
we get this result:
Owned by Model Year Car age
Gudernova Mini 1968 44
Senger Skoda 2002 10
Someone else Praga 1936 76
You can see that there is no need to use C<combinesheets> for really
combining I<more> sheets, an input can be just one sheet.
Another example adds a I<fixed> column to the same input, a column
named I<Last updated> that gets its value from a UNIX command
C<date>. This program does not get any information which row it has
been invoked for. The configuration file is now (note the new line
with the B<PROGS>):
MATCH CAR=Owned by
CAR Owned by
CAR Model
CAR Year
PROG age.sh Car age
PROGS date Last updated
and the result is now:
Owned by Model Year Car age Last updated
Gudernova Mini 1968 44 Mon Feb 27 12:32:04 AST 2012
Senger Skoda 2002 10 Mon Feb 27 12:32:04 AST 2012
Someone else Praga 1936 76 Mon Feb 27 12:32:04 AST 2012
The last possibility is to call a Perl subroutine - using the reserved
word B<PERL> in the configuration file. Let's have an input
spreadsheet (C<flights.tsv>) with data about flights:
Date Flight Airport From Airport To
2009-01-18 AY838 London LHR Helsinki Vantaa
2009-01-22 AY839 Helsinki Vantaa London LHR
2009-03-15 NW2 Manila Tokyo Narita
2009-03-21 NW1 Tokyo Narita Manila
2011-05-06 SV326 Sharm El Sheik Jeddah
2011-07-31 RJ700 Amman Jeddah
2011-09-21 ME369 Jeddah Beirut
2011-09-24 ME368 Beirut Jeddah
2011-12-02 EZY3064 Prague London Stansted
2011-12-09 EZY3067 London Stansted Prague
2012-01-26 MS663 Cairo Jeddah
We want to add columns with the international airport codes for both
I<Airport From> and I<Airport To>. The new columns will be named
I<Code From> and I<Code To>. The Perl subroutine will use a web
service to find the code. The subroutine will use a closure that will
remember already fetched codes so the web service does not need to be
called several times for the same airport name.
The configuration file C<flights.cfg> is:
MATCH FLY=Date
FLY Date
FLY Flight
FLY Airport From
PERL Airport->find_code Code From
FLY Airport To
PERL Airport->find_code Code To
The name of the subroutine is attached to the module where it comes
from by either B<::> or B<-E<gt>> notation.
The invocation is:
combinesheets -config flights.cfg -inputs FLY=flights.tsv
The full code for the module C<Airport>, the file C<Airport.pm> is
here:
package Airport;
use warnings;
use strict;
use LWP::Simple;
use JSON;
# preparing a closure in order not to fetch the same airport code again and again
my $already_found = make_already_found();
sub make_already_found {
my $already_found = {};
( run in 1.147 second using v1.01-cache-2.11-cpan-bbe5e583499 )