MySQL Cookbook, 2nd Edition

Exchanging Data Between MySQL and Microsoft Excel

Problem

You want to exchange information between MySQL and Excel.

Solution

Your programming language might provide modules to make this task easier. For example, there are Perl modules that read and write Excel spreadsheet files. You can use them to construct your own 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, you can read and write Excel spreadsheets from within Perl scripts by installing a few modules:

• Spreadsheet::ParseExcel::Simple provides an easy-to-use interface for reading Excel spreadsheets.

• Spreadsheet::WriteExcel::Simple 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 also need to install as prerequisites.) After installing the modules, use these commands to read their documentation:

%perldoc Spreadsheet::ParseExcel::Simple
% perldoc Spreadsheet::WriteExcel::Simple

These modules make it relatively easy to write a couple of short scripts for converting spreadsheets to and from tab-delimited file format. Combined with techniques for importing and exporting data into and out of MySQL, these scripts can help you move spreadsheet contents 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 ...