You are using an outdated browser. For a faster, safer browsing experience, upgrade for free today.

Загрузка...

Подключение из Excel к MySQL

В данной инструкции речь пойдёт о прямом использовании данных взятых из MySQL в Excel напрямую, т.е. без использования промежуточных действий связанных с выгрузкой данных в XML или CSV и т.д.

Использованные программы:

  • Windows 10;
  • MySQL версии 5.7.25-log MySQL Community Server (GPL);
  • MySQL Connector ODBC 8.2.0 winx64;
  • Microsoft Office 2016 (Excel).

Перед установкой MySQL Connector ODBC не установлен ли он уже на компьютере/сервере, тогда его нужно только донастроить. Чтобы проверить необходимо зайти в Панель управления -> Администрирование -> Источники данных ODBC (64-разрядная версия):

В Панель управления можно зайти 2-мя способами:

Способ 1-й:

Нажимаем на пуск левой кнопкой мыши или на клавиатуре жмём кнопку Win (кнопка с логотипом Windows):

Меню Пуск

И сразу на кириллице начинаем писать панель и сразу же вверху отобразится Панель управления - жмём на неё:

Пишем панель управления

После этих действий запустится Панель управления:

Запущенная панель управления

Способ 2-й - через командную строку:

Нажимаем сочетание клавишь Win+R - всплывёт диалоговое окно с названием Выполнить, в строке ввода команд вводим control и жмём либо Ок мышкой либо Enter на клавиатуре и как следствие запустится Панель управления:

Панель управления с помощью команды control

 

Но панель управления запущена в неудобном формате, надо переключить просмотр например на мелкие значки:

Переключение панели управления на мелкие значки

После переключения панели управления на мелкие значки заходим в Администрирование:

Панель управления мелкие значки и администрирование

В Администрировании выбираем Источники данных ODBC (64-разрядная версия):

Источники данных ODBC проверка

В диалоговом окне Администратор источника данных ODBC (64-разрядная версия) в поле: Источники данных пользователя нет ничего, чтобы даже отдалённо напоминало о подключении к MySQL:

 Администратор источника данных ODBC (64-разрядная версия)

Жмём Добавить:

ничего

В диалоговом окне Создание нового источника данных нет ничего с именем MySQL и ODBC. 

Также желательно проверить не только Источники данных ODBC (64-разрядная версия), но и ODBC Data Sources (32-bit), которая выше. Быть может там есть то что надо.

И так продолжим.

На данном этапе нужно скачать этот самый ODBC driver, для этого в поисковике, например Яндекс вводим запрос mysql driver odbc:

Ищем в поисковике mysql odbc driver

и переходим по данной ссылке : https://dev.mysql.com/downloads/connector/odbc/

сайт где живёт odbc драйвер

и и скачиваем MSI установщик: жмём синюю кнопку download и нас перебросит на страницу с просьбой залогиниться, но это делать не обязательно, просто жмём ссылку No thanks, just start my download.

Отказываемся от логирования

 

После начнётся либо сразу загрузка файла mysql-connector-odbc-8.2.0-winx64, либо браузер предложет путь для сохранения.

Затем запускаем mysql-connector-odbc-8.2.0-winx64.msi на выполнение:

Жмём Next>

Начало установки ODBC

Выбираем: I accept the terms in the license agreement 

и снова жмём Next>

Соглашаемся с лицензией

Выбираем Complete  и далее Next >

Выбираем полную установку

И жмём Install

Предварительная настройка завершена - теперь установка

На данном этапе если все предыдущие действия связанные с установкой проводились из-под локальной учётной записи, то потребуется ввести логин/пароль администраторской учётки!!!!

Установилось всё успешно!!!

Снова заходим  в Создание нового источника данных и видим 2 версии драйвера, выбираем один из них, напримен Unicode и жмём Готово:

Выбираем ODBC Unicode

В появившемся диалоговом окне MySQL Connector/ODBC Data Source Configuration

Заполняем все поля и жмём тест - тест успешен!

Заполняем все поля для подключения

Сразу хотелось бы сказать, то что здесь использован логин root - это частный случай и только для теста. В продакшене такого делать конечно же нельзя, нужен отдельный логин с меньшими правами!!!

 

Жмём ОК для создания подключения

Жмём ОК!

И в источниках данных появился источник данных - Test_MySQL:

В источниках данных пользователя появился нужный источник

и снова жмём ОК - окно закроется.

Первая часть работы сделана!

Далее, если уже был открыт Excel, то закройте его и запустите заново!!!

В Excel преходи во вкладку Данные и кликаем на Подключения:

Excel - Днные - Подключения

В появившемся диалоговом окне Подключения к книге жмём кнопку Добавить:

Подключения к книге - Добавить

В окне Существующие подключения скорее всего ничего нужно не будет, т.к. драйвер ODBC был только установлен, следовательно жмём на кнопку Найти другие:

Найти другие

В окне Выбор источника данных жмём на кнопку Создать:

Выбор источника данных - Создать

В окне Мастер подключения к данным выбираем ODBC DSN и жмём Далее:

Выбираем ODBC DSN

И выбираем только что настроенный Test_MySQL и жмём далее:

Test_MySQL и Далее

В Выбор базы данных и таблицы оставим всё как есть и жмём Далее >:

Подключение к таблице

и сохраним файл подключения в каталоге по умолчанию - жмём Готово:

Финишь настройки

Естественно так называемое понятное имя файла можно изменить, но это по желанию!

В итоге, в диалоговом окне Подключения к книге появилось подключение к базе и таблице:

Подключение к базе и таблице

и закрываем его.

Настройки по созданию подключения завершены! 

Приступаем к этапу извлечения данных из MySQL таблицы в сам Excel. Для этого в Excel на вкладке Данные жмём на Получением внешних данных и выбираем Существующие подключения:

Существующие подключения

В диалоговом окне Существующие подключения выбираем созданный файл и ждмём открыть:

Открываем файл

В диалоговом окне Импорт данных жмём ОК и отобразится вся таблица:

Импорт данных

В Excel появились те же данные что и в консоли:

В excel получены данные

В результате получен успех успехов!!!

Естественно, если не нужны все данные из таблицы, можно в Excel подправить sql запрос и получить какие-нибудь аггрегированные данные:

снова зайдём в Данные -> Получение внешних данных -> Существующие подключения и т.д. и в Импорт данных нажмём на Свойства:

Импорт данных - Свойства

В окне Свойства подключения переходим на вкладку Определение:

Свойства подключения - Определение

И в поле Текст команды правим запрос, на примерно такой и жмём ОК:

Меняем sql запрос

Excel предупредит что файл подключения с настройками изменится - нажмём Да

Перезаписываем файл

и в Импорт данных снова ОК и в результате получим:

Результат аггрегации

В зависимости от задачи, можно в MySQL сделать вьюху(view), который будет хранить очень большой и сложный SELECT запрос ко многим таблицам, а уже SELECT запрос к самой view можно вставлять здесь в Excel.

Файл для создания базы и таблицы и немного данных скачать.