Sql excel: Выполнять SQL-запросы к файлам Excel — Power Automate

Содержание

Выполнять SQL-запросы к файлам Excel — Power Automate

Статья

02/25/2023

Чтение занимает 3 мин

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

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

Вы также можете реализовать эту функциональность с помощью SQL-запросов, используя только два действия: Открыть SQL-подключение и Выполнять инструкции SQL.

Откройте SQL-подключение к файлу Excel

Перед запуском SQL-запроса вы должны открыть подключение с файлом Excel, к которому вы хотите получить доступ.

Чтобы установить подключение, создайте новую переменную с именем %Excel_File_Path% и инициализируйте его, указав путь к файлу Excel. При желании вы можете пропустить этот шаг и использовать жестко заданный путь к файлу позже в потоке.

Теперь разверните действие Открыть SQL-подключение и заполните следующую строку подключения в его свойствах.

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%Excel_File_Path%;Extended Properties=»Excel 12.0 Xml;HDR=YES»;

Заметка

Для успешного использования представленной строки подключения вам необходимо скачать и установить Распространяемый пакет ядра СУБД Microsoft Access 2010.

Откройте SQL-подключение к файлу Excel, защищенному паролем

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

Для этого запустите файл Excel с помощью действие Запустить Excel. Файл защищен паролем, поэтому введите соответствующий пароль в поле Пароль.

Затем разверните соответствующие действия автоматизации пользовательского интерфейса и перейдите к Файл>Информация>Защита книги>Зашифровать паролем. Дополнительные сведения об автоматизации пользовательского интерфейса и о том, как использовать соответствующие действия можно найти в Автоматизировать классические приложения.

После выбора Зашифровать паролем заполните пустую строку во всплывающем диалоговом окне, используя действие Заполнить текстовое поле в окне. Чтобы заполнить пустую строку, используйте следующее выражение: %»»%.

Чтобы нажать на ОК в диалоговом окне и применить изменения, разверните действие Нажать кнопку в окне.

Наконец, разверните действие Закрыть Excel, чтобы сохранить незащищенную книгу как новый файл Excel.

После сохранения файла следуйте инструкциям в Открытие SQL-подключения к файлу Excel, чтобы открыть к нему подключение.

Когда работа с файлом Excel будут завершена, используйте действие Удалить файлы для удаления незащищенной копии файла Excel.

Чтение содержимого электронной таблицы Excel

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

Более эффективный способ получения определенных значений из электронных таблиц — это рассматривать файлы Excel как базы данных и выполнять на них SQL-запросы. Этот подход быстрее и увеличивает производительность потока.

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

SELECT * FROM [SHEET$]

Заметка

Чтобы применить этот SQL-запрос в ваших потоках, замените заполнитель SHEET именем электронной таблицы, к которой вы хотите получить доступ.

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

SELECT * FROM [SHEET$] WHERE [COLUMN NAME] = 'VALUE'

Заметка

Чтобы применить этот SQL-запрос в ваших потоках, замените:

SHEET с именем электронной таблицы, к которой вы хотите получить доступ.
COLUMN NAME столбцом, содержащим значение, которое вы хотите найти. Столбцы в первой строке листа Excel идентифицируются как имена столбцов таблицы.
VALUE со значением, которое вы хотите найти.

Удалить данные из строки Excel

Хотя Excel не поддерживает SQL-запрос DELETE, вы можете использовать запрос UPDATE, чтобы установить для всех ячеек определенной строки значение NULL.

Точнее, вы можете использовать следующий SQL-запрос:

UPDATE [SHEET$] SET [COLUMN1]=NULL, [COLUMN2]=NULL WHERE [COLUMN1]='VALUE'

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

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

Часть запроса [COLUMN1]=’VALUE’определяет строку, которую вы хотите обновить. В вашем потоке используйте имя столбца и значение в зависимости от того, какая комбинация однозначно описывает строки.

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

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

Удобный способ добиться этого результата — установить для значений нежелательной строки значение NULL, а затем получить все значения, кроме нулевых.

Чтобы изменить значения определенной строки в электронной таблице, вы можете использовать SQL-запрос UPDATE, представленный в Удалить данные из строки Excel:

UPDATE [SHEET$] SET [COLUMN1]=NULL, [COLUMN2]=NULL WHERE [COLUMN1]='VALUE'

Затем выполните следующий SQL-запрос, чтобы получить все строки электронной таблицы, не содержащие значений NULL:

SELECT * FROM [SHEET$] WHERE [COLUMN1] IS NOT NULL OR [COLUMN2] IS NOT NULL

