Как экспортировать данные SQL в Excel с помощью n8n
SQL Server Management Studio (SSMS) для многих специалистов остаётся важнейшим инструментом управления. Однако, как показывают тенденции Google, пользователи постоянно интересуются, как можно загрузить данные SQL в таблицу Excel.
Это неудивительно, ведь данные часто хранятся централизованно в базе данных, и всё чаще пользователям требуется экспортировать таблицы SQL в Excel для дальнейшего анализа.
В этом простом в освоении руководстве рассматриваются три различных способа экспорта данных SQL в Excel: с помощью самого Excel, SSMS и n8n.
Вы также узнаете основные плюсы и минусы каждого метода, что позволит вам сэкономить много усилий при выборе лучшего инструмента. В конце статьи мы представим шаблон рабочего процесса для автоматического экспорта SQL-запросов в Excel.
💡Ищете автоматические интеграции MySQL? Посмотрите бесплатные рабочие процессы n8n и узнайте, как интегрировать MySQL с сотнями различных сервисов.
Как перенести данные из SQL в Excel?
Возможно, вы хотите помочь своей компании повысить эффективность работы или автоматизировать собственную нагрузку. В зависимости от потребностей, вы можете применить один из следующих методов:
- Использование самого MS Excel с помощью функции Power Query,
- Экспорт данных SQL в файл Excel с помощью SQL Server Management Studio,
- Использование возможностей n8n.
Давайте начнём и узнаем, как можно получить больше за меньшее время с помощью креативных методов автоматизации!
Импорт данных SQL непосредственно в Excel
Первый подход заключается в использовании функциональности Excel для настройки импорта данных SQL в Excel. MS Excel позволяет импортировать данные из различных источников с помощью функционала Power Query.
Как специалист в области инженерии данных, вы можете настроить процесс извлечения данных SQL для своих коллег. Это позволит им регулярно загружать новые данные с помощью MS Excel самостоятельно. Этот метод удобен для относительно небольших команд, но в больших компаниях установка и настройка соединений Excel для многих людей может быстро стать утомительной.
Шаг 1. Установите драйвер ODBC
Перед тем как экспортировать результаты SQL в Excel, нам потребуется некоторая настройка системы. Пользователи Windows сначала должны установить драйвер Open Database Connectivity (ODBC). Вы можете получить коннектор для баз данных MySQL с официального сайта. Следующим шагом будет установка коннектора.
Шаг 2. Добавьте новый источник данных
После установки драйвера ODBC нажмите кнопку «Пуск» в Windows и найдите «ODBC Data Sources».
Здесь вы можете создать новое имя источника данных (DSN) для текущего пользователя или всей системы. Мы будем импортировать данные из SQL в Excel, используя пример из нашего предыдущего руководства — импорт CSV в MySQL. Нажмите «Add», укажите имя DSN, IP-адрес сервера или имя хоста и учетные данные базы данных. Затем проверьте соединение и, наконец, нажмите «OK».
Шаг 3. Импорт данных с помощью Power Query
После добавления источника данных мы можем открыть Excel, перейти на панель Data - Get & Transform Data и начать процесс импорта. Нажмите Get Data - From Other Sources - From ODBC, затем выберите созданный источник данных. Нажмите «OK» и перейдите в окно «Navigator» где можно выбрать таблицу SQL для импорта.
Нажмите кнопку «Load», чтобы импортировать таблицу в новый лист Excel. И все готово!
Этот метод может показаться несколько упрощенным, но есть несколько мест, где вы можете предоставить пользовательские SQL-запросы:
- После запуска процесса импорта вы можете нажать кнопку «Advanced» и предоставить свой SQL-запрос. Этот запрос будет использоваться для импорта вместо чтения всей таблицы.
- В окне «Navigator» нажмите кнопку «Transform Data». Откроется новое окно «Power Query Editor». В нем вы можете выполнить дальнейшие действия. Вы можете добавить или удалить столбцы, отсортировать строки, транспонировать таблицу и многое другое.
Экспорт SQL-запроса в Excel через SSMS
Еще один способ экспорта таблиц SQL в Excel доступен в SSMS. Встроенный мастер импорта и экспорта SQL Server является популярным инструментом среди инженеров. Он позволяет как вручную переносить данные, так и писать SQL-запросы.
В этом разделе покажем, как переносить данные без выполнения SQL-запросов. Этот метод полезен, если пользователи в вашей компании не имеют прямого доступа к серверу баз данных. Или же вам может понадобиться поделиться фрагментом данных с внешними пользователями. В таких сценариях можно экспортировать MS SQL в Excel с помощью SSMS.
Шаг 1. Установите SSMS и настройте локальный сервер SQL Server
Вы можете бесплатно загрузить SSMS с Microsoft. Процесс установки прост.
Если у вас нет работающего SQL Server, необходимо выполнить несколько дополнительных действий, прежде чем воспроизводить следующие шаги:
- Установите SQL Server Express LocalDB с сайта Microsoft
- Загрузите и образец базы данных SQL (например, AdventureWorks), как описано в руководстве. Наконец, откройте SQL Server Configuration Manager и проверьте Named Pipes. Строка будет выглядеть примерно так
\\\.\pipe\MSSQL$SQLEXPRESS\sql\query
- Эта информация нужна, если у вас локальная установка SQL Server
Шаг 2. Подключение к серверу SQL и экспорт данных SQL в Excel
После того как вы создали сервер LocalDB и восстановили базу данных образца, вы можете открыть SQL Server Management Studio. Затем подключитесь к базе данных из панели «Object Explorer».
После подключения выполните следующие действия:
- Щелкните правой кнопкой мыши на имени базы данных и выберите Tasks - Export Data. Откроется «Export Wizard».
- Сначала настройте источник данных. Выберите «Microsoft OLE DB Driver for SQL Server», затем нажмите «Properties» и настройте соединение. Здесь вам нужно указать «Pipe» в качестве имени сервера и выбрать аутентификацию Windows. Не забудьте выбрать базу данных!
- Наконец, нажмите «Test Connection» и «OK» после завершения.
Далее нам нужно выбрать MS Excel в качестве места назначения. Выберите путь к файлу Excel на локальном диске и выберите версию Excel. Нажмите кнопку «Next» и выберите, что вы хотите:
- скопировать данные из одной или нескольких таблиц или представлений,
- написать SQL-запрос, чтобы указать данные для переноса.
Давайте выберем первый вариант.
В следующем окне выберите одну или несколько исходных таблиц и экспортируйте две таблицы: Address и ProductCategory. Следуйте указаниям «Export Wizard» до конца и откройте созданный файл Excel.
💡 Быстрый лайфхак: нажмите на «Пуск» и найдите файл «Import and Export Data». Так вы сможете запустить процесс экспорта, не открывая SSMS.
Экспорт SQL-запроса в Excel с помощью n8n
Первые два шага имеют один общий недостаток — они требуют определённых ручных усилий. Это может стать утомительным, когда вы начнёте экспортировать большие объёмы данных на регулярной основе. Но с помощью n8n вы можете автоматизировать эти рутинные задачи.
В этой части руководства узнаем, как автоматически экспортировать данные из SQL-сервера в Excel.
Этот примерный рабочий процесс показывает, как конвертировать SQL в файл Excel (который в дальнейшем можно автоматически предоставлять конечным пользователям различными способами). Вы можете достичь цели, используя всего четыре ноды!
Шаг 1. Получение данных из SQL в n8n
Для создания этого рабочего процесса вам понадобится несколько встроенных Сore-нод. Они управляют тем, как активируется рабочий процесс, и преобразуют данные в ходе выполнения цепочки. Вам также понадобится хотя бы одна нода для соединения n8n с внешней системой (в данном руководстве — с сервером MySQL).
- Стартовая нода активирует процесс, когда вы нажимаете «Execute Workflow».
Далее идёт «Set». В ней объявлена строковая переменная TableName, которая равна concerts2. Нода «Set» позволяет нам автоматически создавать имена файлов и листов в Excel. - Третья нода — «MySQL», которая подключается к удаленной базе данных. Сначала вам нужно настроить учётную запись. Пожалуйста, обратитесь к руководству по импорту CSV в MySQL. Если у вас MS SQL Server, то вам нужно использовать ноду «Microsoft SQL».
- После этого выберите операцию «Execute Query» и введите следующий запрос в качестве выражения:
SELECT * FROM {{ $json["TableName"] }}
Как видите, мы выбираем все записи из таблицы, имя которой мы определили в ноде «Set». Если всё правильно, вы увидите импортированные данные:
Шаг 2. Преобразование таблицы в формат Excel
Наконец, «Move Binary Data» преобразует объект JSON в XLSX-файл. Пожалуйста, проверьте конфигурацию ноды «Move Binary Data»:
Параметр «File Name» является выражением и имеет значение:
{{ $node["TableName"].json["TableName"] }}.{{ $parameter["fileFormat"] }}
Здесь мы генерируем имя файла автоматически на основе:
- имени таблицы, которое было предоставлено в ноде «Set»,
- параметра «File Format». Если изменить формат и выбрать XLS (старый формат Excel), имя файла автоматически изменится на concerts2.xls. Этот трюк может быть полезен при программном экспорте базы данных SQL в Excel.
Параметр «Sheet Name» также является выражением:
{{ $node["TableName"].json["TableName"] }}
Мы убеждаемся, что имя Excel-файла и имя листа совпадают с именем таблицы SQL.
Теперь вы видите, как легко экспортировать SQL-запрос в Excel автоматически!
Подведение итогов
В этом руководстве мы узнали, как преобразовать SQL в Excel с помощью трёх различных методов:
- Мы сделали это непосредственно в Excel с помощью встроенного инструмента для соединения.
- Другой подход заключался в использовании Microsoft SQL Server Management Studio.
- Наконец, мы экспортировали базу данных SQL в Excel через n8n. Это позволило нам достичь нашей цели автоматизированным способом и без использования кода.
Выражаю благодарность Эдуарду Парсаданяну и Юлии Татарниковой за данное руководство.