Re^2: CSV files: Searching, manipulating data, appending and saving.

Looks like exactly what I'm trying to do. The problem is, I'm having trouble adapting it. To be honest, I'm trying to follow what the commands are doing. It appears if I create the file it's trying to manipulate and run it as is, I just get errors. If possible, could you do a play by play? The POD starts making sense, then in their example I see this:

  $sth = $dbh->prepare("SELECT foo, bar FROM table WHERE baz=?");

  $sth->execute( $baz );

  while ( @row = $sth->fetchrow_array ) {
    print "@row\n";
  }
[download]

*puts on dunce cap* Where did $baz come from? Was it being assigned in the previous line after the WHERE statement?

**I should mention that I'm looking at the DBI example you gave, although that was probably already obvious.**

Thanks again!

Comment on Re^2: CSV files: Searching, manipulating data, appending and saving. Download Code

Replies are listed 'Best First'.
Re^3: CSV files: Searching, manipulating data, appending and saving. by SpacemanSpiff (Sexton) on Oct 13, 2005 at 06:09 UTC
Ok, it would appear that I've got it reading the CSV finally. I'll work on the manipulating and saving bit in the morning.	[reply]
Re^3: CSV files: Searching, manipulating data, appending and saving. by SpacemanSpiff (Sexton) on Oct 14, 2005 at 01:50 UTC
Ok, I've got the command returning the contents of the whole CSV file. The question now is how do I locate, one specific row and affect it's data? Here's what I have: #!/usr/bin/perl -w use strict; use DBI; use File::Basename; my $dir = '.'; my $file = 'memberlist.csv'; my $table = (fileparse($file,'.csv'))[0]; my $cols = [qw(Username Email Posts EmailSet Group Unknown Date Times +tamp Password MD5)]; my $sep = ','; my $dbh = DBI->connect( "DBI:CSV:f_dir=$dir;csv_eol=\n;csv_sep_char=$sep;", {RaiseError=>1},); $dbh->{csv_tables}->{$table} = { file => $file, col_names => $cols,}; my $sth = $dbh->prepare("SELECT Username, Email, Posts, Timestamp +FROM memberlist"); $sth->execute() or die "Cannot execute: " . $sth->errstr(); while ( ( my $Username, my $Email, my $Posts, my $Timestamp) = $st +h->fetchrow_array) { print "$Username, $Email, $Posts, $Timestamp\n"; } $sth->finish(); [download] Now I tried an 'if' command within the 'while' statement to match up an email, then increment it's '$post' variable once it hit a specified address (a test of the function I need to do) and all it did was increment everyone's post count. At this point, I wonder if I'm on the right track. Should I even be pulling the value into the script, or should I be looking at an 'update' command of some sort? I've read elsewhere that someone just read each line into an array and went from there, but that sounds like a lot of overhead to read it each time. Likewise, I'd be concerned about holding the whole table in memory in the event of an error during the process.	[reply] [d/l]
Re^4: CSV files: Searching, manipulating data, appending and saving. by GrandFather (Saint) on Oct 20, 2005 at 10:10 UTC
Try this version: use warnings; use DBI; use File::Basename; my $dir = '.'; my $file = 'noname.csv'; my $table = (fileparse($file,'.csv'))[0]; my $cols = [qw(Username Email Posts Timestamp)]; my $sep = ','; open outFile, '>', $file; print outFile <<SampleData; Username, Email, Posts, Timestamp ed,ed\@there,11,10:23:01 bill,bill\@there,5,10:23:02 SampleData close outFile; my $dbh = DBI->connect( "DBI:CSV:f_dir=$dir;csv_eol=\n;csv_sep_char=$sep;", {RaiseError=>1},); $dbh->{csv_tables}->{$table} = { file => $file, col_names => $cols, }; my $sth = $dbh->prepare("SELECT Username, Email, Posts, Timestamp FROM + noname"); $sth->execute() or die "Cannot execute: " . $sth->errstr(); my %newPosts; while ((my $Username, my $Email, my $Posts, my $Timestamp) = $sth->fet +chrow_array ()) { next if ! defined $Timestamp; print "$Username, $Email, $Posts, $Timestamp\n"; $newPosts{$Username} = ++$Posts; } for my $Username (keys %newPosts) { my $Posts = $newPosts{$Username}; $sth = $dbh->prepare("UPDATE noname SET Posts = $Posts WHERE Usernam +e = '$Username'"); $sth->execute() or die "Cannot execute: " . $sth->errstr(); } $sth = $dbh->prepare("SELECT Username, Email, Posts, Timestamp FROM no +name"); $sth->execute() or die "Cannot execute: " . $sth->errstr(); while ((my $Username, my $Email, my $Posts, my $Timestamp) = $sth->fet +chrow_array ()) { next if ! defined $Timestamp; print "$Username, $Email, $Posts, $Timestamp\n"; } $sth->finish(); [download] Prints: `Username, Email, Posts, Timestamp ed, ed@there, 11, 10:23:01 bill, bill@there, 5, 10:23:02 Username, Email, 1, Timestamp ed, ed@there, 12, 10:23:01 bill, bill@there, 6, 10:23:02` [download] Perl is Huffman encoded by design.	[reply] [d/l] [select]