(BluePrism)ポケモン図鑑を作る ～Part5 ポケモンの英語名⇔日本語名を対応させるDB作成編～

お世話になっております。
おむおむです。

前回の記事はこちら↓
newgraduate19-rpa.hatenablog.com
前回、Poke APIを叩く中で、
情報が欲しいポケモンの名前を
英語名で指定する必要があると説明しました。

今回は、日本語名⇔英語名を変換するための
データベースを作っていこうと思います！

まずはJSONで取得

自分で一から十までデータベースを構築、
などということはしません！


何度もお世話になっているfanzeyiさんが公開している中で
pokedex.jsonを使用します。

rawデータをブラウザに表示して、
そこからメモ帳に貼りつけ、でもいいんですが、
せっかくなのでエンジニアっぽく()
コマンドプロンプトとか使ってみましょう。

>CURL (rawデータのURL) > C:\temp\pokedex.json

といってもこれだけです。
肝心のrawデータのURLは、github上で対象のファイルで
rawボタンをクリックして表示されるURL、つまり

https://raw.githubusercontent.com/{ユーザ名}/{プロジェクト名}/{ブランチ名}/{ファイル名}

という感じですね、確か。
CURLでGETしたrawデータをテキトーな階層へ
リダイレクトしてファイルを作成します。

JSONから必要な情報を抽出する

さて、取得したJSONファイルには
ポケモンのステータス？など、
今回使用しないデータも入っているので、
必要なもののみ抽出してみます。

抽出に使用するのはこちら↓
stedolan.github.io
コマンドプロンプトを使用して、
JSONデータを加工・抽出できます。

使い方は簡単、DLしてC:\Program File配下に作成した
jqフォルダ内に格納し、パスを通した後、
コマンドプロンプトで叩いてみます。

> jq-win64 --version
jq-1.6

やったぜ

あとは以下のサイトを参照して、
・ID
・ポケモンの英語名
・ポケモンの日本語名
をそれぞれ抽出します。
(めっさわかりやすいわぁ...)
medium.com

JSON→CSVへ変換

さて、JSONファイルは加工できそうだけど、
これからどうしようかな。。。

そうだ、JSONをCSV形式に変換したら、
なんだかDBっぽくなりそうだな！？


というわけで、抽出してCSVに変換するコマンドはこんな感じ↓

>TYPE C:\temp\pokedex.json | jq-win64 -r ".[] | [.id, .name.english, .name.japanese] | @csv" > C:\temp\pokedex.csv

なお、無駄なダブルクォーテーションがついているので、
テキストエディタで開いて置換してまとめて削除します。
(コマンドプロンプトで1Linerで置換できる方法あれば教えてください...)

CSVをデータベースにインポートする

さあここまでノリで来たのはいいものの、
本当にCSVをDBにインポートできるの・・・？

結論、余裕でできます
BULK INSERTというコマンドを使用します。
docs.microsoft.com

と、その前に、インポート先となる
テーブルを作っておく必要がありますね。
大した内容ではないので、コマンドプロンプトから
SQLCMDを使用してデータベースにクエリを発行していきます。

>SQLCMD -S localhost -E -- Windows認証でログイン
1>USE bp ; --使用するデータベースを指定
2>CREATE TABLE pokedex (
3>id INT PRIMARY KEY, -- 主キーに設定
4>english NVARCHAR(12) NOT NULL, -- 英語名のポケモンの文字数はMAX12文字
5>japanese NVARCHAR(6) NOT NULL -- 日本語のポケモン名はMAX6文字
6>);
7>GO

はい、これでpokedexという名前の
テーブルを作成できました。
ちなみにBlue Prismで使用しているものと
同じDB内に作ってます。

あとはここから、BULK INSERTを叩いてみましょう！

1>BULK INSERT pokedex
2>FROM 'C:\temp\pokedex.csv'
3>WITH (FORMAT 'CSV', FIRSTROW = 1);
4>GO
メッセージ 4879、レベル 16、状態 1、サーバー OMUOMU\SQLEXPRESS、行 1
行 1、列 3 の CSV データ ファイル C:\temp\pokedex.csv の列値が無効であるため、一括読み込みが失敗しました。
メッセージ 7399、レベル 16、状態 1、サーバー OMUOMU\SQLEXPRESS、行 1
リンク サーバー "(null)" の OLE DB プロバイダー "BULK" により、エラーがレポートされました。プロバイダーからエラーに関す る情報を取得できませんでした。
メッセージ 7330、レベル 16、状態 2、サーバー OMUOMU\SQLEXPRESS、行 1
リンク サーバー "(null)" の OLE DB プロバイダー "BULK" から行をフェッチできません。

もしやカラム名がないCSVファイルは使えない？と思い、
一行目にカラム名を入れてみたが、同様のエラーが。。。

ちゃんとエラーメッセージを読んでみると、
列値が無効となっているな？
ということは、SQL Server側で日本語(ダブルバイト文字)が認識されていない？

そう思ってマイクロソフトのdocsを読んでいると、
UTF-8のCSVファイルをインポートする際は、
WITH句の中でCODEPAGE = 65001を指定する必要があるっぽい。

ということで、修正してコマンドを叩くぞい！

1>BULK INSERT pokedex
2>FROM 'C:\temp\pokedex.csv'
3>WITH ( CODEPAGE = 65001, FORMAT = 'CSV', FIRSTROW = 1 );
4>GO

(809 行処理されました)

やったぜ！！！
無事インポートが完了しました。

まとめ

・jqコマンドマジ便利☆
・データ加工はPythonでやれ感が強い
・BULK INSERTマジ便利☆

今回もBlue Prismなんも関係ないな...
次回は実際にプロセス作成に入ります！多分！