Из mysql в excel: Экспорт данных из таблицы MySQL в Excel

Импорт и экспорт базы данных MySQL

через phpMyAdmin

через консоль


  1. 1.

    Войдите в phpMyAdmin. Кликните по названию базы данных, экспорт которой вы хотите произвести:






  2. 2.

    Перейдите на вкладку «Экспорт», выберите формат SQL и нажмите Вперёд:






  3. 3.

    На вашем компьютере откроется диалоговое окно. Сохраните базу данных.


Готово, база данных экспортирована на ваш компьютер.


  1. 1.

    Подключитесь к серверу по SSH.



  2. 2.

    Введите команду:


    mysqldump -u USER -p DBNAME > DUMPFILE.sql

    Где:

    USER — пользователь базы данных (например, «u1234567_dbuser»),

    DBNAME — имя экспортируемой базы данных (например, «u1234567_database»),

    DUMPFILE.sql — название файла, в котором сохранится дамп базы данных в формате SQL.



  3. 3.

    Затем введите пароль базы данных (PASSWORD).


Готово, дамп базы данных с названием DUMPFILE.sql будет сохранен в корневую директорию услуги хостинга.

через phpMyAdmin

через консоль


  1. 1.

    Войдите в phpMyAdmin.


  2. org/HowToStep»>
    2.

    Выберите базу данных, в которую будет производиться импорт дампа (резервной копии базы данных):






  3. 3.

    Перейдите на вкладку «Импорт», нажмите Выберите файл и укажите файл дампа базы. При необходимости смените кодировку. Выберите формат SQL и нажмите Вперёд:




    Если файл дампа большой


    Если файл дампа слишком большой для импорта, импорт необходимо производить по SSH.



  4. 4.

    При успешном импорте появится сообщение:





Готово, база данных импортирована.


  1. 1.

    Предварительно загрузите в корневую директорию хостинга файл дампа базы данных по FTP или через панель управления.



  2. 2.

    Подключитесь к серверу по SSH.



  3. 3.

    Введите команду:


    mysql -u USER -p DBNAME

    Где:

    USER — пользователь базы данных (например, «u1234567_dbuser»),

    DBNAME — имя импортируемой базы данных (например, «u1234567_database»),

    DUMPFILE.sql — название файла, в котором сохранится дамп базы данных в формате SQL.



  4. 4.

    Затем введите пароль базы данных (PASSWORD).


Готово, база данных импортирована.

Подводные камни использования Excel Power Query и MySQL для автоматизации отчетности

Отделы аналитики, маркетинга, продаж часто сталкиваются со следующими трудностями при обновлении отчетности:
1. Данные приходится собирать воедино из нескольких источников.
2. Отчеты составляются в Excel, что накладывает значительные ограничения на объем обрабатываемых данных.
3. Внесение изменений в заранее настроенные разработчиками выгрузки дело как правило не самое быстрое.

Если отчеты нужно обновлять еженедельно или даже ежедневно, то эта процедура становится весьма напряженной даже для самых терпеливых. С помощью надстройки Excel Power Query и записи данных в MySQL можно свести обновление большинства отчетов до простого нажатия кнопки «Обновить»:
1. Данные из любого количества источников импортируются через SQL-запросы в обычные таблицы Excel.
2. Даже из большой базы можно записывать в Excel только небольшую часть данных (например, итоговые суммы за нужный диапазон дат с группировкой только по нужным столбцам).
3. Изменения в отчет можно вносить просто поменяв SQL-запрос. Далее формируем нужный отчет стандартными средствами Excel.

В этой статье я покажу как настраивать и автоматически заполнять простые базы данных MySQL (на примере выгрузки статистики всех ключевых слов из Яндекс Метрики), а потом одной кнопкой обновлять отчеты в Excel, используя надстройку Power Query. Power Query имеет весьма странные особенности работы при составлении SQL-запросов (особенно динамических), которые мы разберем во второй части статьи.

Выбор MySQL (или любой другой популярной базы данных) вполне очевиден — бесплатно, относительно просто, возможность работать с довольно большими базами данных без технических хитростей. В качестве примера будем использовать Amazon Web Services: дешево (в большинстве случаев используемый инстанс будет бесплатен для вас в течение 12 месяцев). 

