mmittiga17 has asked for the wisdom of the Perl Monks concerning the following question:

Hi All, I use win32ole to xls2tab formatted files for processing. Question I have is how can I merge lines to reformat the xls itself? Each unique record could take up 5 rows or more. Each record will alway begin in column A and can be identified with the word CUSIP. xls file will go something like this: 

CUSIP    data data data
data data data data
data data data data
CUSIP    data data data
data data data data
data data data data
CUSIP    data data data
data data data data
data data data data

[download]
What I am trying to do is merge ROWS UP to the ROW that starts with CUSIP.

what I want is: 

CUSIP    data data data data data data data data data data data

[download]
Any Thoughts if this is even possible? Thank you for your time.

Replies are listed 'Best First'.
Re: Win32::OLE and Excel Question/Help
by roboticus (Chancellor) on Feb 12, 2013 at 20:26 UTC

    mmittiga17:

    Perhaps something like:

    my $buffer;
while (my $line = <$INFH>) {
   $line =~ s/\n$//;
   if ($line =~ /^CUSIP/) {
      print $OUTFH $buffer, "\n" if defined $buffer;
      $buffer = $line;
   }
   else {
      $buffer .= $line;
   }
}

    [download]

    ...roboticus

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

[reply]
[d/l]
Re: Win32::OLE and Excel Question/Help
by kcott (Archbishop) on Feb 13, 2013 at 09:12 UTC

    G'day mmittiga17,

    Here's another way to do it:

    #!/usr/bin/env perl

use 5.010;
use strict;
use warnings;

local $/ = 'CUSIP';

while (<DATA>) {
    s{\s*$/$}{}m;
    next unless $_;
    s{\n}{ }gm;
    s{\s*$}{};
    say $/, $_;
}

__DATA__
CUSIP    data data data
data data data data
data data data data
CUSIP    data data data
data data data data
data data data data
CUSIP    data data data
data data data data
data data data data

    [download]

    Output:

    $ pm_merge_multiline_with_leader.pl
CUSIP    data data data data data data data data data data data
CUSIP    data data data data data data data data data data data
CUSIP    data data data data data data data data data data data

    [download]

    -- Ken

[reply]
[d/l]
[select]
Re: Win32::OLE and Excel Question/Help
by bulk88 (Priest) on Feb 13, 2013 at 16:35 UTC
    This node has a bad title, but I'm not sure what to change it to, so I wont "consider" it. If someone can think of a better title please consider it.
[reply]

Back to Seekers of Perl Wisdom