DBD::CSV - missing first row?

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

Dear monks,

I'm desperately hoping you can help me with this problem. I'm new to the module, but am attempting to manipulate a CSV file with this module:

CSV file (file.csv):

"field1";"field2";"field3";"field4";"field5";"field6";"field7";
"5/7/2005 6:50:43 AM";1;1;0;"some text 1";"some text 5";"some text 9."
"5/7/2005 5:55:44 AM";1;1;0;"some text 2";"some text 6";"some text 10"
"5/7/2005 5:20:03 AM";1;1;0;"some text 3";"some text 7";"some text 11"
"5/7/2005 5:06:07 AM";1;1;0;"some text 4";"some text 8";"some text 12"
[download]

I'm using the following code to attemp to simply create a virtual SQL table with it, and then use a select statement to pull the data from the virtual table...

my $dbh = DBI->connect(qq{DBI:CSV:csv_sep_char=\\;});
$dbh->{'csv_tables'}->{'info'} = { 'file' => 'test.csv' } or die "Prob
+lem creating table";
my $sth = $dbh->prepare("select * from info") or die "Can't prepare st
+h: $dbh->errstr\n";
$sth->execute or die "Can't execute the query: $sth->errstr";
my $count = 0;
while (my @row = $sth->fetchrow_array) {
    print "Row: $count&nbsp;&nbsp;";
    print $row[0] . "&nbsp;&nbsp;";
    print $row[1] . "&nbsp;&nbsp;";
    print $row[2] . "&nbsp;&nbsp;";
    print $row[3] . "&nbsp;&nbsp;";
    print $row[4] . "&nbsp;&nbsp;";
    print $row[5] . "&nbsp;&nbsp;";
    print $row[6] . "&nbsp;&nbsp;";
    $count++;
}
$sth->finish;
[download]

However, I'm encountering the following error:
"Execution ERROR: Missing first row at c:/apache/Perl/site/lib/DBD/CSV.pm line 165, line 1. . DBD::CSV::st fetchrow_array failed: Attempt to fetch row from a Non-SELECT statement for statement ``select * from info'') at /apache/htdocs/test.cgi line 35."

I've tried a number of different things, but without any luck.

Could anyone advise me of what I'm doing wrong?

Thanks a ton!

Regards,
Stenyj

Comment on DBD::CSV - missing first row? Select or Download Code

Replies are listed 'Best First'.
Re: DBD::CSV - missing first row? by hossman (Prior) on May 07, 2005 at 06:22 UTC
I can't reproduce the exact error you described, but i can tell you that on my system, i had to set... `$dbh->{'csv_eol'} = "\n";` [download] ...because DBD::CSV seems to assume windows line terminators in your files, regardless of what `$/` is. I can imagine that in some version of DBD::CSV, that may come across as an error because it never finds the end of the first line.	[reply] [d/l] [select]
Re: DBD::CSV - missing first row? by Tanktalus (Canon) on May 07, 2005 at 14:10 UTC
What version of DBD::CSV are you using? Here, with perl 5.8.6 and DBD::CSV 0.21, I don't get the error, like hossman. However, it may be something as simple as older versions requiring a leading "#" on the first row to identify it as such, e.g., `#"field1";"field2";"field3";"field4";"field5";"field6";"field7";` [download]	[reply] [d/l]
Re^2: DBD::CSV - missing first row? by Stenyj (Beadle) on May 08, 2005 at 06:12 UTC
Thx for the feeback, but no luck so far. Running: DBI: 1.42 DBD:CSV: 0.22 Perl 5.6.1 New code I copied & pasted to attempt to get it working: #!c:/apache/perl/bin/perl.exe -wT BEGIN { $\| = 1; open (STDERR, ">&STDOUT"); print qq~Content-type: text/html\n\n~; } use CGI qw/:standard/; use strict; use Data::Dumper; use DBI; my $foo = new CGI; print $foo->header; # 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 'test2' $dbh->{'csv_tables'}->{'test2'} = { 'file' => 'test2.csv'}; # Output the name and contact field from each row my $sth = $dbh->prepare("SELECT * FROM test2 WHERE name LIKE 'G%'"); $sth->execute() or die "Can't execute the query: $sth->errstr"; while (my $row = $sth->fetchrow_hashref) { print("name = ", $row->{'Name'}, " contact = ", $row->{'Contact'} +. "\n"); } $sth->finish(); [download] CSV file that is being used for test2.csv: `"Name","Address","Floors","Donated last year","Contact" "Charlotte French Cakes","1179 Glenhuntly Rd",1,"Y","John" "Glenhuntly Pharmacy","1181 Glenhuntly Rd",1,"Y","Paul" "Dick Wicks Magnetic Pain Relief","1183-1185 Glenhuntly Rd",1,"Y","Geo +rge" "Gilmour's Shoes","1187 Glenhuntly Rd",1,"Y","Ringo"` [download] The error I'm getting is: Content-Type: text/html; charset=ISO-8859-1 Execution ERROR: Missing f +irst row at c:/apache/Perl/site/lib/DBD/CSV.pm line 165, line 1. . DB +D::CSV::st fetchrow_hashref failed: Attempt to fetch row from a Non-S +ELECT statement [for statement ``SELECT * FROM test2 WHERE name LIKE +'G%''']) at /apache/htdocs/test2.cgi line 26. [download] Any other suggestions? I appreciate your help/feedback guys... Stenyj	[reply] [d/l] [select]
Re^3: DBD::CSV - missing first row? by holli (Abbot) on May 08, 2005 at 06:36 UTC
Are you sure your testfile contains what you think? The only way I can reproduce the error is when test2.csv is present but empty. holli, /regexed monk/	[reply]
Re^4: DBD::CSV - missing first row? by Stenyj (Beadle) on May 08, 2005 at 07:33 UTC
Re^5: DBD::CSV - missing first row? by holli (Abbot) on May 08, 2005 at 07:38 UTC
Re: DBD::CSV - missing first row? by jZed (Prior) on May 09, 2005 at 00:37 UTC
I can't see anything in your code that should produce the error you are getting. Are you using the latest SQL::Statement (it's the SQL engine that DBD::CSV uses)? One possible culprit is non-printing characters in the file. I suggest you update your SQL::Statement if needed - latest is 1.14 and then try the same script but add in a section that uses SQL CREATE and INSERT to create and populate the table so that DBD::CSV both creates and parses it. If you continue to have problems, please email me directly (I'm the maintainer of DBD::CSV and SQL::Statement and my email's in the pod of those modules) '	[reply]