Importing and Exporting Data - MySQL - page 396

11.8. Exchanging Data Between MySQL and Microsoft
Excel
Problem
You want to exchange information between MySQL and Excel.
Solution
Your programming language might provide library routines to make this task easier.
For example, you can use Perl modules that read and write Excel spreadsheet files to
construct data transfer utilities.
Discussion
If you need to transfer Excel files into MySQL, check around for modules that let you
do this from your chosen programming language. For example, the following modules
enable reading and writing Excel spreadsheets in Perl scripts:
• Spreadsheet::ParseExcel::Simple provides an easy-to-use interface for reading Excel
spreadsheets. (Because Microsoft occasionally revises spreadsheet formats, you
might need to save a spreadsheet in an older format so that this module can read
it.)
• Excel::Writer::XLSX enables you to create files in Excel spreadsheet format.
These Excel modules are available from the Perl CPAN. (They're actually frontends to
other modules, which you must also install as prerequisites.) After installing the mod‐
ules, use these commands to read their documentation:
% perldoc Spreadsheet::ParseExcel::Simple
% perldoc Excel::Writer::XLSX
These modules make it relatively easy to write short scripts for converting spreadsheets
to and from tab-delimited file format. Combined with techniques for importing and
exporting data to and from MySQL, these scripts can help you move spreadsheet con‐
tents to MySQL tables and vice versa. Use them as is, or adapt them to suit your own
purposes.
The following script, from_excel.pl , reads an Excel spreadsheet and converts it to tab-
delimited format:
#!/usr/bin/perl
# from_excel.pl: Read Excel spreadsheet, write tab-delimited,
# linefeed-terminated output to the standard output.
use strict ;