Sql запрос в excel: Выполнять SQL-запросы к файлам Excel — Power Automate
Содержание
Выполнять SQL-запросы к файлам Excel — Power Automate
-
Статья -
- Чтение занимает 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»;
Note
Для успешного использования представленной строки подключения вам необходимо скачать и установить Распространяемый пакет ядра СУБД 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$]
Note
Чтобы применить этот SQL-запрос в ваших потоках, замените заполнитель SHEET именем электронной таблицы, к которой вы хотите получить доступ.
Чтобы получить строки, содержащие определенное значение в определенном столбце, используйте следующий запрос SQL:
SELECT * FROM [SHEET$] WHERE [COLUMN NAME] = 'VALUE'
Note
Чтобы применить этот 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
Note
Заполнители COLUMN1 а также COLUMN2 представляют имена всех существующих столбцов. В этом примере столбцов два, но в реальной таблице количество столбцов может быть другим. Все столбцы в первой строке листа Excel идентифицируются как имена столбцов таблицы.
Создание запроса с параметрами в Microsoft Query
Excel для Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Еще…Меньше
При запросе данных в Excel можно использовать входное значение ( параметр), чтобы указать что-то о запросе. Для этого нужно создать запрос с параметрами в Microsoft Query.
-
Параметры используются в предложении WHERE запроса— они всегда работают в качестве фильтра для извлечения данных.
-
Параметры могут запрашивать у пользователя входное значение при запуске или обновлении запроса, использовать константы в качестве входного значения или использовать содержимое указанной ячейки в качестве входного значения.
-
Параметр является частью запроса, который он изменяет, и его нельзя повторно использовать в других запросах.
Примечание Если вы хотите создать запросы с параметрами другим способом, см. создание запроса с параметрами (Power Query).
Последовательность действий
-
Щелкните Данные > Получить & Преобразование данных > Получить данные > из других источников > из Microsoft Query.
-
Следуйте шагам мастера запросов. На экране Мастер запросов — готово выберите Просмотр данных или изменение запроса в Microsoft Query и нажмите кнопку Готово. Откроется окно Microsoft Query и отобразит запрос.
-
Нажмите кнопку> SQL.
В диалоговом SQL найдите предложение WHERE — строку, которая начинается со слова WHERE, обычно в конце SQL кода. Если предложение WHERE не существует, добавьте его, введя WHERE в новой строке в конце запроса.
-
После where введите имя поля, оператор сравнения (=, <, >, LIKE и т. д.) и одно из следующих данных:
-
Для запроса generic parameter (?) введите вопросии (?). В подсказке, которая появляется при запуске запроса, не отображается полезная фраза.
-
Введите фразу, заключенную в квадратные скобки, для запроса параметра, который помогает вводить допустимые данные.
Фраза отображается в запросе с параметрами при запуске запроса.
-
-
Завершив добавление условий с параметрами в предложение WHERE, нажмите кнопку ОК, чтобы запустить запрос. Excel запрос на в качестве значения для каждого параметра, Microsoft Query отобразит результаты.
-
Когда вы будете готовы загрузить данные, закройте окно Microsoft Query, чтобы вернуться к Excel. Откроется диалоговое окно Импорт данных.
-
Чтобы просмотреть параметры, нажмите кнопку Свойства. Затем в диалоговом окне Свойства подключения на вкладке Определение нажмите кнопку Параметры.
-
В диалоговом окне Параметры отображаются параметры, используемые в запросе. Выберите параметр в области Имя параметра, чтобы просмотреть или изменить параметр How value is obtained. Вы можете изменить запрос параметра, ввести определенное значение или указать ссылку на ячейку.
-
Нажмите кнопку ОК, чтобы сохранить изменения и закрыть диалоговое окно Параметры, а затем в диалоговом окне Импорт данных нажмите кнопку ОК, чтобы отобразить результаты запроса Excel.
Теперь в книге есть запрос с параметрами. При запуске запроса или обновлении подключения к данным Excel проверяет параметр, чтобы завершить предложение WHERE запроса. Если параметр запросит значение, Excel отобразит диалоговое окно Введите значение параметра для сбора входных данных. Вы можете ввести значение или щелкнуть ячейку со значением. Вы также можете указать, что указанное значение или ссылка всегда должны использоваться, а при использовании ссылки на ячейку можно указать, что Excel должно автоматически обновлять подключение к данным (то есть повторно выполнить запрос) при внесении изменений в указанную ячейку.
Настройка запроса с параметрами
Создание запроса с параметрами (Power Query)
Создание раскрывающегося списка
Используйте возможности SQL для выполнения запросов в Excel и непосредственно к таблицам Excel
Часто таблицы Excel постепенно превращаются в большие неудобные рабочие книги. Поиск дубликатов, фильтрация, группировка, сложная сортировка, объединение таблиц в одну и т. д. — становятся настоящим вызовом. Потенциально эти задачи можно было бы легко выполнить с помощью SQL… если бы только можно было выполнять SQL-запросы к данным Excel.
Надстройка XLTools SQL Queries расширяет возможности Excel с помощью языка структурированных запросов:
Запуск SQL-запросов в интерфейсе Excel и непосредственно в таблицах Excel
Автоматически генерировать операторы SELECT и JOIN
Используйте JOIN, ORDER BY, DISTINCT, GROUP BY, SUM и другие операторы SQLite
Написание запросов в интуитивно понятном редакторе с подсветкой синтаксиса
Адресация любых таблиц Excel из древовидного списка
Прежде чем начать, добавьте SQL-запросы в Excel
SQL-запросы — это одна из более чем 20 функций надстройки XLTools для Excel. Работает в Excel 2019, 2016, 2013, 2010, настольном Office 365.
— бесплатная пробная версия дает вам 14 дней полного доступа ко всем функциям.
Как превратить данные Excel в реляционную базу данных и подготовиться к запросам SQL
По умолчанию Excel представляет данные в виде простых диапазонов данных. Но SQL работает только с реляционными источниками данных. Поэтому, прежде чем начать, обязательно отформатируйте диапазоны Excel как таблицы (именованные диапазоны с примененным стилем таблицы):
Выберите диапазон данных На вкладке Главная щелкните Форматировать как таблицу Выберите стиль таблицы.
Выберите таблицу. Откройте вкладку «Проект». Введите имя таблицы.
«ID продукта»
Повторите эти шаги для каждого диапазона данных, который вы планируете использовать в запросах SQL.
таблицы «RetailPrice», «SalesVolume» и др.
Готово, теперь эти таблицы будут служить реляционной базой данных и готовы к запросам.
Как создать и запустить SQL SELECT для таблиц Excel
Надстройка SQL Queries позволяет выполнять запросы к таблицам Excel, расположенным на разных листах и в разных книгах. Убедитесь, что эти книги открыты, а необходимые данные отформатированы как именованные таблицы.
Нажмите кнопку Execute SQL на вкладке XLTools. Откроется окно редактора.
С левой стороны найдите древовидное представление всех доступных таблиц.
Нажмите на узлы, чтобы развернуть/свернуть поля таблицы (столбцы).
Выберите целые таблицы или отдельные поля.
Когда вы отмечаете поля, в правой части редактора автоматически создается оператор 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 Queries
Вы можете объединить несколько таблиц Excel в одну, если они имеют общее поле ключа. Предположим, вам нужно объединить несколько таблиц, которые имеют общий столбец «ProductID»:
Нажмите «Выполнить SQL» на вкладке XLTools.
Выберите поля, которые вы хотите видеть в объединенной таблице.
Когда вы отмечаете поля, SELECT и LEFT JOIN генерируются автоматически.
Выберите, следует ли поместить вывод запроса на новый или существующий рабочий лист.
Нажмите «Выполнить» Готово! Объединенная таблица появится за считанные секунды.
Запуск SQL-запросов к файлам Excel — Power Automate
- Статья
- 4 минуты на чтение
Хотя действия Excel могут обрабатывать большинство сценариев автоматизации Excel, запросы SQL могут более эффективно извлекать и обрабатывать значительные объемы данных Excel.
Предположим, поток должен изменить только те реестры Excel, которые содержат определенное значение. Чтобы реализовать эту функциональность без SQL-запросов, вам потребуются циклы, условные операторы и несколько действий Excel.
Наоборот, вы можете реализовать эту функциональность с запросами SQL, используя только два действия, Действие «Открыть соединение SQL» и действие «Выполнить операторы SQL» .
Открытие SQL-соединения с файлом Excel
Перед выполнением SQL-запроса необходимо открыть соединение с файлом Excel, к которому вы хотите получить доступ.
Чтобы установить соединение, создайте новую переменную с именем %Excel_File_Path% и инициализируйте ее путем к файлу Excel. При желании вы можете пропустить этот шаг и использовать жестко заданный путь к файлу позже в потоке.
Теперь разверните действие Open SQL connection и заполните следующую строку подключения в его свойствах.
Provider=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 с помощью действия Launch Excel . Файл защищен паролем, поэтому введите соответствующий пароль в поле Пароль .
Затем разверните соответствующие действия автоматизации пользовательского интерфейса и перейдите к Файл > Информация > Защитить книгу > Зашифровать паролем . Дополнительную информацию об автоматизации пользовательского интерфейса и о том, как использовать соответствующие действия, можно найти в разделе Автоматизация потоков рабочего стола.
После выбора Зашифровать с помощью пароля заполните пустую строку во всплывающем диалоговом окне с помощью действия Заполнить текстовое поле в Windows . Чтобы заполнить пустую строку, используйте следующее выражение: %»»% .
Чтобы нажать кнопку OK в диалоговом окне и применить изменения, разверните действие Нажмите кнопку в окне .
Наконец, разверните действие Close Excel , чтобы сохранить незащищенную книгу как новый файл Excel.
После сохранения файла следуйте инструкциям в разделе Открытие подключения SQL к файлам Excel, чтобы открыть подключение к нему.
Когда работа с файлом Excel завершена, используйте кнопку Удалить файл(ы) Действие по удалению незащищенной копии файла Excel.
Чтение содержимого электронной таблицы Excel
Хотя действие Чтение с рабочего листа Excel может прочитать содержимое рабочего листа Excel, повторение извлеченных данных циклами может занять значительное время.
Более эффективный способ извлечения определенных значений из электронных таблиц — рассматривать файлы Excel как базы данных и выполнять к ним SQL-запросы. Такой подход быстрее и увеличивает производительность потока.
Чтобы получить все содержимое электронной таблицы, вы можете использовать следующий SQL-запрос в действии Выполнить операторы SQL .
ВЫБЕРИТЕ * ИЗ [ЛИСТ$]
Примечание
Чтобы применить этот SQL-запрос в потоках, замените заполнитель SHEET на имя электронной таблицы, к которой вы хотите получить доступ.
Чтобы получить строки, содержащие определенное значение в определенном столбце, используйте следующий запрос SQL:
SELECT * FROM [SHEET$] WHERE [ИМЯ СТОЛБЦА] = 'ЗНАЧЕНИЕ'
Примечание
Чтобы применить этот SQL-запрос в ваших потоках, замените:
- ЛИСТ на имя электронной таблицы, к которой вы хотите получить доступ
- ИМЯ СТОЛБА со столбцом, содержащим значение, которое вы хотите найти.
Столбцы в первой строке рабочего листа Excel идентифицируются как имена столбцов таблицы.
- ЗНАЧЕНИЕ со значением, которое вы хотите найти
Удалить данные из строки Excel
Хотя Excel не поддерживает DELETE SQL-запрос, вы можете использовать запрос UPDATE , чтобы установить для всех ячеек определенной строки значение null.
Точнее, вы можете использовать следующий SQL-запрос:
UPDATE [SHEET$] SET [COLUMN1]=NULL, [COLUMN2]=NULL WHERE [COLUMN1]='VALUE'
При разработке вашего потока вы должны заменить заполнитель SHEET именем электронной таблицы, к которой вы хотите получить доступ.
Заполнители COLUMN1 и COLUMN2 представляют имена всех существующих столбцов. В этом примере столбцов два, но в реальном сценарии количество столбцов может быть другим. Столбцы в первой строке рабочего листа Excel идентифицируются как имена столбцов таблицы.