Recipe 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