Beefy Boxes and Bandwidth Generously Provided by pair Networks
good chemistry is complicated,
and a little bit messy -LW
 
PerlMonks  

Perl parse excel and duplicate rows

by doubledecker (Scribe)
on Feb 08, 2013 at 18:38 UTC ( #1017874=perlquestion: print w/ replies, xml ) Need Help??
doubledecker has asked for the wisdom of the Perl Monks concerning the following question:

Hello monks,

I'm trying to parse an excel for searching some issue codes. In the excel sheet, I look for 'Work Notes' column and try to search for Issue code. If there is more than One issue code, i need to duplicate the row for each Issue code. Could you please help me.. here is what i've tried so far...
Data looks like this Sl No Employee Task Code Work Notes 1 8989 M8899 "Issue Code : ioi-909 jskfjskf Issue Code: 9090-8989" 2 98983 M90909 "Issue Code : ioi-909 Issue Code: 898989898"
use strict; use Spreadsheet::ParseExcel; use Spreadsheet::ParseExcel::SaveParser; use Data::Dumper; my $parser = new Spreadsheet::ParseExcel::SaveParser; my $template = $parser->Parse('Queue.xls'); my $sheet = 0; my $row = 0; my $col = 0; # Get the format from the cell #my $format = $template->{Worksheet}->$sheet->{Cells}$row$col ->{Forma +tNo}; my $worknotes_col = '3'; my @codes; # Write data to some cells # #$template->AddCell(0, $row, $col, 1, $format); #$template->AddCell(0, $row+1, $col, "Hello", $format); for my $worksheet ( $template->worksheets() ) { my ( $row_min, $row_max ) = $worksheet->row_range(); my ( $col_min, $col_max ) = $worksheet->col_range(); my @columns = (3); for my $row ( $row_min .. $row_max ) { @codes = (); for my $col (@columns) { my $cell = $worksheet->get_cell( $row, $col ); next unless $cell; # do the calculations here #print "Row, Col = ($row, $col)\n"; #print "Value = ", $cell->value(), "\n"; if ( $cell->value() ) { my @worknotes_data = split/\n/, $cell->value(); #skip blank lines @worknotes_data = grep (/\S/, @worknotes_data); trim(@worknotes_data); my @issue_codes = getIssueCodes(\@worknotes_data); trim(@issue_codes); if ( scalar @issue_codes > 1 ) { print "INFO: Found multiple Issue codes \n"; my $tmprow = $row; foreach my $issue ( @issue_codes ) { foreach my $a_col ( $col_min .. $col_max ) { my $cell = $worksheet->get_cell( $tmprow, +$a_col ); print $cell->value(), "\n"; $template->AddCell(0, $tmprow, $a_col, $ce +ll->value()); } # now write the actual issue code $template->AddCell(0, $tmprow, $col_max + 1, $ +issue); $tmprow = $tmprow + 1; } } # only 1 issue code is found, so add a new column to e +xisting row elsif ( scalar @issue_codes == 1 ) { print "INFO: Only One issue code is found, @issue_ +codes\n"; $template->AddCell(0, $row, $col_max + 1, "@issue_ +codes"); } else { print "INFO: Skipping the row as no issue codes ar +e found\n"; } } } } } # Add a new worksheet #$template->AddWorksheet('New Data'); # The SaveParser SaveAs() method returns a reference to a # Spreadsheet::WriteExcel object. If you wish you can then # use this to access any of the methods that aren't # available from the SaveParser object. If you don't need # to do this just use SaveAs(). my $workbook; { # SaveAs generates a lot of harmless warnings about unset # Worksheet properties. You can ignore them if you wish. local $^W = 0; # Rewrite the file or save as a new file $workbook = $template->SaveAs('new.xls'); } sub getIssueCodes { my $worknotes = shift; foreach my $line ( @{$worknotes} ) { if ( $line =~ /Issue\s*Code/gi ) { my ($text, $code) = split/\:/, $line; if ( $code !~ /(NA|N\/A)/gi ) { push (@codes, $code); } } } return @codes; } sub trim { @_ = $_ if not @_ and defined wantarray; @_ = @_ if defined wantarray; for (@_ ? @_ : $_) { s/^\s+//, s/\s+$// } return wantarray ? @_ : $_[0] if defined wantarray; }

Comment on Perl parse excel and duplicate rows
Select or Download Code
Re: Perl parse excel and duplicate rows
by runrig (Abbot) on Feb 08, 2013 at 21:05 UTC
    I'm trying to parse an excel for searching some issue codes. In the excel sheet, I look for 'Work Notes' column and try to search for Issue code. If there is more than One issue code, i need to duplicate the row for each Issue code. Could you please help me.. here is what i've tried so far...

    Which part are you having trouble with? Parsing the excel? Finding the 'Work Notes' column? Searching for the Issue code? Duplicating the row in the output?

    Could you post less code that focuses on the part you're having trouble with, and explains what you expect, what you're getting, etc.? I don't have time right now to slog through 100+ lines of code, but 10-20 might be doable. Thanks.

      I am having problem with duplication of rows

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others making s'mores by the fire in the courtyard of the Monastery: (7)
As of 2014-10-23 00:54 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    For retirement, I am banking on:










    Results (123 votes), past polls