This is part of The Pile, a partial archive of some open source mailing lists and newsgroups.
To: "DBI users" <dbi-users@perl.org>
From: "Ron Savage" <ron@savage.net.au>
Subject: Fw: dealing with csv files
Date: Sun, 18 Mar 2001 16:06:02 +1100
Stacy
DBI 1.14
Text::CSV_XS 0.22
SQL::Statement 0.1017
DBD::CSV 0.1025
The latter 3 were installed a few minutes ago :-).
weather.dat:
-----><8-----
Date&Time,MaxWSpd,AvgWSpd,WindDir,AirPres,RainBkt,RelHumd,OutTemp,InsTemp,MsrTemp,FocTemp,XtrTemp
26-DEC-1999 00:00:00, -1, -2, -3, -4, -5, -6, -7, -8, -9, -10, -11
-----><8-----
I changed all the 9s so I could see which column was being returned.
x.pl:
-----><8-----
#!/usr/local/bin/perl -w
use DBI;
$dbh = DBI->connect("DBI:CSV:f_dir=d:/temp") or die "Cannot connect: " . $DBI::errstr;
$dbh->{'csv_tables'}->{'weather'} = { 'file' => 'weather'};
my $sql = "SELECT MaxWSpd, WindDir FROM weather WHERE AvgWSpd < 10.00";
my $sth = $dbh->prepare($sql) or die "Cannot prepare: " . $dbh->errstr();
$sth->execute() or die "Cannot execute: " . $sth->errstr();
my($MaxWSpd, $WindDir, $AvgWSpd);
$sth->bind_columns(\$MaxWSpd, \$WindDir);
while ($sth->fetch) {
print("Found result row: $MaxWSpd, $WindDir \n");
}
$sth->finish();
$dbh->disconnect();
-----><8-----
Same as yours.
Ouput:
-----><8-----
Found result row: -1, -3
-----><8-----
===
To: "Stacy Mader" <Stacy.Mader@atnf.csiro.au>
From: "Jeff Zucker" <jeff@vpservices.com>
Subject: Re: dealing with csv files
Date: 18 Mar 2001 20:43:22 -0800
Stacy Mader wrote:
>
> Hi all,
>
> In a file called weather (in /tmp/weather_db) I have some date files in
> the following format:
>
> Date&Time,MaxWSpd,AvgWSpd,WindDir,AirPres,RainBkt,RelHumd,OutTemp,InsTemp,MsrTemp,FocTemp,XtrTemp
The column name "Date&Time" is not a valid SQL column name. Column
names can not contain anything other than letters, numbers, and
underscores.
===
To: Jeff Zucker <jeff@vpservices.com>
From: Stacy Mader <Stacy.Mader@atnf.csiro.au>
Subject: Re: dealing with csv files
Date: Mon, 19 Mar 2001 17:14:48 +1100 (EST)
Even if I rename Date&Time to DateTime, my perl code does not
return any columns!
===
To: Warren Pollans <wpollans@verio.net>
From: Stacy Mader <Stacy.Mader@atnf.csiro.au>
Subject: Re: dealing with csv files
Date: Tue, 20 Mar 2001 12:52:47 +1100
Hi all,
I appear to have solved my problem with dealing with CSV files: here is my solution code:
#!/usr/local/bin/perl -w
use DBI;
($infile) = @ARGV;
my $dbh = DBI->connect("DBI:CSV:");
$dbh->{'csv_tables'}->{'weather'} =
{
'eol' => "\n",
'file' => "$infile",
'col_names' => ["DateTime", "MaxWSpd",
"AvgWSpd", "WindDir",
"AirPres", "RainBkt",
"RelHumd", "OutTemp",
"InsTemp", "MsrTemp",
"FocTemp", "XtrTemp"],
};
$sth = $dbh->prepare("SELECT DateTime,MaxWSpd FROM weather WHERE MaxWSpd > 35");
$sth->execute;
while (@row = $sth->fetchrow) {
print "@row\n";
}
Defining the columns did the trick.
===
To: DBI users <dbi-users@perl.org>
From: Warren Pollans <cottage1@gate.net>
Subject: Re: Fw: dealing with csv files
Date: Mon, 19 Mar 2001 19:59:33 -0700
Ron Savage wrote:
>
> Stacy
>
> DBI 1.14
> Text::CSV_XS 0.22
> SQL::Statement 0.1017
> DBD::CSV 0.1025
>
> The latter 3 were installed a few minutes ago :-).
>
> weather.dat:
> -----><8-----
> Date&Time,MaxWSpd,AvgWSpd,WindDir,AirPres,RainBkt,RelHumd,OutTemp,InsTemp,MsrTemp,FocTemp,XtrTemp
> 26-DEC-1999 00:00:00, -1, -2, -3, -4, -5, -6, -7, -8, -9, -10, -11
> -----><8-----
>
> I changed all the 9s so I could see which column was being returned.
>
> x.pl:
> -----><8-----
> #!/usr/local/bin/perl -w
> use DBI;
>
> $dbh = DBI->connect("DBI:CSV:f_dir=d:/temp") or die "Cannot connect: " . $DBI::errstr;
> $dbh->{'csv_tables'}->{'weather'} = { 'file' => 'weather'};
> my $sql = "SELECT MaxWSpd, WindDir FROM weather WHERE AvgWSpd < 10.00";
>
> my $sth = $dbh->prepare($sql) or die "Cannot prepare: " . $dbh->errstr();
>
> $sth->execute() or die "Cannot execute: " . $sth->errstr();
Hello All,
I was able to get the script to work by explicitly adding a record
separator ('^M' in vi) at the end of each line - didn't have to define
the column names. I got here by creating a table and seeing what was in
it. I had a large tab-delimited file (from star-office spreadsheet)
which for which Stacy's script produced the same no-output result -
adding '^M' to the end of each line fixed that problem.
Stacy, it may be that explicitly defining '\n' as your EOL char is what
fixed your problem.
===
To: <wpollans@bigfoot.com>
From: Stacy Mader <Stacy.Mader@atnf.csiro.au>
Subject: Re: Fw: dealing with csv files
Date: Tue, 20 Mar 2001 14:05:23 +1100 (EST)
Yes, you are right! I removed 'col_names' and the script still
worked.
===
To: DBI users <dbi-users@perl.org>
From: Colin Meyer <cmeyer@helvella.org>
Subject: Re: Fw: dealing with csv files
Date: Tue, 20 Mar 2001 14:35:45 -0800
On Mon, Mar 19, 2001 at 07:59:33PM -0700, Warren Pollans wrote:
> Ron Savage wrote:
>
> I was able to get the script to work by explicitly adding a record
> separator ('^M' in vi) at the end of each line - didn't have to define
> the column names. I got here by creating a table and seeing what was in
> it. I had a large tab-delimited file (from star-office spreadsheet)
> which for which Stacy's script produced the same no-output result -
> adding '^M' to the end of each line fixed that problem.
>
> Stacy, it may be that explicitly defining '\n' as your EOL char is what
> fixed your problem.
DBD::CSV can read files with different record separators (and different
column separators also). The csv_eol attribute of the database handle
($dbh) contains the characters that separate each record from the next
one. csv_eol defaults to "\015\012" (aka "\r\n") which is the msdos
style line separator. For unix style eol, use "\012".
# this should do the trick:
$dbh->{csv_eol} = "\012";
# csv_eol can also be specified as part of the dsn:
$csv_dsn = "dbi:CSV:f_dir=/path/to/csvfiles;csv_eol=\012";
$dbh = DBI->connect($dsn) or die $DBI::errstr;
For more details, see the DBD::CSV manpage.
===