slavailn:

Yes, that's pretty easy to do in perl. You'll want to get Spreadsheet::ParseExcel or Spreadsheet::XLSX which will help you disassemble the current spreadsheets. The first page of the documentation will help you dig the data out of your spreadsheet files. Then you can print the data table out as a .CSV file which you can load into a spreadsheet fairly easily, or you can go the extra mile and get Spreadsheet::WriteExcel to generate a new spreadsheet with the information you want.

The tricky bit will be when some of your spreadsheets are missing a row or have an extra row and you want to get the data lined up. So I'd suggest using a hash of arrays, where the first column of the array would be the first file, the second column for the second file, etch.

A rough sketch of what I'd do is like this:

my @Files;
my %Table;
while (my $FName = shift) {
    push @Files, $FName;
    # open spreadsheet & get worksheet, as shown in Spreadsheet::Parse
+Excel docs
    for my $row ($row_min .. $row_max) {
        my $category = $worksheet->get_cell($row, 0); # first column h
+as category
        my $value    = $worksheet->get_cell($row, 1); # second column 
+has value
        $Table{$category}[@Files] = $val; # Store into slot in table
    }
}

[download]

When you're done, the resulting data might look something like:

@Files = ('file1', 'file2');
%Table = (
#   Cat      file1  file2
    'A' => [ 12,    34   ],
    'B' => [ 152,   567  ],
);

[download]

Let us know if you need any further hints.

...roboticus

When your only tool is a hammer, all problems look like your thumb.

In reply to Re: simple table join script by roboticus
in thread simple table join script by slavailn

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.