Заполнители COLUMN1 а также COLUMN2 представляют имена всех столбцов для обработки. В этом примере два столбца, но в реальной таблице количество столбцов может быть другим. Все столбцы в первой строке листа Excel идентифицируются как имена столбцов таблицы.

Как выполнить SQL запрос напрямую к таблице Excel – инструкция

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

Инструмент XLTools «SQL запросы» расширяет Excel возможностями языка структурированных запросов:

Создание запросов SQL в интерфейсе Excel и напрямую к Excel таблицам
Автогенерация запросов SELECT и JOIN
Доступны JOIN, ORDER BY, DISTINCT, GROUP BY, SUM и другие операторы SQLite
Создание запросов в интуитивном редакторе с подстветкой синтаксиса
Обращение к любым таблицам Excel из дерева данных

Перед началом работы добавьте «Всплывающие часы» в Excel

«SQL запросы» – это один из 20+ инструментов в составе надстройки XLTools для Excel. Работает в Excel 2019, 2016, 2013, 2010, десктоп Office 365.

– пробный период дает 14 дней полного доступа ко всем инструментам.

Как превратить данные Excel в реляционную базу данных и подготовить их к работе с SQL запросами

По умолчанию Excel воспринимает данные как простые диапазоны. Но SQL применим только к реляционным базам данных. Поэтому, прежде чем создать запрос, преобразуйте диапазоны Excel в таблицу (именованный диапазон с применением стиля таблицы):

Выделите диапазон данных На вкладке «Главная» нажмите Форматировать как таблицу Примените стиль таблицы.
Выберите таблицу Откройте вкладку «Конструктор» Напечатайте имя таблицы.
Напр., «КодТовара».
Повторите эти шаги для каждого диапазона, который планируете использовать в запросах.
Напр., «КодТовара», «ЦенаРозн», «ОбъемПродаж», т.д.
Готово, теперь эти таблицы будут служить реляционной базой данных и готовы к SQL запросам.

Как создать и выполнить запрос SQL SELECT к таблицам Excel

Надстройка «SQL запросы» позволяет выполнять запросы к Excel таблицам на разных листах и в разных книгах. Для этого убедитесь, что эти книги открыты, а нужные данные отформатированы как именованные таблицы.

Нажмите кнопку Выполнить SQL на вкладке XLTools Откроется окно редактора.
В левой части окна находится дерево данных со всеми доступными таблицами Excel.
Нажатием на узлы открываются/сворачиваются поля таблицы (столбцы).
Выберите целые таблицы или конкретные поля.
По мере выбора полей, в правой части редактора автоматически генерируется запрос SELECT.
Внимание: редактор запросов SQL автоматически подсвечивает синтаксис.
Укажите, куда необходимо поместить результат запроса: на новый или существующий лист.
Нажмите «Выполнить» Готово!

Операторы Left Join, Order By, Group By, Distinct и другие SQLite команды в Excel

XLTools использует стандарт SQLite. Пользователи, владеющие языком SQLite, могут создавать самые разнообразные запросы:

LEFT JOIN – объединить две и более таблиц по общему ключевому столбцу
ORDER BY – сортировка данных в выдаче запроса
DISTINCT – удаление дубликатов из результата запроса
GROUP BY – группировка данных в выдаче запроса
SUM, COUNT, MIN, MAX, AVG и другие операторы

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

Как объединить две и более Excel таблиц с помощью надстройки «SQL запросы»

Вы можете объединить несколько таблиц Excel в одну, если у них есть общее ключевое поле. Предположим, вам нужно объединить несколько таблиц по общему столбцу «КодТовара»:

Нажмите Выполнить SQL на вкладке XLTools Выберите поля, которые нужно включить в объединённую таблицу.
По мере выбора полей, автоматически генерируется запрос SELECT и LEFT JOIN.
Укажите, куда необходимо поместить результат запроса: на новый или существующий лист.
Нажмите «Выполнить» Готово! Объединённая таблица появится в считанные секунды.

Запуск SQL-запросов к файлам Excel — Power Automate

Статья
24.02.2023
4 минуты на чтение

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

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

В качестве альтернативы эту функцию можно реализовать с помощью запросов SQL, используя только два действия: Открыть соединение SQL и Выполнить операторы SQL .

Открытие SQL-соединения с файлом Excel

Перед выполнением SQL-запроса необходимо открыть соединение с файлом Excel, к которому вы хотите получить доступ.

Чтобы установить соединение, создайте новую переменную с именем %Excel_File_Path% и инициализируйте ее путем к файлу Excel. При желании вы можете пропустить этот шаг и позже использовать жестко закодированный путь к файлу.

