Learning MySQL

Importing and Exporting Data

From time to time, you may need to transfer data into the database from external sources, or to generate data in a format that other applications can use. A common file format for this is the comma-separated values (CSV) format discussed in Loading Data from Comma-Delimited Files” in Chapter 8. Data import and export is one of the areas in which Perl is very strong, and programs in Perl can read and write data in a large number of formats. For example, you can generate plain text, HTML, XML, or Rich Text Format (RTF) documents. RTF documents are more complex but can contain formatting instructions that most word processors understand. There are even Perl modules to process binary (nontext) formats, such as the Microsoft Excel spreadsheet file format.

Earlier, in Binding Variables to a Query,” we explained how to import data from a CSV file. Let’s now look at an example to export data from our Animals database to a CSV file. All we need to do is to use the print statement to write to the output file, with the data separated by a comma, as shown in Example 17-6.

Example 17-6. Perl script to export data from the Animals database, using binding

#!/usr/bin/perl
use DBI;
use strict;

# If the user hasn't provided any command-line arguments, provide a
# helpful error message.
if(@ARGV!=1)
{
    die("Syntax: $0 [Output file]\n");
}

my $DB_Database="AnimalDB";
my $DB_Username="root";
my $DB_Password="the_mysql_root_password"; my $dbh=DBI->connect( "DBI:mysql:host=localhost;database=$DB_Database", ...