Fellow Monks I need your help,
PayPal produces an IPN history of shopping cart transactions in a csv format, with the first row of the report as column heading. Then the subsequent transactions span at least two rows: the first row is general info, and the (immediately) following rows contain product info (and redundant information). I want to flatten the data for one transaction into one row, and add relevant product headings as I go. For example:
Txn ID, Name , Type , Product ID, Qty, Gross
010 , Moe , Cart , , 1 , 15
010 , Moe , Item , widget-1 , 1 , 15
020 , Larry, Cart , , 3 , 35
020 , Larry, Item , widget-1 , 1 , 15
020 , Larry, Item , widget-2 , 2 , 20
And I'd like to convert to:
Txn ID, Name , Qty, Gross, widget-1 Qty, widget-1 Gross, widget-2 Qt
+y, widget-2 Gross
010 , Moe , 1 , 20 , 1 , 15 ,
+ ,
020 , Larry, 3 , 35 , 1 , 15 , 2
+ , 20
Of course the actual files have more fields and I left out the quote marks and added spaces for legibility. And I actually don't care if the columns are not in a particular order, as well as they contain the correct data.
My strategy is to iterate down through the rows, adding unique column names (hash keys) and assigning an order (auto-incremented). Then as I iterate through the data, I can know which row to put the data in. Here's the code so far. It mostly works, but the final transaction is overwriting all the others, so I'm getting a spreadsheet with the same transaction multiple time. I also need to add the crucial Item data. I am planning to create new Item-specific headings to append to the data. Any help appreciated.
#!/usr/bin/perl
use strict;
use Text::CSV;
use Data::Dumper;
my $report_file = '/files/report.csv';
my $csv_file = '/files/custom.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 : $!";
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') {
+
# push (@records, \@temp) if @temp; EDITED: THIS LINE RESULTED I
+N OVERWRITTEN DATA
push (@records, [@temp]) if @temp; # THIS SEEMS TO WORK, S
+EE MY COMMENT BELOW
@temp = ();
while (($key, $value) = each %{$row}) {
${%column_order}{$key} = $ci++ unless (defined ${%column_o
+rder}{$key});
$temp["${%column_order}{$key}"] = $value;
+
}
} elsif (${$row}{'Type'} eq 'Shopping Cart Item') {
# more code to add append to first row
)
}
my $report_fh;
open ($report_fh, ">:encoding(utf8)", $report_file) or die "$report_fi
+le : $!";
for my $key (sort {$column_order{$a}<=>$column_order{$b}} keys %column
+_order) {
print $report_fh "$key" , ", ";
}
print $report_fh "\n";
for my $record (@records) {
print $report_fh join(", ",@$record), "\n";
}
print $report_fh "\n";
-
Are you posting in the right place? Check out Where do I post X? to know for sure.
-
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big>
<blockquote> <br /> <dd>
<dl> <dt> <em> <font>
<h1> <h2> <h3> <h4>
<h5> <h6> <hr /> <i>
<li> <nbsp> <ol> <p>
<small> <strike> <strong>
<sub> <sup> <table>
<td> <th> <tr> <tt>
<u> <ul>
-
Snippets of code should be wrapped in
<code> tags not
<pre> tags. In fact, <pre>
tags should generally be avoided. If they must
be used, extreme care should be
taken to ensure that their contents do not
have long lines (<70 chars), in order to prevent
horizontal scrolling (and possible janitor
intervention).
-
Want more info? How to link
or How to display code and escape characters
are good places to start.