PowerAutomateDesktopでCSVをデータベースに

開発
Irvin John MabliによるPixabayからの画像
2021.03.26

※この記事にはプロモーションが含まれています。

今回は、Power Automate Desktopを使って、CSVファイルのデータを、コンテナ起動したマイクロソフトSQLサーバーに格納する手順を紹介します。

スポンサーリンク
スポンサーリンク

環境構築

Power Automate Desktop環境の準備

Windows 10上にPower Automate Desktopをインストールします。

Windows 10へのPower Automate Desktopインストールの詳細手順は、無料RPA最有力！Power Automate Desktopを使って自動化を参考にしてください。

MSSQLServer Expressの準備

MSSQLServer Expressは、WSL2からLinuxベースのDockerコンテナを使用します。

WSL2上でのDocker環境構築詳細手順については、Docker Desktop for Windowsでコンテナ起動を参考にしてください。

Docker環境が出来たら、下記コマンドでイメージをPullします。

docker pull mcr.microsoft.com/mssql/server:2019-latest

次に、下記コマンドでMSSQLServer Expressコンテナを実行します。

docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=P@ssw0rd' -e 'MSSQL_PID=Express' -p 1433:1433 -d mcr.microsoft.com/mssql/server:2019-latest

起動していることを確認します。

> docker ps

CONTAINER ID IMAGE                                      COMMAND                 CREATED         STATUS         PORTS                  NAMES
774d20ee5ed0 mcr.microsoft.com/mssql/server:2019-latest "/opt/mssql/bin/perm…" 22 seconds ago Up 22 seconds 0.0.0.0:1433->1433/tcp frosty_lalande

CSVファイルの準備

下記CSVファイルをローカルフォルダに格納します。CSVファイルは2つ用意しました。UTF-8エンコードで保存しています。

日本語,こんにちは,200
英語,Hello,100
中国語,你好,333

ドイツ語,Guten Tag,555
ロシア語,Здравствуйте,789

データベース・テーブル作成

PowerShellから下記を実行し、データベースtestdbおよびテーブルsampleを作成します。

> docker exec -it <<container id>> /bin/bash
> /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P P@ssw0rd
1> USE master
1> CREATE DATABASE testdb
1> GO
1> USE testdb
1> CREATE TABLE sample(name nchar(32), greeting nchar(32), num INT);
1> GO
1> QUIT
> exit

なお、もしデータベースおよびテーブルの作成をやり直したい場合は、下記必要なコマンドをGOして一旦削除します。

  • DROP TABLE sample;
  • DROP DATABASE testdb;

フロー作成

それでは、フローを作成してみます。

SQL接続オープン

まず、このフローではファイルとデータ分だけループするので、ループの外側でデータベースへの接続処理を行います。”アクション”から[データベース] > [SQL接続を開く]を指定し、下記パラメーターを設定します。接続情報は変数SQLConnectionの格納されるものとします。

  • プロバイダー
    • 接続するデータ選択：Miscrosoft OLE DB Provider for SQL Server
  • 接続
    • サーバー名選択：localhost
    • 特定のユーザー名とパスワード
      • ユーザー名：SA
      • パスワード：P@ssw0rd
        • “パスワードを保存する”にチェックする
    • サーバー上のデータベースを選択：testdb

CSVファイル一覧取得

次に、特定フォルダ(Ex. \tmp\PAD_test\csv2db)に格納されているCSVファイルを取得します。”アクション”から[フォルダー] > [フォルダー内のファイルを取得]を指定し、下記パラメーターを設定します。ファイル一覧は変数Filesに格納します。

  • 全般
    • フォルダー：E:\tmp\PAD_test\csv2db
    • ファイルフィルター：*.csv
    • サブフォルダを含める：(任意)

ファイル単位の処理

全てのファイルを処理するように、ファイルループを作ります。”アクション”から[ループ] > [For each]を指定し、下記パラメーターを設定します。各ファイル名は、変数CurrentItemに格納されます。

  • パラメータの選択
    • 反復処理を行う値：%Files%

CSVデータ読み込み

CSVファイルから、データを読み出します。”アクション”から[ファイル] > [CSVを読み取ります]を指定し、下記パラメーターを設定します。読み込んだデータは、2次元配列変数CSVTableに格納します。

  • 全般
    • ファイルパス：%CurrentItem%
    • エンコード：UTF-8

行単位の処理

1つのCSVファイル内の複数行データを読み出すためのデータループを作ります。”アクション”から[ループ] > [Loop]を指定し、下記パラメーターを設定します。先の配列CSVTableからデータが読み出しやすいように、開始点を0、終点を行数-1に設定しています。データを変数LoopIndexはループ処理のインデックスを示します。

  • パラメーターの選択
    • 開始値：0
    • 終了：%CSVTable.RowsCount – 1%
    • 1

SQLクエリ構成

データベースにデータを挿入するためのSQL構文を変数NewVarに作成します。”アクション”から[変数] > [変数の設定]を指定し、下記パラメーターを設定します。SQL実行の処理内に記載しないのは、変数を扱う際に余計な文字列編集されることを防ぐためです。LoopIndexを用いて、CSVTableから1行分のデータを抜き出し、データベースに書き込む命令を構成しています。Nプリフィックスは日本語等を扱うための処理です。

  • 変数の設定
    • 設定：NewVar
    • 宛先：INSERT INTO sample VALUES(N’%CSVTable[LoopIndex][0]%’ , N’%CSVTable[LoopIndex][1]%’ , N’%CSVTable[LoopIndex][2]%’)

SQL実行

上で構成したSQL文を実行します。”アクション”から[データベース] > [SQLステートメントの実行]を指定し、下記パラメーターを設定します。

  • パラメーターの選択
    • 接続の取得方法：SQL接続変数
    • SQL接続：%SQLConnection%
    • SQLステートメント：%NewVar%
    • タイムアウト：30 (任意)

SQL接続クローズ

最後にループの一番外側で、SQL接続情報をクローズします。”アクション”から[データベース] > [SQL接続を閉じる]を指定し、下記パラメーターを設定します。

  • 全般
    • SQL接続：%SQLConnection%

完成フロー

これで、フローは完成です。

  • 完成フロー

フロー実行

それでは、上部の右三角ボタンをクリックして、フローを実行します。

エラー無くフローが完了したら、PowerShellから下記を実行し、データベースにデータが格納されているか確認します。データが5件、保存されていることが分かります。

> docker exec -it <<container id>> /bin/bash
> /opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P P@ssw0rd
1> USE testdb
1> select * from sample;
1> GO

name                             greeting                         num
-------------------------------- -------------------------------- -----------
日本語                           こんにちは                       200
英語                             Hello                            100
中国語                           你好                             333
ドイツ語                         Guten Tag                        555
ロシア語                         Здравствуйте                     789

1> QUIT
> exit

まとめ

今回は、蓄積されるCSVファイルをデータベースに格納するサンプルを紹介しました。処理が終わったCSVファイルの移動、トリガー(フローが開始する条件)や大容量向けにデータベースのコミット改善を行うと、更に実用的になるでしょう。日々の運用で定型のデータを扱う場面がある場合は、Power Automate Desktopが支援してくれる機会も少なからずあるかと思います。

以上、RPAでCSVをコンテナMSSQLサーバーに格納手順の紹介でした。

コメント