Теперь разверните действие Open SQL connection и заполните следующую строку подключения в его свойствах.

Поставщик=Microsoft.ACE.OLEDB.12.0;Источник данных=%Excel_File_Path%;Расширенные свойства=»Excel 12.0 Xml;HDR=YES»;

Примечание

Для успешного использования представленной строки подключения необходимо загрузить и установить распространяемый компонент Microsoft Access Database Engine 2010.

Открытие SQL-соединения с защищенным паролем файлом Excel

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

Для этого запустите файл Excel с помощью действия Запустить Excel . Файл защищен паролем, поэтому введите соответствующий пароль в поле Пароль .

Затем разверните соответствующие действия автоматизации пользовательского интерфейса и перейдите к File > Info > Protect Workbook > Encrypt with Password . Дополнительную информацию об автоматизации пользовательского интерфейса и о том, как использовать соответствующие действия, можно найти в разделе Автоматизация настольных приложений.

После выбора Зашифровать с помощью пароля заполните пустую строку во всплывающем диалоговом окне с помощью действия Заполнить текстовое поле в окне . Чтобы заполнить пустую строку, используйте следующее выражение: %»»% .

Чтобы нажать кнопку OK в диалоговом окне и применить изменения, разверните действие Нажмите кнопку в окне .

Наконец, разверните действие Close Excel , чтобы сохранить незащищенную книгу как новый файл Excel.

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

Когда работа с файлом Excel завершена, используйте действие Удалить файл(ы) , чтобы удалить незащищенную копию файла Excel.

Чтение содержимого электронной таблицы Excel

Хотя действие Чтение из рабочего листа Excel может считывать содержимое рабочего листа Excel, повторение извлеченных данных циклами может занять значительное время.

Более эффективный способ извлечения определенных значений из электронных таблиц — рассматривать файлы Excel как базы данных и выполнять к ним SQL-запросы. Такой подход быстрее и увеличивает производительность потока.

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

 ВЫБЕРИТЕ * ИЗ [ЛИСТ$]

Примечание

Чтобы применить этот SQL-запрос в ваших потоках, замените SHEET заполнитель с именем электронной таблицы, к которой вы хотите получить доступ.

Чтобы получить строки, содержащие определенное значение в определенном столбце, используйте следующий SQL-запрос:

 SELECT * FROM [SHEET$] WHERE [ИМЯ СТОЛБА] = 'ЗНАЧЕНИЕ'

Примечание

Чтобы применить этот SQL-запрос в ваших потоках, замените:

ЛИСТ на имя электронной таблицы, к которой вы хотите получить доступ.
ИМЯ СТОЛБА со столбцом, содержащим значение, которое вы хотите найти. Столбцы в первой строке рабочего листа Excel идентифицируются как имена столбцов таблицы.
VALUE со значением, которое вы хотите найти.

Удалить данные из строки Excel

Хотя Excel не поддерживает SQL-запрос DELETE , вы можете использовать запрос UPDATE , чтобы установить для всех ячеек определенной строки значение null.

Точнее, вы можете использовать следующий SQL-запрос:

 UPDATE [SHEET$] SET [COLUMN1]=NULL, [COLUMN2]=NULL WHERE [COLUMN1]='VALUE'

Развивая свой поток, вы должны заменить SHEET заполнитель с именем электронной таблицы, к которой вы хотите получить доступ.

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

Часть запроса [COLUMN1]=’VALUE’ определяет строку, которую вы хотите обновить. В своем потоке используйте имя столбца и значение, в зависимости от того, какая комбинация однозначно описывает строки.

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

Удобный способ добиться этого — установить для значений нежелательной строки значение null, а затем получить все значения, кроме нулевых.

Чтобы изменить значения определенной строки в электронной таблице, вы можете использовать SQL-запрос UPDATE , как показано в разделе Удаление данных из строки Excel:

 UPDATE [SHEET$] SET [COLUMN1]=NULL, [COLUMN2]=NULL, ГДЕ [COLUMN1]='VALUE'

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

 SELECT * FROM [SHEET$], ГДЕ [COLUMN1] НЕ NULL ИЛИ [COLUMN2] НЕ NULL

Заполнители COLUMN1 и COLUMN2 представляют имена обрабатываемых столбцов. В этом примере есть два столбца, но в реальной таблице количество столбцов может отличаться. Все столбцы в первой строке рабочего листа Excel идентифицируются как имена столбцов таблицы.

Как использовать операторы SQL в MS Excel | Small Business

Автор: Jeff Grundy