Итак, начнем (если у вас уже есть базы данных с готовыми данными, то можно сразу переходить к разделу с Excel):
1. Регистрируемся на AWS (если еще нет учетки), запускаем самый простой инстанс t2.micro и заходим на него по SSH. Можно посмотреть краткую инструкцию в прошлом посте habrahabr.ru/post/265383. Обратите внимание, что нам потребуется первый в списке вариант инстанса на Amazon Linux AMI. Необходимо выставить правила, разрешающие обращение к инстансу по нужным портам:

В целях безопасности лучше выставлять ограничения на IP-адрес. Если у вас динамический IP, то это проблемная опция. Также иногда ограничение доступа к MYSQL по IP вызывает ошибку в Excel. Если выставить любой IP, то все работает.

2. Исполняем подряд команды, описанные в документацииdocs.aws.amazon.com/AWSEC2/latest/UserGuide/ec2-ug.pdf. Нам нужна глава «Tutorial: Installing a LAMP Web Server on Amazon Linux». Запомните пароль, который вводите при выполнении команды «sudo mysql_secure_installation». Для удобства установите phpMyAdmin как описано в конце этой главы. Если будете копипастить из документации строчку «sudo sed -i -e ‘s/127. 0.0.1/your_ip_address/g’ /etc/ht tpd/conf.d/phpMyAdmin.conf», то обратите внимание, что иногда при копировании в «httpd» появляется лишний пробел.

После этих действий на вашем инстансе должна открываться такая страница:

3. Заходим под пользователем root и паролем, который вводили при настройке. Для доступа к базе данных «извне» (т. е. из Excel) нам потребуется пользователь, отличный от root. Заводим его в интерфейсе phpMyAdmin в меню Пользователи —> Добавить пользователя. Добавим пользователя stats, зададим пароль и назначим ему привилегии SELECT и INSERT. Итого получим:

4. Теперь создадим базу данных data:

5. В данном примере будем наполнять базу статистикой посещений по ключевым словам из Яндекс Метрики. Для этого создадим таблицу seo (обратите внимание, что у столбца id надо отметить опцию A_I (auto increment)):

6. Для получения статистики по ключевым словам из Яндекс Метрики можно использовать следующий скрипт. В качестве параметров нужно указать начальную и конечную дату выгрузки (переменные $startDate и $endDate), авторизационный токен (в коде есть описание как его получить), номер счетчика, из которого нужно получить статистику, и параметры базы данных: ID инстанса, логин (у нас «stats»), пароль и название базы (у нас «data»). Скопируйте в корневую папку инстанса этот код и запустите командой «php seo.php».

Воронка продаж: делаем автоматически обновляемый отчет из базы данных с помощью Excel

Код PHP для выгрузки данных Яндекс Метрики

Если возникнут ошибки при соединении с базой, то они отобразятся в консоли и выполнение будет прервано. В случае успешного выполнения получим статистику ключевых слов за выбранный период:

Отлично, данные получены. Посмотрим как получать их в Excel.

Использование Power Query для выгрузки данных в Excel
Power Query представляет собой надстройку, которая расширяет возможности Excel по выгрузке данных. Скачать можно тут www.microsoft.com/en-us/download/details.aspx?id=39379. Для работы с MySQL может потребоваться MySQL Connector и Visual Studio (предлагаются при установке из дистрибутива).

1. После установки выбираем MySQL:

2. В качестве базы указываем ID нашего инстанса (как было в скрипте) ec2-…. compute.amazonaws.com. База данных data. Для ввода логина выбираем «База данных»:

3. В открывшемся окне дважды кликаем на таблицу seo и получаем:

В этом окне можно управлять запросами, изменяя столбцы и количество строчек. Когда база данных небольшая, то это работает. Однако если размер данных превышает даже 20MB, то Excel на большинстве компьютеров просто повиснет от такого запроса. К тому же неплохо бы менять даты запроса или другие параметры.

