プログラミングしないでJSONを取り込む Excel/PowerBI

BIツール

今回はJSONファイルを一番簡単な方法で取り込む方法を説明します。

このようなことをしたい方に役立つ記事です。

  • VBSなどのプログラミングを使わないで、JSONを取り込みたい
  • REST APIでJSON形式のデータを取り込みたい
  • JSONファイルの取り込み方がわからない

JSONファイル取り込みの重要性について

今のWebサイトの多くはJSONファイルをシステムの裏側で使っています。
そのJSONファイルの内容を、人が見やすいように変換してWebに表示しています。

最近ではクラウドシステムを使う事が多くなってきています。
クラウドシステムに登録したデータを取りたい場合どのような方法を使っていますでしょうか?
・CSVファイルやExcelでダウンロード
・提供されている帳票を使う
という方が多いと思います。

多くのクラウドシステムではJSONでデータをとれるようになっています。
システム関係者ですとこれは半ば常識なのですが、一般的にはあまり知られていません。専門用語でREST APIと呼ぶデータのとり方です。

例えばYoutubeやTwitterのデータもRest APIで提供されていますので、データで取ることができます。

このJSONデータを取る方法は今後さらに普及していくと思います。
今回はそのJSONデータのとり方について説明します。

JSONファイルとは?

このような形式で保存してあるテキストファイルです。
システム関係者以外はcsvのバージョンアップ版と考えれば大丈夫です。
csvより複雑なデータをやり取りできます

{
  "data": [
    {
      "code": "5097",
      "name": "東京あおば農協",
      "nameHalfKana": "トウキヨウアオバノウキヨウ",
      "nameFullKana": "トウキヨウアオバノウキヨウ",
      "nameFullHira": "とうきようあおばのうきよう"
    },
   ]
}

ExcelでJSONファイルを取り込む方法

JSONファイルの準備

サンプルで銀行データのJSONファイルを取り込んでみます。

サンプルデータ

BankCode APIで提供していただいている、データを使わせていただきます。
表示されたデータをbank.jsonという名前でファイルに保存してください。

https://bankcode-api.appspot.com/
{{ngMeta.description}}

JSONファイルを取り込み

JSONファイルにExcelから接続するまでの手順です。

  1. リボンから「データ」を選択する
  2. 「データの取得」のアイコンをクリック
  3. 「ファイルから(F)」を選択する
  4. 「JSONから(J)」を選択する
  5. 「bank.json」のファイルを選択する

Power Queryについて

接続設定が終わると「Power Query」の画面が自動で起動します。

Power Queryとは?

データへの接続と変換をする、Excelに付属しているツールです。
高次元なデータの取得や変換をする時に、このPower Queryを利用します。

下のような画面がでます。画面の構成を説明します。

左端のクエリ:
データへの接続定義名がリスト表示されます。Excelから2つのJSONファイルに接続した場合は、2つの定義名がここに表示されます。

右端のクエリの設定
上から順番にデータ変換のステップが保存されます。
今回は接続しただけなので、その接続定義が「ソース」と記録されています

中央
右端のクエリで選択した内容の、定義が書かれています。

Power QueryでJSON形式のデータを表形式に変換

中央の画面にはJSONファイルの第一階層のデータが表示されています。
次の階層がある場合は、リンクできる形で表示されます。
dataの中のデータを取り出すために、横の「List」をクリックします。

この第一階層のデータには、

  • data
  • size
  • limit
  • version

という4つの項目があります。
sizeとlimitの項目の値は10です。

このように単純に値を持っている場合と、項目の中にまた項目をもっている場合があります。それがdataです。

Listをクリックすることで、dataの中にある項目を見る事ができます。

次にRecordというデータが出てきました。
レコードも下に項目を持っているという意味です。

Recordをクリックすると何のデータが入っているか確認できます。

Recordをクリックした後の表示がこちらです。

このデータをダウンロードすると、10行データあるうちのこの1行のデータしかとれません。そのため一つ上の階層に戻ります。

