Глава 5. Импорт данных MySQL в Excel


WebMoney: WMZ Z294115950220 WMR R409981405661 WME E134003968233	Visa 4274 3200 2453 6495

Глава 5. Импорт данных MySQL в Excel

Данные могут быть импортированы из MySQL в рабочий лист Microsoft Excel при помощи опции Import MySQL Data после выбора таблицы представления или процедуры, чтобы импортировать.

Операция импорта данных игнорирует столбцы, содержащие двоичные данные (Varbinary или Blob). См. Известное ограничение для двоичных данных.

Чтобы импортировать данные MySQL в рабочий лист Excel

Запустите Excel, выберите вкладку меню Data и нажмите MySQL for Excel, чтобы открыть панель задач MySQL for Excel.

В ней в поле Open a MySQL Connection дважды щелкните по существующей связи, чтобы показать доступные схемы базы данных.

Выберите схему из списка и нажмите Next, чтобы показать все объекты базы данных.

Выберите таблицу, представление или процедуру с данными, чтобы импортировать и затем нажмите Import MySQL Data. Окно предварительного просмотра показывает отобранные данные и предоставляет Options и Advanced Options для тонкой настройки.

Для описания каждой опции см. Импорт таблицы или представления или раздел 5.1.

Для описания каждой расширенной опции см. раздел 5.2.

Нажмите Import для выполнения действия.

Окна Import Data обеспечивают предварительный просмотр столбцов, чтобы выбрать во время операции по импорту. Можно определить столбцы и строки, чтобы импортировать. Как следующие данные показывают, предварительный просмотр включает маленькое подмножество строк для отобранной таблицы или представления.

Рис. 5.1. Импорт данных таблицы в MySQL for Excel
Content is described in the surrounding text.

Импорт таблицы или представления. Варианты данных импорта относятся только к текущей операции. Возможности для импортирования таблицы или представления:

Include Column Names as Headers: Выбрано по умолчанию, это вставляет имена столбцов наверху рабочего листа Microsoft Excel как строку "headers".

Limit to ___ Rows and Start with Row ___: Выключено по умолчанию, это ограничивает диапазон импортированных данных. Опция Limit to по умолчанию установлена в 1 и определяет количество строк для импорта. Опция Start with Row по умолчанию установлена в 1 (первая строка) и определяет, где импорт начинается. У каждого выбора есть максимальное значение COUNT(rows) в таблице.

Create a PivotTable with the imported data: Выключено по умолчанию, см. раздел 5.4 .

Add Summary Fields: Выключено по умолчанию, добавляет итоговую область к каждому столбцу, см. раздел 5.3.

Выбор столбцов для импорта. По умолчанию все столбцы отобраны и будут импортированы. Определенные столбцы могут отбираться (или отменяться), с использованием стандартного метода Microsoft Windows: Control + клик мышкой для выбора отдельных столбцов или Shift + клик мышкой для выбора диапазона.

Цвет фона показывает статус каждого столбца. Белый означает, что столбец был выбрана, а поэтому будет импортирован. С другой стороны серый фон означает, что столбец не будет импортирован.

Щелчок правой кнопкой мыши где угодно в сетке предварительного просмотра открывает контекстное меню с опцией Select None или Select All, в зависимости от текущего статуса.

5.1. Импорт процедур

Чтобы импортировать сохраненные процедуры, сначала выберите соответствующие параметры, в случае необходимости, и затем нажмите Call. Затем используйте следующие опции, чтобы настроить импорт:

Include Column Names as Headers: Включена по умолчанию, это вставит имена столбцов наверху рабочего листа Excel как строку "headers".

Import: Поскольку процедура могла бы возвратить многократные наборы результатов, опции импорта включают:

Selected Result Set: Импортирует отобранный лист. Это поведение по умолчанию.

All Result Sets - Arranged Horizontally: Импортирует все наборы результатов в рабочий лист Excel горизонтально и вставляет один пустой столбец между наборами результатов.

All Result Sets - Arranged Vertically: Импортирует все наборы результатов в рабочий лист Excel вертикально и вставляет одну пустую строку между наборами результатов.

Например, область предварительного просмотра в окне Import Data подобна показанной ниже после выбора нужных опций и нажатия Call, чтобы выполнить процедуру.

Рис. 5.2. Импорт хранимой процедуры в MySQL for Excel
Content is described in the surrounding text.