Динамические запросы в Power Query можно делать с помощью встроенного языка Mmsdn.microsoft.com/en-us/library/mt253322.aspx, однако запросы крайне неустойчивы в плане изменения каких-либо параметров в них. Чтобы запрос оставался «постоянным» сделаем следующий прием:

1. Сначала составляем таблицу, в которой указываем нужные нам параметры. В нашем примере это дата выгрузки. Формат ячеек со значениями лучше выставить как тестовый, т. к. Excel любит изменять формат ячеек по своему усмотрению:

2. Создадим запрос Power Query «Из таблицы», который будет просто дублировать эту таблицу:

3. В опциях запроса обязательно укажите формат второго столбца как Текст, иначе последующий SQL-запрос будет некорректным. Далее жмем «Закрыть и загрузить».

Итого мы получили запрос Power Query к обычной таблице, из которого будет брать значение начала и конца выгрузки.

http://excellentricks.ru/excel/kak-sobrat-otchety-s-sotrudnikov-i-ne-popast-v-excel-hell/

Чтобы сделать SQL-запрос потребуется отключить одну опцию: заходим в Параметры и настройки —> Параметры запроса —> Конфиденциальность и выбираем «Игнорировать уровни конфиденциальности для возможного улучшения производительности». Жмем Ок.

4. Теперь делаем запрос к нашей базе данных, указывая в качестве начала и конца периода значения таблицы из пункта 3. Снова подключаемся к базе в Power Query и нажимаем «Расширенный редактор» в меню.

Например, мы хотим получить сумму визитов, которые принесли ключевые слова, содержащие «2015». На языке M запрос выглядит так:
let Source = MySQL. Database("ec2-....compute.amazonaws.com", "data", [Query="select sum(visits) from seo where startDate>='"&Text.From(Таблица1{0}[Значение])&"' and endDate<='"&Text.From(Таблица1{1}[Значение])&"' and query like '%2015%';"]) in Source 

В параметрах startDate и endDate указываются значения в таблице из пункта 3. При запросе «Для выполнения этого собственного запроса к базе данных необходимы разрешения» жмем «Редактировать разрешение», проверяем, что все параметры подтянулись корректно и выполняем запрос. Теперь полученный ответ от SQL-запроса можно обработать обычными формулами Excel в привычном вам виде.

5. Важно! Когда вы будете обновлять выгрузку в следующий раз, то это приходится делать следующим способом (другие почему-то дают ошибку):
— меняем даты в таблице из пункта 1
— заходим в меню Данные —> Подключения и нажимаем «Обновить все»:

В этом случае все запросы выполнятся корректно и ваши отчеты обновятся автоматически. Итого для обновления отчета вам потребуется только изменить параметры запроса и нажать «Обновить все».

Интеграция MySQL в Excel

Получайте готовые к анализу данные из MySQL в Excel по расписанию, чтобы оптимизировать отчетность и аналитику.

Начать бесплатно

ЗАКАЗАТЬ ДЕМО

Нам доверяют 24 000+ компаний по всему миру

Подключите MySQL к Excel с помощью Coupler.io

Наслаждайтесь простым и эффективным решением для интеграции MySQL и Excel всего за несколько простых шагов без каких-либо технических навыков. Автоматизируйте отчеты и потоки аналитических данных с помощью Coupler.io: собирайте, объединяйте, просматривайте, преобразовывайте и фильтруйте данные из MySQL перед их загрузкой в ​​Excel. Настройте желаемое расписание для автоматического обновления данных, которое может запускаться от каждого месяца до каждых 15 минут!

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

Подключить MySQL к Excel

Альтернативные направления

Лукер Студия

Собирайте данные из различных приложений и загружайте их в Looker Studio. Создавайте подробные отчеты, чтобы принимать решения на основе данных, и делитесь ими с заинтересованными сторонами.

УЗНАТЬ БОЛЬШЕ

Google BigQuery

Импортируйте информацию из приложений в хранилище BigQuery и храните ее в одном месте. Выполняйте сложные манипуляции с данными, используя SQL-запросы для быстрого извлечения ценной информации.

УЗНАТЬ БОЛЬШЕ

Google Таблицы

