#PowerAutomate でCSVデータをJSON化する

いろんなところでCSV形式のデータって公開されていたり、あるいはシステム間連携でCSV形式でデータを渡して、夜間に取り込みとかありますよね。
そういった定期処理であれば使えるかもしれない、CSVからJSONへの変換についてご紹介します。

※定期処理でJSON作ってどう使うかはあまり思いつかないですが、JSON形式にしてtxtファイルにしておけば、あとでstring to jsonですぐデータに戻せるから、データソースにもできるかもです。

セットアップ

用意するCSVは以下のようなものです。

input.txt

permalink,company,numEmps,category,city,state,fundedDate,raisedAmt,raisedCurrency,round
lifelock,LifeLock,,web,Tempe,AZ,1-May-07,6850000,USD,b
lifelock,LifeLock,,web,Tempe,AZ,1-Oct-06,6000000,USD,a
lifelock,LifeLock,,web,Tempe,AZ,1-Jan-08,25000000,USD,c
mycityfaces,MyCityFaces,7,web,Scottsdale,AZ,1-Jan-08,50000,USD,seed

テーブルで表すと

permalink	company	numEmps	category	city	state	fundedDate	raisedAmt	raisedCurrency	round
lifelock	LifeLock		web	Tempe	AZ	1-May-07	6850000	USD	b
lifelock	LifeLock		web	Tempe	AZ	1-Oct-06	6000000	USD	a
ifelock	LifeLock		web	Tempe	AZ	1-Jan-08	25000000	USD	c
mycityfaces	MyCityFaces	7	web	Scottsdale	AZ	1-Jan-08	50000	USD	seed

これをPower Automateで加工して、以下のようなJSON Arrayを得ることを目指します。

out.json

[
  {
    "permalink": "lifelock",
    "company": "LifeLock",
    "numEmps": "",
    "category": "web",
    "city": "Tempe",
    "state": "AZ",
    "fundedDate": "1-May-07",
    "raisedAmt": "6850000",
    "raisedCurrency": "USD",
    "round": "b"
  },
  {
    "permalink": "lifelock",
    "company": "LifeLock",
    "numEmps": "",
    "category": "web",
    "city": "Tempe",
    "state": "AZ",
    "fundedDate": "1-Oct-06",
    "raisedAmt": "6000000",
    "raisedCurrency": "USD",
    "round": "a"
  },
  {
    "permalink": "lifelock",
    "company": "LifeLock",
    "numEmps": "",
    "category": "web",
    "city": "Tempe",
    "state": "AZ",
    "fundedDate": "1-Jan-08",
    "raisedAmt": "25000000",
    "raisedCurrency": "USD",
    "round": "c"
  },
  {
    "permalink": "mycityfaces",
    "company": "MyCityFaces",
    "numEmps": "7",
    "category": "web",
    "city": "Scottsdale",
    "state": "AZ",
    "fundedDate": "1-Jan-08",
    "raisedAmt": "50000",
    "raisedCurrency": "USD",
    "round": "seed"
  },
..
]

これまではApply to eachをネストする方法が考えられていたのですが、あまり多重でループ処理したくないという思いから(エラー出たときに見にくい)、多重Apply to eachを回避した方法を紹介します。

方針・全体像

今回もSelectが大活躍するのと、加えてPieter's Methodを利用します。処理の各アクションは以下のようになります。
もともとただの文字列からjsonを作るので、どこかしらにjsonの形式にするアクションが必要ですが、今回でいえば一番最後の5.がそれにあたります。

各アクション解説

1. 改行コードを定義

改行コードの定義はいろいろ方法があります。一番簡単なのはメモ帳とかで改行して、コピーペーストすればよいです。
もしくは、https://blog.tottokug.com/entry/2020/02/06/230353 こちらのブログか、あとは

decodeUriComponent('%0D%0A')　→ CRLF
decodeUriComponent('%0A')　→ LF

テキストの改行コードに応じて、上記の関数で分割ができます。Composeアクションで定義しておきましょう。

2. CSVを改行コードで分割

以降、便宜的に、CSVのデータをテーブルのようにあらわします。
ここでやるのは、まず行方向の分割です。改行コードを定義してあるので、その変数でCSVの内容を分割します。

Compose.action

split(body('Get_file_content'),outputs('Compose_2'))

※ComposeとCompose2の位置が逆になってしまったので、改行コード部分がoutputs('Compose_2')になっています

3. 行方向に分割した結果の0番目をヘッダー行として定義

最終的に生成するjsonのキー部分を先に作っておきます。
キーは、CSVのヘッダー行ですので、先ほど改行コードで分割した最初の行 (0番目) を、今度は横に分割します。 (カンマ区切り)

4. 2行目～最終行までをループ処理

2行目～最終行までのループ処理ですが、雰囲気としては for(i=2; i<=N-1; i++) みたいな感じです。
配列としては2行目は、[1]になることに注意して、Apply to eachの最初の入力を、

range(1,sub(length(outputs('Compose')),1))

とします。range()は以前の投稿でも紹介しましたが、2つの入力がある関数で、range(N,M)の形式で、NからM個の整数配列を作ります。
ここでいえば、range(1,sub())なので、 [1,2,3....(改行で分割した行数)-1] が範囲になります。

5. Selectアクションで一行を処理

ここが一番のポイントです。Selectアクションで先に定義しておいたヘッダー行と、ループ対象行をカンマでsplitした結果から、jsonの素を作ります。図で書くと以下のような操作です。

このような処理は、前の投稿(LookUp番外編)でも紹介しています。

今回は、Selectの入力を、まずrange関数で[0,1,2,...,列数-1]にします。
この整数をもとにして、ヘッダー行をカンマで区切ったN列目と、ループ対象行をカンマで区切ったN列目を文字列結合します。

使った数式は

concat('"',outputs('Header')?[item()],'":"',split(outputs('Compose')?[items('Apply_to_each')],',')?[item()],'"')

作るのもとの対応としては・・・

このような感じです。
これを実行した結果を次のステップでComposeしてjsonとしての1行にします。

6. ComposeでSelect結果をjson化

ここでPieter's Methodを利用します。
詳細： https://sharepains.com/2019/07/09/compose-actions-inside-apply-to-each-steps-in-microsoft-flow/

Apply to eachの中でComposeした結果を、Apply to eachの外側でComposeすると、なんと自動的に配列にしてくれるんです！！！！
すごい！！
これを実行するために、まずjsonオブジェクトを1つ作ります。

json(concat('{',join(body('Select'),','),'}'))

Selectの結果をカンマでjoinして、concatで前後に{}をつけます。これをjson関数でjsonに。
これによって生成されるのが、

composed.json

{
 "company":"LifeLock",
 "category":"web",
 "city":"Tempe",
 ...
}

こんなjsonです。
あとはPieter's Methodでこれを配列にすれば完成！

完成！

サンプルを以下に配置しました。
https://github.com/mofumofu-dance/PowerApps365/blob/master/Samples/ConvertCSVtoJSONFlow_20200226031717.zip