Re^2: Combine data in table

This is what I did:

# Connect to the database
my $dbh = DBI->connect("DBI:CSV:f_dir=.;csv_eol=\n;");

# Associate our csv file with the table name 'results'
$dbh->{'csv_tables'}->{'results'} = { 'file' => "file.csv"};

my $row = "IP,Partition,PartitionSize,PartitionFree";
my %ips;
foreach my $row (@$row){
  $ips{ $row->{colIP} }->{ colPartitionSize } += $row->{colPartitionSi
+ze};
  $ips{ $row->{colIP} }->{ colPartitionFree } += $row->{colPartitionFr
+ee};
}
print Dumper \%ips;
[download]

But I am getting:

Can't use string ("IP,Partition,PartitionSize,Parti") as an ARRAY ref 
+while "strict refs" in use at ./psfile.pl line 39.
[download]

Comment on Re^2: Combine data in table Select or Download Code

Replies are listed 'Best First'.
Re^3: Combine data in table by davidrw (Prior) on Aug 07, 2005 at 18:09 UTC
I would recommend using the "SELECT ... GROUP BY" SQL statement -- it's very conscise and exactly what you want, and leverages the fact you're using DBD/CSV .. As for your code in this post, though, you have `my $row = "IP,Partition,PartitionSize,PartitionFree";` which just sets some string as the value for `$row` .. you need it to be all the data as an array of hashrefs .. `my $rows = $dbh->selectall_arrayref("SELECT * FROM your_table", {Slice +=>{}}, ); my %ips; foreach my $row (@$rows){ $ips{ $row->{colIP} }->{ colPartitionSize } += $row->{colPartitionSi +ze}; $ips{ $row->{colIP} }->{ colPartitionFree } += $row->{colPartitionFr +ee}; } print Dumper \%ips;` [download] (Note that this is inefficient, as well -- would be better to use a `$sth` and fetch one row at a time instead of sucking in all the rows at once)	[reply] [d/l] [select]
Re^4: Combine data in table by DrAxeman (Scribe) on Aug 07, 2005 at 19:10 UTC
I got the Perl loop method to work (mostly for the experience). But now I'm working on the SQL method. My code looks like: `# Connect to the database, (the directory containing our csv file(s)) my $dbh = DBI->connect("DBI:CSV:f_dir=.;csv_eol=\n;"); # Associate our csv file with the table name 'results' $dbh->{'csv_tables'}->{'results'} = { 'file' => "psinfooutputdiskfile. +csv"}; my @rows = $dbh->selectrow_arrayref("SELECT IP, SUM(PartitionSize), SU +M(PartitionFree) FROM results GROUP BY IP"); print ("@rows\n"); # Just here to view output` [download] I'm getting `SQL ERROR: Can't find table names in FROM clause! Execution ERROR: No command found!.` [download] Not sure what is wrong here. it I modify the SELECT statement to: `my @rows = $dbh->selectrow_arrayref("SELECT IP FROM results");` [download] It works. But if I try to include a GROUP BY or SUM I get the error.	[reply] [d/l] [select]
Re^5: Combine data in table by davidrw (Prior) on Aug 07, 2005 at 19:30 UTC
Note first that you probably want `my $rows = $dbh->selectall_arrayref( ... )` instead of `selectrow_arrayref()` are those column names right? should they be "colIP", "colPartitionSize", "colPartitionFree" instead? Besides that, the SQL looks fine .. do the statements "SELECT IP FROM results" and "SELECT IP FROM results GROUP BY IP" and "SELECT SUM(PartitionSize), SUM(PartitionFree) FROM results" work?	[reply] [d/l] [select]
Re^6: Combine data in table by DrAxeman (Scribe) on Aug 07, 2005 at 19:49 UTC
Re^7: Combine data in table by davidrw (Prior) on Aug 07, 2005 at 20:19 UTC
Some notes below your chosen depth have not been shown here
Re^4: Combine data in table by DrAxeman (Scribe) on Aug 07, 2005 at 23:25 UTC
Using this method, how would I print out %ips? I'd like data comming out to like the original data: `IP,PartitionFree,PartitionSize 1.2.3.4,23452,234322 2.3.4.5,99348,8666623` [download]	[reply] [d/l]
Re^5: Combine data in table by davidrw (Prior) on Aug 08, 2005 at 01:19 UTC
here's three different ways.. note that the second two make it trivial to output in a sorted manner (something like `sort keys %ips` instead of just `keys %ips`): `while( my ($ip, $h) = each %ips ){ printf "%s,%d,%d\n", $ip, $h->{colPartitionSize}, $h->{colPartitionF +ree}; } foreach my $ip ( keys %ips ){ my $h = $ips{$ip}; printf "%s,%d,%d\n", $ip, $h->{colPartitionSize}, $h->{colPartitionF +ree}; } printf "%s,%d,%d\n", $_, $ips{$_}->{colPartitionSize}, $ips{$_}->{colP +artitionFree} for keys %ips;` [download]	[reply] [d/l] [select]