Exchanging Data Between MySQL and Microsoft Excel

10.40.1 Problem

You want to exchange information between MySQL and Excel.

10.40.2 Solution

Use utilities such as DBTools or MySQLFront. Or use Perl modules that read and write Excel spreadsheet files to construct your own data transfer utilities.

10.40.3 Discussion

One way to transfer Excel files into MySQL is to use the DBTools or MySQLFront utilities that were discussed in Recipe 10.39 for working with Access files. Both programs know how to read Excel files as well. But both are Windows-specific; for a more cross-platform solution that works for both Unix and Windows, 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 allows you to create files in Excel spreadsheet format.

These modules are available from the Perl CPAN. (They're actually frontends to other modules, which you'll 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 (shown below) 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 spreadsheet and converts it to tab-delimited format:

#! /usr/bin/perl -w
# from_excel.pl - read Excel spreadsheet, write tab-delimited,
# linefeed-terminated output to the standard output.

use strict;
use Spreadsheet::ParseExcel::Simple;

@ARGV or die "Usage: $0 excel-file
";

my $xls = Spreadsheet::ParseExcel::Simple->read ($ARGV[0]);
foreach my $sheet ($xls->sheets ( ))
{
 while ($sheet->has_data ( ))
 {
 my @data = $sheet->next_row ( );
 print join ("	", @data) . "
";
 }
}

exit (0);

The to_excel.pl script performs the converse operation of reading a tab-delimited file and writing it in Excel format:

#! /usr/bin/perl -w
# to_excel.pl - read tab-delimited, linefeed-terminated input, write
# Excel-format output to the standard output.

use strict;
use Spreadsheet::WriteExcel::Simple;

my $ss = Spreadsheet::WriteExcel::Simple->new ( );

while (<>) # read each row of input
{
 chomp;
 my @data = split (/	/, $_, 10000); # split, preserving all fields
 $ss->write_row (@data); # write row to the spreadsheet
}

print $ss->data ( ); # write the spreadsheet

exit (0);

to_excel.pl assumes input in tab-delimited, linefeed-terminated format. Use it in conjunction with cvt_file.pl to work with files that are not in that format.

Another Excel-related Perl module, Spreadsheet::WriteExcel::FromDB, reads data from a table using a DBI connection and writes it in Excel format. Here's a short script that exports a MySQL table as an Excel spreadsheet:

#! /usr/bin/perl -w
# mysql_to_excel.pl - given a database and table name,
# dump the table to the standard output in Excel format.

use strict;
use DBI;
use Spreadsheet::ParseExcel::Simple;
use Spreadsheet::WriteExcel::FromDB;

# ... process command-line options (not shown) ...

@ARGV == 2 or die "Usage: $0 [options] db_name tbl_name
";
my $db_name = shift (@ARGV);
my $tbl_name = shift (@ARGV);

# ... connect to database (not shown) ...

my $ss = Spreadsheet::WriteExcel::FromDB->read ($dbh, $tbl_name);
print $ss->as_xls ( );

exit (0);

Each of the three utilities writes to its standard output, which you can redirect to capture the results in a file:

% from_excel.pl data.xls > data.txt
% to_excel.pl data.txt > data.xls
% mysql_to_excel.pl cookbook profile > profile.xls

Using the mysql Client Program

Writing MySQL-Based Programs

Record Selection Techniques

Working with Strings

Working with Dates and Times

Sorting Query Results

Generating Summaries

Modifying Tables with ALTER TABLE

Obtaining and Using Metadata

Importing and Exporting Data

Generating and Using Sequences

Using Multiple Tables

Statistical Techniques

Handling Duplicates

Performing Transactions

Introduction to MySQL on the Web

Incorporating Query Resultsinto Web Pages

Processing Web Input with MySQL

Using MySQL-Based Web Session Management

Appendix A. Obtaining MySQL Software

Appendix B. JSP and Tomcat Primer

Appendix C. References



MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2005
Pages: 412
Authors: Paul DuBois

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net