 |  |
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.