MySQL / 10. Импорт и экспорт данных

Обмен данными между MySQL и Microsoft Excel

Задача
Вы хотите осуществлять обмен информацией между MySQL и Excel.

Решение
Используйте такие утилиты, как DBTools и MySQLFront. Или примените модули Perl, которые читают и записывают файлы электронных таблиц Excel, для создания собственных утилит передачи данных.

Обсуждение
Один из способов передачи файлов Excel в MySQL – применение утилит DBTools и MySQLFront, упомянутых в рецепте 10.38 при обсуждении обработки файлов Access. Обе программы умеют читать и файлы Excel. Но обе работают только в Windows, так что если вам нужно решение, работающее и в UNIX, и в Windows, вы можете читать и записывать электронные таблицы Excel из сценариев Perl, установив предварительно несколько модулей:

• Модуль Spreadsheet::ParseExcel::Simple предоставляет удобный интерфейс для чтения таблиц Excel.

• Модуль Spreadsheet::WriteExcel::Simple позволяет создавать файлы в формате электронных таблиц Excel.

Эти модули доступны в Perl CPAN. (Они фактически являются внешними интерфейсами к другим модулям, которые также нужно будет предварительно установить.) После установки модулей для обращения к их документации используйте такие команды:

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

Эти модули значительно упрощают написание двух небольших сценариев (приведенных ниже) для преобразования электронных таблиц в формат с разделителями-символами табуляции и обратно. Используя эти сценарии в сочетании с приемами импорта и экспорта данных MySQL, вы сможете перемещать содержимое электронных таблиц в таблицы MySQL и наоборот.

Можно применять их «как есть» или адаптировать к вашим конкретным требованиям.

Сценарий from_excel.pl читает электронные таблицы Excel и преобразует их в формат значений, разделенных символами табуляции:

#! /usr/bin/perl -w
# from_excel.pl – читает электронную таблицу Excel, записывает
# в стандартный вывод в формате: символы табуляции в качестве разделителей,
# символ перевода строки – признак конца строки.
use strict;
use Spreadsheet::ParseExcel::Simple;
@ARGV or die "Usage: $0 excel-file\n";
my $xls = Spreadsheet::ParseExcel::Simple->read ($ARGV[0]);
foreach my $sheet ($xls->sheets ())
{
while ($sheet->has_data ())
{
my @data = $sheet->next_row ();
print join ("\t", @data) . "\n";
}
}
exit (0);

Сценарий to_excel.pl выполняет обратную операцию чтения файла с разделителями-табуляциями и записывает его в формате Excel:

#! /usr/bin/perl -w
# to_excel.pl – читает ввод, разделенный символами табуляции, признак конца
# строки – символ перевода строки, записывает в стандартный вывод в формате Excel.
use strict;
use Spreadsheet::WriteExcel::Simple;
my $ss = Spreadsheet::WriteExcel::Simple->new ();
while (<>) # читать каждую строку ввода
{
chomp;
my @data = split (/\t/, $_, 10000); # разбить на части, сохраняя все поля
$ss->write_row (\@data); # записать строку в таблицу
}
print $ss->data (); # вывести таблицу
exit (0);

Сценарий to_excel.pl принимает ввод в формате значений, разделенных символами табуляции, с символом перевода строки в качестве признака конца строки. Для того чтобы он мог работать с файлами в другом формате, используйте его в сочетании с cvt_file.pl.

Еще один связанный с Excel модуль Perl, Spreadsheet::WriteExcel::FromDB, читает данные из таблицы, используя соединение DBI, и записывает их в формате Excel. Рассмотрим небольшой сценарий, который экспортирует таблицу MySQL в виде электронной таблицы Excel:

#! /usr/bin/perl -w
# mysql_to_excel.pl – получив имена базы данных и таблицы,
# выгружает таблицу в стандартный вывод в формате Excel.
use strict;
use DBI;
use Spreadsheet::ParseExcel::Simple;
use Spreadsheet::WriteExcel::FromDB;
# ... обработка опций командной строки (не приводится) ...
@ARGV == 2 or die "Usage: $0 [options] db_name tbl_name\n";
my $db_name = shift (@ARGV);
my $tbl_name = shift (@ARGV);
# ... соединение с базой данных (не приводится) ...
my $ss = Spreadsheet::WriteExcel::FromDB->read ($dbh, $tbl_name);
print $ss->as_xls ();
exit (0);

Все три утилиты выводят результат в стандартный вывод, откуда его можно перенаправить, например, в файл:

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

Статьи по MySQL на эту тему:

Импорт XML в MySQL
Обмен данными между MySQL и FileMaker Pro
Экспорт результатов запроса в XML

Вернуться в раздел: MySQL / 10. Импорт и экспорт данных