لطفا منتظر باشید ...
 |  |
14.17. Querying a CSV File with SQL
14.17.1. Problem
You want to use SQL to insert, delete,
or retrieve data from a comma-separated values (CSV) file.
14.17.2. Solution
Use the
DBD::CSV module from CPAN:use DBI;
$dbh = DBI->connect("dbi:CSV:f_dir=/home/gnat/payroll", ", ",
{ AutoCommit => 1, RaiseError => 1 });
$dbh->do("UPDATE salaries SET salary = salary * 2 WHERE name = 'Nat'");
$sth = $dbh->prepare("SELECT name,salary FROM salaries WHERE name = 'Nat'");
$sth->execute( );
while (@row = $sth->fetchrow_array) {
# ...
}
$sth->finish( );
$dbh->disconnect( );
14.17.3. Discussion
A "table" in CSV terms is a file (the table name becomes the
filename). The tables are kept in the directory specified by the
f_dir parameter in the connect
method call. The DBD::CSV module supports CREATE
and DROP to make and destroy tables:$dbh->do("CREATE TABLE salaries (salary FLOAT, name CHAR(20))");
Valid column types are: TINYINT, BIGINT, LONGVARBINARY, VARBINARY,
BINARY, LONGVARCHAR, CHAR, NUMERIC, DECIMAL, INTEGER, SMALLINT,
FLOAT, REAL, and DOUBLE.When you access a table, the DBD::CSV module locks the corresponding
file with the flock(2) syscall. If
flock(2) isn't supported on the filesystem
containing the CSV file, two processes will be able to access the
file at the same time, possibly leading to incorrect results or lost
data.If you're reading or writing an Excel CSV file, you need to tell the
DBD::CSV module that the value separator is actually a semicolon:$dbh = DBI->connect('dbi:CSV:f_dir=/home/gnat/payroll;csv_sep_char=\;');
We need to quote the semicolon to prevent connect
from thinking it's separating csv_sep_char= from
another connection attribute. We use single quotes rather than double
quotes to avoid having to backslash the backslash:$dbh = DBI->connect("dbi:CSV:f_dir=/home/gnat/payroll;csv_sep_char=\\;");
14.17.4. See Also
The documentation for the CPAN module DBD::CSV; Recipe 1.20; Recipe 14.8
|  | |
| 14.16. Viewing Data One Page at a Time |  | 14.18. Using SQL Without a Database Server |
Copyright © 2003 O'Reilly & Associates. All rights reserved.
