概要
「CSVファイルのデータテーブルをSQLで扱いたい」といった質問をPower Automate for desktopのフォーラムで何度となく見かけました。しかし明確な解決方法が書かれていることを見たことがありません。Learnには「CSV ファイルを Excel スプレッドシートに変換」記述と「Excel ファイルで SQL クエリを実行する」がありますがCSVのファイルのデータテーブルを直接SQL操作する記述はありません。私自身もPowerShellを使って処理していました。カスタムアクション作ろうかと思ったのですが、その前にもう一度検証したところODBCドライバ経由で普通に操作できるではないですか。そこで今回はデータベースアクションでCSVファイルのデータテーブルをSQL操作する方法を紹介します。
注意事項、実行環境
- 2023年5現在、Power Automate for desktopのLearnでは見当たらないで内容ですので自己責任でお願いいたします。
- そもそも暗黙知レベルでしたらごめんなさい。
- 様々な条件を検証していないので、試した方はレスで教えていただけたら嬉しいです。
- Windows 11 Home 22H2
- Powwer Automate for desktop 2.32.111.23124 インストール版
- Microsoft 365 開発者プログラム
重要なポイント
対象CSVを開いたままだとエラーになります。フローを動かすときは必ず閉じておく必要があります。
検証１SELECT WHEREなどフィルター処理
-
サンプルデータを用意します。今回はKaggleのwinemag-data_first150k.csvで検証しました。任意で用意して頂ければと思います。私の用途では15万行くらい動けば問題ないのでこのデータをお借りして試しました。
-
ドキュメントフォルダーに「SampleCSV」フォルダーを作成し格納します。
-
winemag-data_first150k.csvのコピーを作成しファイル名から記号を削除したものを用意します。理由は後述。
-
フローを作成します
-
「SQL接続を開く」アクションで接続文字列を作成します。「Microsoft OLE Provider for ODBC Drivers]を選択して「次へ」
-
データリンクプロパティが開いたら「接続文字列使用する」を選択し「ビルド」
-
先ほどの「SampleCSV」フォルダーを指定して新規作成
Microsoft Access Text Driver(.txt,.csv)を選択して「次へ」
-
データソースの新規作成で適宜「参照」から適宜名前を付けて「保存」
-
「次へ」で進み完了を押して詳細を設定します。
-
オプションを展開します
-
「現在のフォルダーを使用する」のチェックを外しCSVを格納するフォルダーを指定します。今回は「SampleCSV」
-
「書式の定義」に移動します。
-
CSVに合わせて各項目を変更しておきます。SHIFT-JISの場合はANSIを選ばないと文字化けします。今回は列名の見出しがあるのでチェックを付け、日本語でないのでOEMにしました。推量をクリックすると列データの型が推察されるようです。なおデフォルトはANSIで列名の見出しにチェックを付けました。
-
接続文字列が入っているのが確認できました。
-
OKで進むと「SQLを開く」アクションに接続文字列が記述されます。
-
「SQLステートメントの実行」アクションに記述してみます。少しデータが多いので列と行をフィルター次のようにフィルターしました。FROM節でCSV名を指定することができます。
-
「SQL接続を閉じる」アクションで接続を閉じます。
-
動作確認します。うまく動作しそうです。
-
記号付きのCSVを試してみます。
-
ファイル名に記号（－）が含まれるとエラーになりました。（アンダーバー「_」）だけ混ざったファイル名も試したところ使えるようです。その他の記号は試してないので注意が必要です。どなたか知見があればご連絡いただければ幸いです。この辺にありそうで見つけられませんでした。また記号ファイルやファイル名に予約語が含まれる場合FROM句以降のファイル名をダブルクォーテーション「”」で囲むことで動作しました。
検証2 複数テーブルを結合してみる
-
検証1で使用したSampleCSVフォルダーにサンプルデータを追加します。今回は次のような簡単なデータをmaster.csvと
transaction.csvして保存しました。この二つのテーブルを結合してみます。
-
フローを作成します
-
「SQLの接続を開く」アクションの接続文字列は検証1の文字列をそのまま使います。
検証１で使った「ＳＱＬ接続を開く」アクションをコピペしました。
-
「SQLステートメントの実行」アクションに商品コードに商品名、単価などのマスタデーターを結合するように書いてみます。テーブルの指定はCSVのファイル名です。また+5日ShippingDate列とsubtotal列を追加しようと思います。
SELECT T1.orderID ,T1.date AS orderDate, T1.date +5 AS shippingDate, T1.code, T2.name , T2.price, T1.value, (T2.price*T1.value) AS subtotal FROM "transaction.csv" AS T1 LEFT JOIN "master.csv" AS T2 ON T1.code=T2.code; -
「SQL接続を閉じる」アクションで接続を閉じます。
結果
普通につかえるじゃないか・・・。
まとめ
CSVファイルのデータテーブルをODBCドライバ経由でデータベースアクションを用いて操作できました。決まったフォルダーにデータCSVを入れておけば扱える点が非常に便利だと思いました。しかしPower Automate for desktopのLearnには記事がないので困った人も多いのではないでしょうか。フォーラムでもSQL CSV等の検索ワードではまともな回答が見つからなかったので使えないと思い込んでいました。（もしかして自分だけ？）
今回のハマりどころは「CSVファイルを開いているとフロー実行ではエラーになる」と、「マイナス記号がファイル名に含まれるとエラーになる」でした。
世間ではAIだのCopilotなどと騒がれていますが私の生息する区域ではCSVが飛び交うんですよ・・・APIでつながる世界線とかどこにあるんですか・・・。いまさら何やってるんだよとか言わないで下さい！泣きます。
ついでにお願いです。CSVをPower Automate for desktopのSQLアクションで扱う知見が少ないのでこうしたほうが良いとか、ここおかしいとか、こんなの試したとかレス頂けたら嬉しです。 おわり