Recipe 14.8 Saving Query Results to Excel or CSV

14.8.1 Problem

You want to query a relational database and create a file of the results so that another program or person can use them. The two common formats people want to get data in are CSV and Excel.

14.8.2 Solution

Use the CPAN module DBIx::Dump to dump the statement handle after the query:

use DBIx::Dump;
use DBI;

# ... connect to your database as normal
$sth = $dbh->prepare("SELECT ...");  # your query here
$sth->execute( );

$out = DBIx::Dump->new('format' => $FORMAT,   # excel or csv
                       'output' => $FILENAME, # file to save as
                       'sth'    => $sth);
$out->dump( );

14.8.3 Discussion

The CPAN module DBIx::Dump supports Excel and CSV file formats. It uses the CPAN module Spreadsheet::WriteExcel to write Excel files, and the CPAN module Text::CSV_XS to write CSV files.

The first row in the output files holds the column names. For example:

ID,NAME
1,Nat
2,Tom
4,Larry
5,Damian
6,Jon
7,Dan

14.8.4 See Also

The documentation for the CPAN modules DBIx::Dump, Spreadsheet::WriteExcel, and Text::CSV_XS; Recipe 14.17