in reply to Re^2: CSV files: Searching, manipulating data, appending and saving.
in thread CSV files: Searching, manipulating data, appending and saving.

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.
  • Comment on Re^3: CSV files: Searching, manipulating data, appending and saving.
  • Download Code

Replies are listed 'Best First'.
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]

In Section Seekers of Perl Wisdom