Querying a CSV File with SQL (Perl Cookbook, 2nd Edition)

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

Copyright © 2003 O'Reilly & Associates. All rights reserved.