今回はJSONファイルを一番簡単な方法で取り込む方法を説明します。
このようなことをしたい方に役立つ記事です。
- VBSなどのプログラミングを使わないで、JSONを取り込みたい
- REST APIでJSON形式のデータを取り込みたい
- JSONファイルの取り込み方がわからない
JSONファイル取り込みの重要性について
今のWebサイトの多くはJSONファイルをシステムの裏側で使っています。
そのJSONファイルの内容を、人が見やすいように変換してWebに表示しています。
最近ではクラウドシステムを使う事が多くなってきています。
クラウドシステムに登録したデータを取りたい場合どのような方法を使っていますでしょうか？
・CSVファイルやExcelでダウンロード
・提供されている帳票を使う
という方が多いと思います。
多くのクラウドシステムではJSONでデータをとれるようになっています。
システム関係者ですとこれは半ば常識なのですが、一般的にはあまり知られていません。専門用語でREST APIと呼ぶデータのとり方です。
例えばYoutubeやTwitterのデータもRest APIで提供されていますので、データで取ることができます。
このJSONデータを取る方法は今後さらに普及していくと思います。
今回はそのJSONデータのとり方について説明します。
このような形式で保存してあるテキストファイルです。
システム関係者以外はcsvのバージョンアップ版と考えれば大丈夫です。
csvより複雑なデータをやり取りできます
{
"data": [
{
"code": "5097",
"name": "東京あおば農協",
"nameHalfKana": "ﾄｳｷﾖｳｱｵﾊﾞﾉｳｷﾖｳ",
"nameFullKana": "トウキヨウアオバノウキヨウ",
"nameFullHira": "とうきようあおばのうきよう"
},
]
}
ExcelでJSONファイルを取り込む方法
JSONファイルの準備
サンプルで銀行データのJSONファイルを取り込んでみます。
BankCode APIで提供していただいている、データを使わせていただきます。
表示されたデータをbank.jsonという名前でファイルに保存してください。
JSONファイルを取り込み
JSONファイルにExcelから接続するまでの手順です。
- リボンから「データ」を選択する
- 「データの取得」のアイコンをクリック
- 「ファイルから(F)」を選択する
- 「JSONから(J)」を選択する
- 「bank.json」のファイルを選択する
Power Queryについて
接続設定が終わると「Power Query」の画面が自動で起動します。
データへの接続と変換をする、Excelに付属しているツールです。
高次元なデータの取得や変換をする時に、このPower Queryを利用します。
下のような画面がでます。画面の構成を説明します。
左端のクエリ：
データへの接続定義名がリスト表示されます。Excelから２つのJSONファイルに接続した場合は、２つの定義名がここに表示されます。
右端のクエリの設定
上から順番にデータ変換のステップが保存されます。
今回は接続しただけなので、その接続定義が「ソース」と記録されています
中央
右端のクエリで選択した内容の、定義が書かれています。
Power QueryでJSON形式のデータを表形式に変換
中央の画面にはJSONファイルの第一階層のデータが表示されています。
次の階層がある場合は、リンクできる形で表示されます。
dataの中のデータを取り出すために、横の「List」をクリックします。
この第一階層のデータには、
- data
- size
- limit
- version
という4つの項目があります。
sizeとlimitの項目の値は10です。
このように単純に値を持っている場合と、項目の中にまた項目をもっている場合があります。それがdataです。
Listをクリックすることで、dataの中にある項目を見る事ができます。
次にRecordというデータが出てきました。
レコードも下に項目を持っているという意味です。
Recordをクリックすると何のデータが入っているか確認できます。
Recordをクリックした後の表示がこちらです。
このデータをダウンロードすると、10行データあるうちのこの１行のデータしかとれません。そのため一つ上の階層に戻ります。
右のナビゲーションの設定アイコンをクリックするかダブルクリックします。
するとデータの階層が表示されます。
data[1]を選択して、OKボタンを押すことで一つ前の画面に戻ります。
次に「テーブルへの変換」をクリックします。
これを実行することで、Excelで扱えるデータの形式に変換します。
何も変わったように見えないかもしれませんが、
・中央画面にアイコンが追加された
・適用したステップに「テーブルに変換」が追加された
の２点が変わっています。
このように操作をするたびにその操作の内容が、適用したステップに保存されます。
戻したい場合は、適用したステップから削除してください。
今回は中央画面の赤のアイコンをクリックしてください。
項目リストが表示されますので、OKをクリックします。
すると欲しいデータに変換されました。
ExcelにJSONデータを反映させる
「閉じて読み込む」をクリックすると、おなじみのExcelにデータが反映されます。
Excelのデータですが、単にJSONデータをExcelにコピーしただけではありません。
今回設定した手順がPower Queryに保存されています。
つまり
bank.jsonファイルにアクセスして、変換して、Excelに表示する
という一連の作業が保存されています。
試しにbank.jsonファイルの銀行名を何か変えてみてください。
その後この２つのどちらかを実行してください
・表をクリック後、右クリック。「更新」を選択する
・右のウィンドウの更新アイコンをクリックする
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に変更します。
- ソースの設定アイコンをクリック
- 形式を指定してファイルを開くの中から「Json」を選択
以上をすることで、JSON形式で取り込み可能となります。
後の手順は
「Power QueryでJSON形式のデータを表形式に変換」
以下の内容と同じですでそちらを参照してください。
Power BIでREST APIを使ったJSONファイルの取り込み
データを取得の下の矢印アイコンから「Web」を選択します。
後の手順は
「REST APIサイトへの接続設定」
と同じです。
まとめ
ExcelとPower BIはPower Queryというデータ接続用のツールを使っています。
ですのでデータ取得の操作はExcelとPower BI両方とも同じです。
JSONになじみの薄い方は、取りたいデータを切り取る部分が難しいと思います。
基本の操作は、取りたいデータの位置に移動。
「テーブルへの変換」ー＞ 展開アイコンがでるので、クリックして項目表示
とすることです。
JSON形式のデータはWebサイトでは一般的なので、活用する機会が出てくると思いますので、ぜひ覚えておいてください。
