reading files with DBD::CSV

data67 has asked for the wisdom of the Perl Monks concerning the following question:

Hi all, i recently found out that i can use DBI's DBD::CSV component to access a text file much like a tabl in a database (with some notable differences). Now i am useg this code below to read a file BUT everytime i run the script it just returns the first line and ignores the remaining.

The file format of the data file is (delimited with "!"s):

1018884184639!1!2!3!4!5!6!7!8!9!10!11!12!http://www.perlmonks.com!Fri 
+Mar 29 10:32:44 2002!
1017939028902!1!2!3!4!5!6!7!8!9!10!11!12!http://perlmonks.com!Fri Mar 
+29 10:32:44 2002!
1017939144004!1!2!3!4!5!6!7!8!9!10!11!12!http://perlmonks.com!Fri Mar 
+29 10:32:44 2002!
[download]

And here is what my code looks like so far:

use DBI;


    $dbh = DBI->connect("DBI:CSV:f_dir=/export/home/devtools")
        or die "Cannot connect: " . $DBI::errstr;


    # reading "devtools.data" as a table:
    $dbh = DBI->connect(qq{DBI:CSV:csv_sep_char=\\!});
    $dbh->{'csv_tables'}->{'devtools'} = { 'file' => 'devtools.data'};
    $dbh->{csv_tables}->{'devtools'}->{skip_rows} = 0;
    $dbh->{csv_tables}->{'devtools'}->{col_names} =
        [qw(id pp midd fos db dbt dlang fil dtools stools scm tt dest 
+url lastupdate )];

    $sth = $dbh->prepare("SELECT id, url FROM devtools");
    $sth->execute() or die "Cannot execute: " . $sth->errstr();

#DBI->trace(1);


while ( ( $id, $url) = $sth->fetchrow_array) {
        print "ID is - $id , and url is - $url\n";
}

$sth->finish();
[download]

And this returns (just one record???):

ID is - 1018884184639 , and url is - http://www.perlmonks.com
[download]

Why is it not going through the whole file? WHat am i not doing right.
Thanks

Comment on reading files with DBD::CSV Select or Download Code

Replies are listed 'Best First'.
(jeffa) Re: reading files with DBD::CSV by jeffa (Bishop) on Jun 14, 2002 at 15:41 UTC
That was a hard bug to find! ;) You are connecting twice - remove the second one and change the first to: `my $dbh = DBI->connect( "DBI:CSV:f_dir=/export/home/devtools;csv_eol=\n;csv_sep_char=!;", {RaiseError=>1}, );` [download] The important part is setting csv_eol to a newline. The RaiseError part is not necessary, but it prevents you from having to check each and every DB transaction - so you can change this: `$sth->execute() or die "Cannot execute: " . $sth->errstr();` [download] to simply this: `$sth->execute();` [download] And thanks, because your code showed me how to use a csv file with a 'dot extension' (that is, "foo.csv"). I was under the impression that you could not do that. Oh, and: `use strict; # please ;) use DBI; use File::Basename; my $dir = '.'; my $file = 'simple.csv'; my $table = (fileparse($file,'.csv'))[0]; my $cols = [qw(foo bar baz)]; 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, };` [download] is a MRWTDI (more robust way to do it). jeffa L-LL-L--L-LL-L--L-LL-L-- -R--R-RR-R--R-RR-R--R-RR B--B--B--B--B--B--B--B-- H---H---H---H---H---H--- (the triplet paradiddle with high-hat)	[reply] [d/l] [select]
Re: (jeffa) Re: reading files with DBD::CSV by data67 (Monk) on Jun 14, 2002 at 16:09 UTC
You are most welcome! By the way, with DBD::CSV you can work with flat-file databases as if they were real tables. So this means that you can use the power/ease of SQL in these. The eception here is that that one of the limitations of DBD::CSV is that it does not allow table "joins" and therefore sub-queries can be used. And yes, you were right. I was connecting twice to the file!! Thanks	[reply]
Re: reading files with DBD::CSV by SarahM (Monk) on Jun 14, 2002 at 15:16 UTC
Your code works just fine for me...I'm running ActivePerl 5.6.1 build 631 on Win 2K. Double check that you are working on the right file...but other then that I'm not sure what to tell you.	[reply]
Re: reading files with DBD::CSV by data67 (Monk) on Jun 14, 2002 at 16:25 UTC
I am not sure but does anyone know if sub-queries work in DBD::CSV? I mean that i have to interdependent flat-files that i want to use DBD::CSV on. I know for a fact that joins dont work. is there another way to for with two interdependent files together?	[reply]