Перемещайте данные из приложений в Google Таблицы и пользуйтесь гибкостью Таблиц. Создавайте многочисленные автоматически обновляемые информационные панели и отчеты и легко делитесь ими со своей командой.

УЗНАТЬ БОЛЬШЕ

Получите максимум от интеграции MySQL с Excel

Соберите данные

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

Понимание данных

Предварительный просмотр, преобразование, сортировка и фильтрация данных для большей ясности всего за несколько кликов в интерфейсе Coupler.io. Поднимите аналитику данных на новый уровень.

Автоматизация данных

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

Акт по данным

Визуализируйте свои данные, быстро подключившись к средствам визуализации данных и BI. С легкостью создавайте информативные информационные панели и принимайте более взвешенные решения.

Как соединить MySQL и Excel

  1. Подключить MySQL и настроить параметры

  2. Предварительный просмотр, преобразование и фильтрация данных

  3. Настройка Excel и настройка параметров

  4. Установить расписание обновления данных

ЗАРЕГИСТРИРОВАТЬСЯ БЕСПЛАТНО

Другие интеграции, которыми вы можете воспользоваться

Тип источника:

Премиум

Доступно по запросу для Squad, Business,

и корпоративные планы

Основной
Готовые интеграции включены во все планы

Категории:

Все

Аналитика и маркетинг

CRM

Базы данных и файловые системы

Электронная коммерция и розничная торговля

Электронная почта и служба поддержки клиентов

Финансовый менеджмент и бухгалтерский учет

Фитнес и спортивный менеджмент

Формы и опросы

Управление персоналом

Прочее

Производительность и отслеживание времени

Управление проектами

Поиск

Все
Аналитика и маркетинг

CRM

Базы данных и файловые системы

Электронная коммерция и розничная торговля

Электронная почта и обслуживание клиентов

Финансовый менеджмент и бухгалтерский учет

Фитнес и спортивный менеджмент

Формы и опросы

Управление персоналом

Производительность и отслеживание времени

Управление проектом
Другое

Сосредоточьтесь на своих бизнес-целях, пока мы позаботимся о ваших данных

Попробуйте Coupler. io

Coupler.io использует файлы cookie для улучшения вашего просмотра, анализа
трафика и показывать таргетированную рекламу. Продолжая использовать наш сайт и
приложения, вы соглашаетесь с нашей Политикой конфиденциальности.

Как получить данные из базы данных MySQL в Excel

спросил

Изменено
2 года, 10 месяцев назад

Просмотрено
25 тысяч раз

Как получить данные из таблицы базы данных MySQL в Excel?

  • MySQL
  • Excel

1

  1. Установить Разъем/ODBC

  2. Создать ODBC DSN ( Панель управления/Администрирование/ODBC в Windows)

  3. Импорт данных с использованием Microsoft Query ( Данные/Внешние данные/Из других источников/Microsoft Query в Excel 2007 )

Если ваш сервер также является вашим клиентским компьютером, вы можете сделать

 select * from table1 to outfile 'c:/path/outfile. csv'

(обратите внимание на использование косой черты даже в Windows)

Если ваш сервер является удаленным компьютером, убедитесь, что на клиентском компьютере установлено клиентское программное обеспечение MySQL, и выполните:

 mysql -e "SELECT * from table1" > имя_файла

Затем вы можете открыть CSV-файл в Excel.

Попробуйте с

 ВЫБЕРИТЕ столбец 1, столбец 1, столбец 1 из имени таблицы INTO OUTFILE "path/CSV/tablename.xls" ПОЛЯ, ЗАВЕРШЕННЫЕ "," НЕОБЯЗАТЕЛЬНО ЗАКРЫТЫЕ '"' СТРОКИ, ЗАВЕРШЕННЫЕ "\n"

см. http://dev.mysql.com/doc/refman/5.1/en/load-data.html

Я думаю, что проще всего использовать ODBC. Вы можете загрузить драйвер ODBC для MySQL (если у вас его еще нет). Затем, попав в Excel, вы можете перейти в «Данные-> Получить внешние данные» (название и расположение пунктов меню могут различаться) и выбрать или создать источник данных.

odbc-запрос.

Excel: /данные/получить внешние данные.