В большинстве электронных таблиц Excel вы вручную вводите данные в ячейки, а затем используете формулы или другие функции для их анализа или выполнения вычислений. Если у вас есть большой источник данных, например база данных Access, база данных SQL Server или даже большой текстовый файл, вы также можете извлечь данные из него с помощью Excel. Использование операторов SQL в Excel позволяет подключаться к внешнему источнику данных, анализировать содержимое полей или таблиц и импортировать данные — и все это без необходимости вводить данные вручную. После того, как вы импортируете внешние данные с операторами SQL, вы можете отсортировать их, проанализировать или выполнить любые вычисления, которые могут вам понадобиться.

Откройте Microsoft Excel, а затем создайте новый файл рабочей книги или откройте существующий файл, к которому вы хотите подключить внешний источник данных.
Щелкните «Данные» на ленте Excel. Щелкните значок «Из других источников» в разделе «Получить внешние данные». Нажмите «Из Microsoft Query» в раскрывающемся меню.
Щелкните тип вашего источника данных в окне «Выбор источника данных». Нажмите и включите параметр «Использовать мастер запросов для создания/редактирования запросов», а затем нажмите «ОК». Сначала появится окно «Подключение к базе данных», а через несколько секунд появится окно браузера «Выбрать файл базы данных».
Перейдите к папке и файлу для вашей базы данных или файла источника данных. Выделите имя файла источника данных и нажмите «ОК». На экране появится окно мастера запросов.
Нажмите и выберите таблицу в источнике данных, содержащую поля, которые вы хотите запросить с помощью SQL и импортировать в электронную таблицу Excel. Нажмите кнопку «>» в середине окна мастера запросов, чтобы заполнить столбцы на панели «Ваш запрос» именами полей из выбранной таблицы в вашем источнике данных. Нажмите кнопку «Далее», чтобы продолжить.
Выберите параметры фильтра для данных, которые нужно извлечь и отобразить в электронной таблице, если хотите. Создавая фильтр для данных в одном или нескольких полях, вы указываете Excel извлекать из источника данных только те данные, которые соответствуют определенным условиям или критериям. Например, если ваш источник данных содержит список клиентов и их контактную информацию, у вас может быть поле в таблице для телефонных номеров. Если вы хотите получить только клиентов из источника данных, у которых есть (919) код города, вы можете сделать это, применив фильтр. Нажмите «Номер телефона» или другое поле с аналогичным названием на панели «Столбец для фильтра» и выберите «содержит» в списке типов фильтра. Введите «919» в следующее поле и нажмите «Далее».
Выберите порядок сортировки по возрастанию или убыванию для записей, извлекаемых из источника данных. Нажмите кнопку «Далее». Включите опцию «Вернуть данные в Microsoft Excel» и нажмите кнопку «Готово».
Щелкните и включите параметр «Таблица» в окне «Импорт данных». Включите параметр «Существующий рабочий лист» и щелкните значок «красная стрелка» справа от поля ячейки под меткой «Существующий рабочий лист». Щелкните и выберите ячейку, в которую вы хотите поместить верхний правый угол таблицы данных, содержащей записи из внешнего источника данных.
Нажмите «ОК». Excel отображает таблицу с извлеченными записями, которые возвращаются в результате базового SQL-запроса источника данных с помощью мастера запросов.
Просмотрите запрос SQL, чтобы узнать, как код извлекает данные из внешнего источника данных. Нажмите «Существующие подключения» на вкладке «Данные». Щелкните значок «Запрос из типа источника данных» в разделе «Подключения в этой книге» окна «Существующие подключения». На экране появится окно импорта данных.
Нажмите кнопку «Свойства». В окне «Свойства подключения» перейдите на вкладку «Определение». Найдите текст в поле Текст команды. Там появляется собственный код SQL-запроса. Для SQL-запроса, извлекающего записи из внешней таблицы с пометкой «Личные_контакты», код будет выглядеть примерно так: Документы\База данных1.accdb .tbl_Personal_Contacts tbl_Personal_Contacts
Нажмите кнопку «ОК», чтобы закрыть окно «Свойства подключения». Отредактируйте другие данные в электронной таблице и сохраните книгу по мере необходимости.

Ссылки

Университет Индианы: Что такое SQL?
Microsoft: использование Microsoft Query для извлечения внешних данных
Chandoo.org: SQL-запросы из Excel
The Code Project: получение данных из Microsoft Excel с использованием SQL

Советы

Вместо подключения к существующему источнику данных вы также можете создать новый источник данных в окне «Выбор источника данных». Если вы выберете опцию «Новый источник данных», вы можете выбрать тип источника данных, который вы хотите подключить, и открыть совместимую программу базы данных, такую как Microsoft Access или Microsoft FoxPro, непосредственно из Excel.