Using Perl with MySQL - Learning MySQL - page 553

$sth->bind_columns(\$Animal_Name, \$Animal_Count);
# Open the file specified on the command line; if we can't open it,
# print an error message and stop.
open(OUTPUTFILE, ">$ARGV[0]")
or
die("Failed opening $ARGV[0]\n");
# Write header row with column names
print OUTPUTFILE "Name,Count\n";
# Iterate through the results and write them as comma-separated values
# to the output file
while($sth->fetchrow_arrayref())
{
print OUTPUTFILE "$Animal_Name,$Animal_Count\n";
}
$sth->finish();
$dbh->disconnect();
close(OUTPUTFILE);
If the data could contain a comma, the resulting file could be unusable. For example,
if we want to export names and telephone numbers in the format:
Name,Number
we'd have difficulty if the data in the MySQL database were allowed to have commas,
as it does here:
+-----------------+-----------------+
| Name | Number |
+-----------------+-----------------+
| Hamzeh Abdollah | [phone] |
| Bloggs, Fred | [phone] |
...
+-----------------+-----------------+
If we exported this data to a CSV file, we would have:
Hamzeh Abdollah,[phone]
Bloggs, Fred,[phone]
The spreadsheet program would take the second row to have the name “Bloggs” and
the telephone number “ Fred”. To avoid this problem, we can enclose the data in double
quotes when writing it out:
print OUTPUTFILE "\"$Name\",\"$Count\"\n";
Note that since the text to be written to file is already enclosed in double quotes, we've
escaped (placed a backslash symbol before) the quotes surrounding the data. The ex-
ported data would be:
"Hamzeh Abdollah","[phone]"
"Bloggs, Fred","[phone]"