DBD::CSV::prepare() problems

[polelo]

I use perl DBD::CSV to process and correlate information contained in several csv files received from an external computer,on a daily interval. The incoming file names are case sentive for uniqueness. On MS Windows platforms the DBD::CSV::prepare() command identifies each file correctly and processes it without dying/exiting. However when the same processing script is ported to UNIX/LINUX O/S it reports that the files cannot be found in the specied OS path; the file names are exactly the same as listed in the MS Windows O/S.

On looking at one of the error message (DBD::CSV::st execute failed: Cannot open /in/17_trx_22aug2012_0405.csv:) I noticed that the file names,e.g "17_trx_22aug2012_0405" appear(s) in lowercase on the error report, instead of "17_Trx_22Aug2012_0405", which is how it was associated with a tablename before the prepare() function.

Your help will be much appreciated on the following questions: 1) Is it possible that the prepare() method is converting each file name to lowercase, and this might not be a problem on Windows, but will be on UNIX/LINUX O/S? 2) How can I instruct the prepare() not to lowercase the literals of the DBD::CSV table association? 3) How can I view the prepared SQL (after the prepare() function)?

[Tux]

1. SQL::Statement deals with table-names case-insensitive.
2. Show us how you connect to the "database" and how you specify file/table names
3. Printing a prepared statement should be doable with say $sth->{Statement};
4. We need to know what versions you use for DBI, Text::CSV_XS, SQL::Statement, and DBD::CSV

[polelo]

Dear Tux

Thanks for your reply

this is how I connect to the CSV database

use DBI;
...

#MS Windows path to files
my $fpath="G:\\in";
#LINUX server paths
#my $fpath="/users/excel/in";

my $csvfile="Trx_24Aug2012_0409.csv";
my $cellh = DBI->connect("DBI:CSV:f_dir=$fpath;csv_eol=\n;");
    
#associate csv file with tablename
my $fname=$fpath.'/'.$csvfile;
$cellh->{'csv_tables'}->{'CELLS'} = {'file' =>$fname};
    
my $sql="select * from CELLS";
my $stcellh =  $cellh->prepare($sql) or die "$DBI::errstr\n";
      
$stcellh->execute() or die "$DBI::errstr\n";
my $colunmcount = $stcellh->{NUM_OF_FIELDS};
print "$colunmcount\n\n";
$stcellh->finish();

Thanks again

Polelo

[Tux]

Because you use the file => $filename approach, you do not use the case-insensitiveness from SQL::Statement.

I do not think you should define csv_eol

When on Windows *and* other OS's, do not use $dir."/".$file, but use File::Spec.

When in doubt about casing, use something like

chdir $f_path;
foreach my $file_name (glob "*.*") {
    lc $file_name eq lc $csvfile or next;
    $csv_file = $file_name;
    last;
    }

my $dbh = DBI->connect ("dbi:CSV:", undef, undef, {
    f_dir      => $f_path,
    f_ext      => ".csv/r",
    f_encoding => "utf-8",
    f_schema   => undef,
    RaiseError => 1,
    PrintError => 1,
    });
$cellh->{csv_tables}{cells} = { file => $csv_file };

my $sth = $sbh->prepare ("select * from cells");
$sth->execute;
print "fields: @{[@{$sth->{NAME_lc}}]}\n";

untested, but should work.

[remiah]

Hello Polelo


I tried with my FreeBSD but it says nothing for filename. No error message, no warnings.

#!/usr/bin/perl
use strict;
use warnings;
use DBI;

my $dsn="dbi:CSV:";

my $dbh=DBI->connect($dsn, "","",{
        f_encoding       => "utf8",
        f_dir            => "./",
})or die DBI->errstr;

$dbh->{csv_tables}->{t1} = {
        'file' => '17_Trx_22Aug2012_0405.csv',
        'eol' => "\n",
};

my $sth;
$sth=$dbh->prepare("select * from t1");
$sth->execute;
while( my $r = $sth->fetchrow_hashref){
        print "$r->{item1},$r->{item2}\n";
}
$sth->finish;
$dbh->disconnect;

#print version
print "perl version =$]\n";
for (qw/DBI Text::CSV_XS SQL::Statement DBD::CSV/){
        print "$_ ==>".$_->VERSION."\n";
}

versions of mine. How is yours?


perl version =5.012002
DBI ==>1.615
Text::CSV_XS ==>0.85
SQL::Statement ==>1.33
DBD::CSV ==>0.35