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

Re: How to combine rows of a spreadsheet

by wrinkles (Pilgrim)
on Sep 09, 2010 at 19:48 UTC ( #859552=note: print w/ replies, xml ) Need Help??


in reply to How to combine rows of a spreadsheet

Many thanks to JavaFan and wwe for much-appreciated help. I will learn from your approaches to this problem. After replacing the code where I pushed an arrayref to pushing an anonymous array, my code fell into place. So I've pasted it below for completeness. (correction added 9-22-2010)

#!/usr/bin/perl use strict; use Text::CSV; use Data::Dumper; my $report_file = '/path/to/output.csv'; my $csv_file = '/path/to/input.csv'; my $csv = Text::CSV->new ( { allow_whitespace => 1, binary => 1, sep_c +har => ',' } ) or die "Cannot use CSV: ".Text::CSV->error_diag (); my $csv_fh; open ($csv_fh, "<:encoding(utf8)", $csv_file) or die "$csv_file : $!"; # declare column names for getline_hr $csv->column_names($csv->getline($csv_fh)) or die "$csv_file : $!"; # Build Array of ArrayRefs my @records = (); my @temp = (); my %column_order = (); my $ci= 0; while (my $row = $csv->getline_hr($csv_fh)) { my $key; my $value; if (${$row}{'Type'} eq 'Shopping Cart Payment Received') { + # check if this is the first row of t +ransaction push (@records, [@temp]) if @temp; + # push last tra +nsaction hash to record - DON'T USE \@temp @temp = (); + + # reset temp array while (($key, $value) = each %{$row}) { + # iterate through shopping cart row ${%column_order}{$key} = $ci++ unless (defined ${%column_o +rder}{$key}); # associate each unique column name with a position $temp["${%column_order}{$key}"] = $value; + # place the value +in the proper position } } elsif (${$row}{'Type'} eq 'Shopping Cart Item') { my $prefix = ${$row}{'Item ID'}; + # prefix to + create new column names unique to this Item while (($key, $value) = each %{$row}) { + # iterate through shopping cart row if ($key eq 'Quantity') { my $new_column = '_' . $prefix . '::' . $key; ${%column_order}{$new_column} = $ci++ unless (defi +ned ${%column_order}{$new_column}); $temp["${%column_order}{$new_column}"] = $value; } if ($key eq 'Gross') { my $new_column = '_' . $prefix . '::' . $key; ${%column_order}{$new_column} = $ci++ unless (defi +ned ${%column_order}{$new_column}); $temp["${%column_order}{$new_column}"] = $value; } #wrong if ($prefix eq ('Widget-1' || 'Widget-2')) if ($prefix =~ /(Widget-1|Widget-2)/) #better { # only these two products have options if ($key eq 'Option 1 Name') { my $new_column = '_' . $prefix . '::' . $key; ${%column_order}{$new_column} = $ci++ unless ( +defined ${%column_order}{$new_column}); $temp["${%column_order}{$new_column}"] = $valu +e; } if ($key eq 'Option 1 Value') { my $new_column = '_' . $prefix . '::' . $key; ${%column_order}{$new_column} = $ci++ unless ( +defined ${%column_order}{$new_column}); $temp["${%column_order}{$new_column}"] = $valu +e; } } } } elsif (${$row}{'Type'} eq 'Update to eCheck Received') { push (@records, [@temp]) if @temp; @temp = (); while (($key, $value) = each %{$row}) { ${%column_order}{$key} = $ci++ unless (defined ${% +column_order}{$key}); $temp["${%column_order}{$key}"] = $value; } } elsif (${$row}{'Type'} eq 'Refund') { push (@records, [@temp]) if @temp; + @temp = (); + while (($key, $value) = each %{$row}) { ${%column_order}{$key} = $ci++ unless (defined ${% +column_order}{$key}); $temp["${%column_order}{$key}"] = $value; + } } } my $report_fh; open ($report_fh, ">:encoding(utf8)", $report_file) or die "$report_fi +le : $!"; my @toprow = (sort {$column_order{$a}<=>$column_order{$b}} keys %colum +n_order); my $status = $csv->print($report_fh, \@toprow); print $report_fh "\n"; for (@records) { $status = $csv->print($report_fh, $_); print $report_fh "\n"; }


Comment on Re: How to combine rows of a spreadsheet
Download Code

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others surveying the Monastery: (5)
As of 2014-12-20 10:10 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (95 votes), past polls