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

Excel アクションはほとんどの Excel 自動化シナリオを処理できますが、SQL クエリは大量の Excel データをより効率的に取得および操作できます。

フローが特定の値を含む Excel レジストリのみを変更する必要があるとします。 SQL クエリを使用せずにこの機能を実現するには、ループ、条件、および複数の Excel アクションが必要になります。

ただし、SQL クエリを使えば、SQL 接続を開くおよび SQL ステートメントの実行という 2 つのアクションのみを使用するだけでこの機能を実装することができます。

Excel ファイルへの SQL 接続を開く

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 Database Engine2010 再配布可能をダウンロードしてインストールする必要があります。

パスワードで保護された Excel ファイルへの SQL 接続を開く

パスワードで保護された Excel ファイルに対して SQL クエリを実行するシナリオでは、別の方法が必要です。 SQL 接続を開くアクションはパスワードで保護された Excel ファイルに接続できないため、保護を削除する必要があります。

これを実現するには、Excel の起動アクションを使用して Excel ファイルを起動します。 ファイルはパスワードで保護されているため、適切なパスワードをパスワード フィールドに入力します。

次に、適切な UI 自動化アクションをデプロイし、ファイル>情報>ブックの保護>パスワードで暗号化に移動します。 UI オートメーションの詳細と、それぞれのアクションの使用方法については、デスクトップ アプリケーションを自動化するを参照してください。

パスワードを使用して暗号化を選択した後で、ウィンドウ内のテキスト フィールドに入力するアクションを使用してポップアップ ダイアログに空の文字列を入力します。 空の文字列を入力するには、式%""%を使用します。

ダイアログのOKボタンを押すには、変更を適用し、ウィンドウ内のボタンを押すアクションをデプロイします。

最後に、Excel を閉じるアクションをデプロイして、保護されていないブックを新しい Excel ファイルとして保存します。

ファイルを保存した後、Excel ファイルへの SQL 接続を開くの手順に従って、そのファイルへの接続を開きます。

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 は DELETE SQL クエリをサポートしていませんが、UPDATE クエリを使用して特定の行のすべてのセルを null 値に設定できます。

より正確には、次の SQL クエリを使用できます。

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

フローを開発する際、SHEET プレースホルダーを、アクセスするスプレッドシートの名前に置き換える必要があります。

COLUMN1 および COLUMN2 プレースホルダーは、処理する列の名前を表します。 この例では列は 2 つですが、実際のシナリオでは列の数が異なる場合があります。 Excel ワークシートの最初の行の列は、テーブルの列名として識別されます。

クエリの COLUMN1='VALUE' の部分は、更新する行を定義します。 フローでは、列名と、行を一意に記述する組み合わせに基づく値を使用します。

特定の行を除く Excel データを取得する

シナリオによっては、特定の行を除外して、Excel スプレッドシートのすべてのコンテンツを取得することが必要になる場合があります。

これを実現するための便利な方法は、不要な行の値を null 値に設定してから、null 値以外のすべての値を取得することです。

スプレッドシートの特定の行の値を変更するには、Excel 行のデータの削除に提示されているように、UPDATE SQL クエリを使用できます。

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 プレースホルダーは、処理する列の名前を表します。 この例では列は 2 つですが、実際のテーブルでは列の数が異なる場合があります。 Excel ワークシートに含まれる最初の行の列はすべて、テーブルの列名として識別されます。