Это неудивительно, ведь данные часто хранятся централизованно в базе данных, и всё чаще пользователям требуется экспортировать таблицы SQL в Excel для дальнейшего анализа.

В этом простом в освоении руководстве рассматриваются три различных способа экспорта данных SQL в Excel: с помощью самого Excel, SSMS и n8n.

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

Как перенести данные из 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, необходимо выполнить несколько дополнительных действий, прежде чем воспроизводить следующие шаги:

1. Установите SQL Server Express LocalDB с сайта Microsoft
2. Загрузите и образец базы данных SQL (например, AdventureWorks), как описано в руководстве. Наконец, откройте SQL Server Configuration Manager и проверьте Named Pipes. Строка будет выглядеть примерно так
   \\\.\pipe\MSSQL$SQLEXPRESS\sql\query
3. Эта информация нужна, если у вас локальная установка 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.

Экспорт 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. Это позволило нам достичь нашей цели автоматизированным способом и без использования кода.

Выражаю благодарность Эдуарду Парсаданяну и Юлии Татарниковой за данное руководство.

Энэйтэн Эппсмитов