5.2. Расширенные настройки импорта

Расширенные настройки позволяют вам настроить, как MySQL for Excel импортирует данные из таблиц, представлений и процедур MySQL. Используйте Reset to Defaults, чтобы восстановить оригинальные значения опций.

Ряд расширенных настроек затрагивает текущую операцию импорта данных, как описано ниже.

Вкладка General

Рис. 5.3. Основные опции
Content is described in the surrounding text.

Основные опции:

Use the first [10 ] rows to preview the MySQL table's data. Это затрагивает шаг предварительного просмотра в импорте и редактировании. По умолчанию 10 строк. Диапазон значений от 1 до 100.

Escape text values that start with "=" so Excel does not treat them as formulas. Это может не отразить различия в предварительном просмотре, потому что это применяется только после того, как данные импортируются в рабочий лист Excel.

Вкладка Formatting

Рис. 5.4. Опции форматирования
Content is described in the surrounding text.

Опции форматирования:

Excel number format for DateTime and TimeStamp data: [m/d/yyyy h:mm]

Excel number format for Date data: [m/d/yyyy]

Excel number format for Time data: [hh:mm:ss]

Import all floating-point data using a DECIMAL data type. Это импортирует данные с плавающей запятой как тип DECIMAL, вместо FLOAT или DOUBLE, чтобы показать точное значение данных в базе. Когда эта опция отключена, значения с плавающей запятой, показанные в предварительном просмотре, являются приближенными значениями. Включена по умолчанию.

Вкладка Excel Table

Рис. 5.5. Опции таблиц Excel
Content is described in the surrounding text.

Опции таблиц Excel:

Create an Excel table for the imported MySQL table data. Включена по умолчанию и имеет следующие параметры настройки:

Use style [ ] for the new Excel table. По умолчанию MySqlDefault.

Prefix Excel tables with the following text: _______. По умолчанию выключена.

5.3. Добавление итоговых полей

Итоговые поля это вычисляемые поля, добавленные к последней строке каждого столбца в импортированной таблице MySQL. Выпадающий список для каждого итогового поля в получающемся рабочем листе включает такие функции, как Average, Sum, Min и Max.

Эта опция была добавлена в MySQL for Excel 1.3.0.

Опция Add Summary Fields (выключена по умолчанию) появляется в диалоге Import Data.

Рис. 5.6. Опция 'Add Summary Fields'
Content is described in the surrounding text.

Предоставление возможности этого выбора добавляет ряд итоговых полей для соответствующих столбцов в ваших импортированных данных. Недавно созданная строка, добавленная к существующим, показана в следующем примере.

Рис. 5.7. Новая строка добавлена
Content is described in the surrounding text.

Выберите в строке стрелку вниз и щелкните по ней, чтобы показать ряд итоговых опций.

Рис. 5.8. Строка 'Add Summary Fields'

5.4. Создание PivotTables

PivotTable может быть создан из импортированных таблиц MySQL, представлений, хранимых процедур или всей Excel Data Model.

Эта опция была добавлена в MySQL for Excel 1.3.0.

Excel PivotTable суммирует и обеспечивает визуальное представление данных многими различными способами. Это родная особенность Excel. Для получения инструкций относительно того, как использовать PivotTables, см. документацию, предоставленную Microsoft.

Следующий пример показывает простой вариант использования, где пустой PivotTable создается из импортированной таблицы MySQL. Этот пример использует таблицу film базы данных sakila. Чтобы создать PivotTable, выберите таблицу film из панели задач и затем нажмите Import MySQL Data . В диалоге Import Data выберите опцию Create a PivotTable и нажмите OK, чтобы выполнить операцию.

Рис. 5.9. Создайте PivotTable с импортированными данными
Content is described in the surrounding text.

Когда отмечена опция Create a PivotTable with the imported data, пустая PivotTable введен справа от импортированных данных. Название PivotTable следует тем же самым правилам обозначения, используемым для таблиц Excel, составленных из импортированных данных, но PivotTable может быть создан с расширенной опцией Create an Excel table for the imported MySQL data. Это означает, что PivotTable может быть создан из импортированного диапазона Excel (если вышеупомянутая расширенная настройка выключена) или из импортированной таблицы Excel (если опция включена).

Нажмите Import, чтобы собрать данные таблицы film к активному рабочему листу Excel, и это также создает PivotTable для тех данных как показано ниже.

