Beefy Boxes and Bandwidth Generously Provided by pair Networks
XP is just a number

DBD::CSV::prepare() problems

by polelo (Initiate)
on Aug 24, 2012 at 12:50 UTC ( #989524=perlquestion: print w/replies, xml ) Need Help??
polelo has asked for the wisdom of the Perl Monks concerning the following question:

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)?

Replies are listed 'Best First'.
Re: DBD::CSV::prepare() problems
by Tux (Abbot) on Aug 24, 2012 at 13:13 UTC
    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

    Enjoy, Have FUN! H.Merijn

      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


        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.

        Enjoy, Have FUN! H.Merijn
        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

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://989524]
Approved by moritz
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others examining the Monastery: (6)
As of 2017-12-12 16:49 GMT
Find Nodes?
    Voting Booth?
    What programming language do you hate the most?

    Results (335 votes). Check out past polls.