DBD::CSV forgets a column and a row?

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

Hello monks...

Update: merlyn is right about the missing row problem. I should have had that second cup of coffee before I posted. I hope the missing column problem is that easy to solve.
Update:changed typo: "=" to "==" in if statement.

DBD::CSV has mostly done the right thing for me, but I ran into a couple of issues with a particular set of data. I have a file and I want to print the results of fetchrow_array if a particular row matches any element of a different array, @chk. Here's some test input data, a file called "DUDS.csv":

ADT,,111111111,,*SOH,5.00,,,,,,,,#
ADS,,222222222,,*SOH,1312.00,,,,,,,,#
ADS,,111111111,,*FWT,14.00,,,,,,,,#
ADS,,222222222,,*UNLF,99.75,,,,,,,,#
[download]

Here's a runnable test script that shows my problems:


use warnings; 
use strict;
use DBI; 

open (OUT, ">file.txt"); 

my @chk = (111111111,222222222);

my    $dbh = DBI->connect(qq{DBI:CSV:csv_sep_char=\\,});
$dbh->{'csv_tables'}->{'comp'} = { 'file' => 'DUDS.csv'};

my $sql;
my @row ; 
my $sth = $dbh->prepare("SELECT * from comp");
$sth->execute();

while (my @row = $sth->fetchrow_array) { 
    foreach my $chk(@chk) {
        if ( $chk == $row[2]) {
        print OUT "@row\n";
print OUT "$row[0],$row[1],$row[2],$row[3],$row[4],$row[5],$row[6],$ro
+w[7]\n\n";
        last; 
        }
    }
    }
[download]

The output from this script is below. I'm confused because: I think the first row of the input file should be represented; and because the numeric value in the sixth column has disappeared. I don't think this is right, but I don't see anything wrong with my code (yet!)

ADS  222222222  *SOH         #
ADS,,222222222,,*SOH,,,

ADS  111111111  *FWT         #
ADS,,111111111,,*FWT,,,

ADS  222222222  *UNLF         #
ADS,,222222222,,*UNLF,,,
[download]

Comment on DBD::CSV forgets a column and a row? Select or Download Code

Replies are listed 'Best First'.
•Re: DBD::CSV forgets a column and a row? by merlyn (Sage) on Aug 16, 2004 at 14:52 UTC
I don't see the column labels in your file. That is, the first line of a CSV file is special, providing the names for the columns. If they are missing, that's where your first line of data went - it's being interpreted as the column labels. -- Randal L. Schwartz, Perl hacker Be sure to read my standard disclaimer if this is a reply.	[reply]
Re: DBD::CSV forgets a column and a row? by jZed (Prior) on Aug 16, 2004 at 15:38 UTC
`if ( $chk = $row[2]) {` [download] I think you mean ==, not =. And merlyn is correct that, by default, DBD::CSV expects the first row in the data file to be the column names. You can satisfy that by either adding the column names to the top of the file, OR, by setting the col_names flag. If the col_names flag is unset, the module will eat the first row and expect it to be column names. If the col_names flag is set, the module will treat the first row as data. For example: `$dbh->{'csv_tables'}->{'comp'} = { 'file' => 'DUDS.csv' , eol => "\n" , col_names => [qw( c1 c2 c3 c4 c5 c6 c7 c8 c9 c10 c11 c12 c13 c14 )] };` [download]	[reply] [d/l] [select]
Re^2: DBD::CSV forgets a column and a row? by McMahon (Chaplain) on Aug 16, 2004 at 15:52 UTC
Thanks for the col_names tip... I'm still at a loss to explain the missing column in the output, though...	[reply]
Re^3: DBD::CSV forgets a column and a row? by jZed (Prior) on Aug 16, 2004 at 15:54 UTC
I tried your script and I don't get any missing columns when I add column names and change the == to an =. Remember, you have to name all the columns (including the ones that have no data represented by ",,," in your example data file).	[reply]
Re^4: DBD::CSV forgets a column and a row? by McMahon (Chaplain) on Aug 16, 2004 at 15:59 UTC
Re: DBD::CSV forgets a column and a row? by ikegami (Patriarch) on Aug 16, 2004 at 15:40 UTC
I don't know why a column isn't being displayed, but I spotted an abnormality. Shouldn't `if ($chk = $row[2]) {` be `if ($chk == $row[2]) {`?	[reply] [d/l] [select]