Рис. 5.10. Пустой PivotTable
Content is described in the surrounding text.

Нажатие на PivotTable открывает окно PivotTable Fields рядом с панелью задач MySQL for Excel и отсюда можно выбрать поля, которые вы хотите суммировать в отчете PivotTable. Перетащите поля списка в любую зону FILTERS, COLUMNS, ROWS или VALUES, в зависимости от визуализации, которую вы хотите в отчете. Отчет абсолютно динамичный, это означает, что можно изменить представление, двигая поля между зонами, пока вы не увидите визуализацию, в которой вы нуждаетесь для своего отчета PivotTable.

Этот рисунок показывает пример отчета PivotTable, используя таблицу sakila.film, импортированную в предыдущем примере. Это включает в себя фильтр release_year и суммирует значения rental_rate, также группируя данные по значениям в колонке рейтинга.

Рис. 5.11. Пример PivotTable

Расширение одной из групп показывает свои значения из названия и столбцов описания, как показано ниже.

Рис. 5.12. Расширение группы
Content is described in the surrounding text.

Та же самая операция может быть выполнена с данными, прибывающими из представления MySQL или хранимых процедур. Одно различие для хранимых процедур позволяет вам создать PivotTable для каждого из импортированных наборов результатов, возвращенных вызовом процедуры.

В следующем примере выбрана хранимая процедура film_in_stock, и настраиваются ее входные значения параметров. Когда процедуру вызывают, она возвращает один набор результатов (Result1) и таблицу OutAndReturnValues (всегда есть, если процедура произвела параметры или возвращаемое значение).

Рис. 5.13. Хранимая процедура
Content is described in the surrounding text.

В следующем примере включена опция All Result Sets - Arranged Horizontally. Поскольку также включена опция Create a PivotTable with the imported data, PivotTable был создан для каждого возвращенного набора результатов.

Рис. 5.14. Горизонтальное упорядочивание
Content is described in the surrounding text.

Важный вариант использования для PivotTables это когда мы создаем его для многократных связанных таблиц, как правило, единственная таблица не содержит все данные, необходимые отчету PivotTables. Можно создать единственный PivotTable, связанный с данными в текущей Excel Data Model, которая содержит области от нескольких связанных таблиц. Таким путем можно использовать данные в единственном отчете для всей схемы MySQL в случае необходимости. Однако это можно сделать только в Excel 2013 (и позже), где доступна Excel Data Model.

В Excel до 2013 PivotTable могут быть созданы только для каждой импортированной таблицы или представления. Это вызвано тем, что единственный PivotTable для всей Excel Data Model требует, чтобы таблицы были связаны друг с другом. Если отношения Excel не могут быть созданы, то этот тип PivotTable не может быть создан. В этих случаях диалог Import Data выглядит примерно так:

Рис. 5.15. Создает выбор отношений Excel до 2013
Content is described in the surrounding text.

Нажатие Why is this option disabled? покажет информационный диалог (см. следующий рисунок) с объяснением отключенных средств управления.

Рис. 5.16. Пояснение отключенных средств управления
Content is described in the surrounding text.

Следующий пример использует все таблицы в схеме. Можно выбрать каждую вручную или использовать Control + A в списке объектов базы данных, чтобы выбрать их все. При нажатии Import Multiple Tables and Views диалог Import Data появляется, как показано в примере. Подтвердите, что опция Create a PivotTable выбрана и установлена в for all the tables in the data model. Сохраните это значение.

Рис. 5.17. Импорт всех таблиц и представлений
Content is described in the surrounding text.

При нажатии на Import, данные во всех отобранных таблицах импортируются в Excel, Data Model и отношения Excel создаются, и новый рабочий лист создается, который содержит PivotTable со всеми таблицами, которые были импортированы. Эта комбинация продемонстрирована ниже. Обратите внимание на то, что все таблицы перечисляются в окне PivotTable Fields.

Рис. 5.18. Импорт и списки
Content is described in the surrounding text.

Можно также формировать выпадающий список для каждой импортированной таблицы или представления (см. следующий рисунок), который в свою очередь создает PivotTable для каждой из импортированных таблиц или представлений, в противоположность созданию единственного PivotTable для всех.

Рис. 5.19. Импорт каждой импортированной таблицы или представления
Content is described in the surrounding text.