Microsoft Learn Challenge

ExcelファイルでSQLクエリを実行する – Power Automate

この記事では、Excelの自動化を効率よく行うためにSQLクエリを使用する方法について説明しています。特定の値を含むExcelのレジストリを変更する場合、SQLクエリを使えば、二つのアクションだけで済みます。パスワード保護されたExcelファイルへの接続は、まず保護を解除する必要があります。さらに、Excelデータを取得する際は、SQLクエリを利用することで迅速に必要な情報を得ることが可能です。また、特定の行を除外してデータを取得する方法も記載されています。

Excelファイルに対するSQLクエリの活用方法

Excelは多くの業務シーンで利用されており、データの管理や分析に役立ちますが、大量のデータを処理する際には限界があります。そこで、SQLクエリを活用することで、Excelデータの操作をより効率的に行う方法を紹介します。

SQL接続の構築

まず、SQLクエリを実行するには、Excelファイルへの接続を開く必要があります。接続を確立するためには、次の接続文字列を使用します。

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

この接続でExcelファイルを開くことができれば、その後のクエリ実行がスムーズになります。

パスワード保護されたExcelファイルへの接続

パスワードで保護されたExcelファイルに対してSQLクエリを実行する場合は、少し異なるアプローチが必要です。まずは、Excelファイルを「Launch Excel」アクションを使用して開き、パスワードを入力します。その後、UI自動化アクションを使用して、ファイルの保護を解除し、クエリの実行が可能です。

Excelスプレッドシートの内容を読み取る

SQLクエリを使用してExcelスプレッドシートの内容を効率的に取得することができます。例えば、特定のシートの全内容を取得するためには、次のSQLクエリを使用します。

SELECT * FROM [SHEET$]

特定のカラムで特定の値を持つ行を取得したい場合は、以下のクエリを利用します。

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

Excel行のデータ削除

ExcelにはSQLの「DELETE」クエリはありませんが、「UPDATE」クエリを使用して特定の行のセルをNULLに設定することが可能です。例えば、

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

このようにすることで、指定した行のデータを削除したことになります。

特定の行を除いたデータの取得

場合によっては、特定の行を除いた全データを取得したいことがあります。具体的には、特定の行の値をNULLに設定した後、NULLではないものを取得するクエリを使用します。

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

結論

SQLクエリを利用することで、Excelファイルのデータ操作は格段に効率的になります。SQLの強力な機能を活用することで、複雑なデータ処理を簡潔に行えることが強みです。Power Automateなどのツールを使用することで、このプロセスを自動化し、ビジネスプロセスを効率化することができるでしょう。

————-

Run SQL queries on Excel files – Power Automate

Source link

The article discusses how to utilize SQL queries for efficient automation of Excel data manipulation. While Excel actions can handle various automation scenarios, SQL queries provide a more streamlined and efficient method, especially when dealing with large datasets.

Key Points:

  1. Connecting to Excel Files:

    • To run SQL queries, you first need to establish a connection to the Excel file using the "Open SQL connection" action.
    • The connection string can be configured to connect to a regular Excel file or a password-protected one. If the file is protected, you’ll need to unlock it by removing the password before creating the SQL connection.

  2. Executing SQL Queries:

    • SQL queries can efficiently read from, manipulate, and delete data from Excel files.
    • To read all data from a specific sheet, use the query: SELECT * FROM [SHEET$].
    • If you need to filter rows based on certain criteria, the syntax is: SELECT * FROM [SHEET$] WHERE [COLUMN NAME] = 'VALUE'.

  3. Updating and Deleting Data:

    • Since Excel does not allow the use of the SQL DELETE statement, you can use the UPDATE query to set specific row values to NULL, effectively removing them. Example: UPDATE [SHEET$] SET [COLUMN1]=NULL, [COLUMN2]=NULL WHERE [COLUMN1]='VALUE'.
    • After updating unwanted rows to NULL, you can retrieve all remaining rows with: SELECT * FROM [SHEET$] WHERE [COLUMN1] IS NOT NULL OR [COLUMN2] IS NOT NULL.

  4. Handling Password-Protected Files:

    • To work with password-protected files, one must launch the file in Excel, remove the encryption, and save it as a non-protected version before proceeding with SQL operations.
  5. Performance Efficiency:
    • Using SQL queries instead of traditional looping structures significantly increases performance, allowing users to manipulate large datasets swiftly.

In summary, leveraging SQL queries in Excel automation offers streamlined, efficient data manipulation and retrieval through fewer actions and reduced complexity compared to traditional Excel actions.

「Microsoft 365 Copilot における研究者およびアナリストの紹介」 前の記事 Power BI PremiumからMicrosoft Fabricへの移行に関する猶予期間 | Microsoft Power BIブログ 次の記事

関連記事