Beefy Boxes and Bandwidth Generously Provided by pair Networks Russ
Perl-Sensitive Sunglasses
 
PerlMonks  

Win32::OLE and Excel Question/Help

by mmittiga17 (Scribe)
on Feb 12, 2013 at 19:42 UTC ( #1018436=perlquestion: print w/ replies, xml ) Need Help??
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
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
Any Thoughts if this is even possible? Thank you for your time.

Comment on Win32::OLE and Excel Question/Help
Select or Download Code
Re: Win32::OLE and Excel Question/Help
by roboticus (Canon) 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; } }

    ...roboticus

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

Re: Win32::OLE and Excel Question/Help
by kcott (Abbot) 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

    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

    -- Ken

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.

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1018436]
Approved by Corion
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (10)
As of 2014-04-23 11:00 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    April first is:







    Results (541 votes), past polls