右のナビゲーションの設定アイコンをクリックするかダブルクリックします。
するとデータの階層が表示されます。

data[1]を選択して、OKボタンを押すことで一つ前の画面に戻ります。

次に「テーブルへの変換」をクリックします。
これを実行することで、Excelで扱えるデータの形式に変換します。

何も変わったように見えないかもしれませんが、
・中央画面にアイコンが追加された
・適用したステップに「テーブルに変換」が追加された
の2点が変わっています。

このように操作をするたびにその操作の内容が、適用したステップに保存されます。
戻したい場合は、適用したステップから削除してください。

今回は中央画面の赤のアイコンをクリックしてください。

項目リストが表示されますので、OKをクリックします。

すると欲しいデータに変換されました。

ExcelにJSONデータを反映させる

「閉じて読み込む」をクリックすると、おなじみのExcelにデータが反映されます。

Excelのデータですが、単にJSONデータをExcelにコピーしただけではありません。

今回設定した手順がPower Queryに保存されています。
つまり
 bank.jsonファイルにアクセスして、変換して、Excelに表示する
という一連の作業が保存されています。

試しにbank.jsonファイルの銀行名を何か変えてみてください。
その後この2つのどちらかを実行してください
・表をクリック後、右クリック。「更新」を選択する
・右のウィンドウの更新アイコンをクリックする

Excelのデータが変わったのがわかると思います。

Power BIでJSONファイルを取り込む方法

bank.jsonファイルを作成していない方は、このページの「JSONファイルの準備」をご参照ください。

Power BIから「データを取得」を選択後、「JSON」をクリック。

するとPower Queryの画面が出てきます。
Power QueryはExcelの時に出てきたツールです。

操作方法は同じですので、Excelの時と同じ操作で取り込みができます。
「Power Queryについて」から参照してください。

以上で「Power BIでJSONファイルを取り込む方法」の説明はおしまいです。

ExcelとPower BIで同じ方法を使っているので、覚える手間が少なくて便利です。

ExcelでREST APIを使ったJSONファイルの取り込み

Webのデータを直接Excelに取り込んでみます。

リボンから「データ」を選択し、その後「Webから」を選択します。

REST APIサイトへの接続設定

Webからというメニューが表示されますので、
下のURLの値を入れます。

JSONファイルの取り込みで使ったのと同じURLで、銀行データを表示します。

https://bankcode-api.appspot.com/api/bank/JP?startWith=true&name=%E6%9D%B1%E4%BA%AC

アクセス画面が表示されます。

これは認証が必要なサイトの場合の設定です。
今回は認証は不要なので、「匿名」のまま「接続」をクリックします。

接続すると初めての方は、csvの取り込み画面になると思います。
いったんそのまま「OK」をクリックして保存してください。

データの取り込み設定がcsvになっているので、JSONに変更します。

  1. ソースの設定アイコンをクリック
  2. 形式を指定してファイルを開くの中から「Json」を選択

以上をすることで、JSON形式で取り込み可能となります。
後の手順は
Power QueryでJSON形式のデータを表形式に変換
以下の内容と同じですでそちらを参照してください。

Power BIでREST APIを使ったJSONファイルの取り込み

データを取得の下の矢印アイコンから「Web」を選択します。

後の手順は
REST APIサイトへの接続設定
と同じです。

まとめ

ExcelとPower BIはPower Queryというデータ接続用のツールを使っています。
ですのでデータ取得の操作はExcelとPower BI両方とも同じです。

JSONになじみの薄い方は、取りたいデータを切り取る部分が難しいと思います。
基本の操作は、取りたいデータの位置に移動。
「テーブルへの変換」ー> 展開アイコンがでるので、クリックして項目表示
とすることです。

JSON形式のデータはWebサイトでは一般的なので、活用する機会が出てくると思いますので、ぜひ覚えておいてください。

タイトルとURLをコピーしました
Close Bitnami banner
Bitnami