SQLを使ってExcelのデータのJSONに変換する

開発をしている中でデータの変換が必要になる場面は多々あるかと思います。

例えば、**顧客からExcelでもらった表形式のデータをJSON形式に変換したい場合**、どのような方法で変換しますか？

私がパッと思いつくのは例えば以下のような方法。

1. AI（ChatGPTなど）にファイルをアップして変換してもらう
1. Excel（またはCSV）をJSONに変換するPythonスクリプトを作成する
1. エディタに張り付けて正規表現を駆使して置換する
1. Excelを駆使してExcel内で変換する
1. データをRDBのテーブルに突っ込んでSQLで抽出する

手軽さという面では1.のAIに変換してもらう方法がおそらく最も手っ取り早いですが、データ量（Excelの行数）が多いと１回のやり取りで全データを変換してもらうのは難しいでしょう。また、データの内容によってはAIに学習させたくないものもあるので、使える場面は意外と限られそう。

ローカルでPythonを動かせる環境があって、ある程度Pythonに慣れている人であれば、AIを使いながらサクッと変換用のスクリプトを作成するのが手っ取り早いかもしれません。（Pythonでなくとも、使い慣れているスクリプト言語があれば何でも良いと思いますが。）

私の場合、Pythonよりも、正規表現よりも、Excelよりも、SQLの方が慣れているので、こういう場面では5の方法を選びがちです。

以下、私がよくやる変換方式を軽く紹介します。

## ExcelのデータをSQLでJSONにする

今回は商品のデータとして以下のサンプルをもとに考えます。
洋服を取り扱う企業の商品データのイメージです。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/497228/01c0b0ef-88ef-c881-1776-4fac82df84aa.png)

これをSQLで扱うには、まずテーブルを作成してデータをインサートしてあげる必要があります。
その前段として、まずはExcel内でSQL（SELECT）を作成します。
最初の列に`SELECT`句を付けて、文字列は'（シングルクォート）で囲われるようにし、最後に`UNION ALL`を付けます。
データの１行目だけはカラム名用にエイリアスを付けておきます。
Excel上はこんな感じ。

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/497228/abd10f74-c3c5-3682-720e-ad6460a299a8.png)

これをオートフィルで全行に適用し、その内容をエディタに張り付けてタブを空文字に置換すると以下のようになります。

```sql
SELECT '0001' 商品CD,'Tシャツ' 商品名, '白' カラー, 'S' サイズ,800 単価 UNION ALL
SELECT '0001', 'Tシャツ', '白', 'M',800 UNION ALL
SELECT '0001', 'Tシャツ', '白', 'L',800 UNION ALL
SELECT '0001', 'Tシャツ', '白', 'LL',800 UNION ALL
SELECT '0001', 'Tシャツ', '白', '3L',1000 UNION ALL
SELECT '0001', 'Tシャツ', '白', '4L',1000 UNION ALL
SELECT '0001', 'Tシャツ', '黒', 'S',800 UNION ALL
SELECT '0001', 'Tシャツ', '黒', 'M',800 UNION ALL
-- 以下略
```

１行目にCREATE文を追記し、適当なテーブル名を付けて、最後の`UNION ALL`をセミコロンに変えて以下のようなSQLにします。

```sql
CREATE TABLE item_tmp AS 
SELECT '0001' 商品CD,'Tシャツ' 商品名, '白' カラー, 'S' サイズ,800 単価 UNION ALL
SELECT '0001', 'Tシャツ', '白', 'M',800 UNION ALL
SELECT '0001', 'Tシャツ', '白', 'L',800 UNION ALL
-- 途中省略
SELECT '0002', 'ロングTシャツ', '黒', '3L',1400 UNION ALL
SELECT '0002', 'ロングTシャツ', '黒', '4L',1400;
```

これを実行すると、接続先のDBに対してitem_tmpという名前のテーブルが作成され、SELECTの結果がそのままデータとして作成される。

後はSQLでほしいデータ形式を作って取得します。
例えばこんな感じ。


```sql
-- 以下はMySQLの構文。DBによって文字列結合の関数や演算子が異なるので注意。
select concat('{"itemCode":"',商品CD,'","商品名":"',商品名,'","単価":',単価,'},')
from item_tmp;
```

実行結果は以下のようになる。

```
{"itemCode":"0001","商品名":"Tシャツ","単価":800},
{"itemCode":"0001","商品名":"Tシャツ","単価":800},
{"itemCode":"0001","商品名":"Tシャツ","単価":800},
{"itemCode":"0001","商品名":"Tシャツ","単価":800},
{"itemCode":"0001","商品名":"Tシャツ","単価":1000},
{"itemCode":"0001","商品名":"Tシャツ","単価":1000},

・・・
```

これに対して、全体が配列になるようにキーを追加し、全体を囲う波括弧を追加すればJSON形式になります。

```json
{
  "items": [
    {"itemCode":"0001","商品名":"Tシャツ","単価":800},
    {"itemCode":"0001","商品名":"Tシャツ","単価":800},
    {"itemCode":"0001","商品名":"Tシャツ","単価":800},
    {"itemCode":"0001","商品名":"Tシャツ","単価":800},
    {"itemCode":"0001","商品名":"Tシャツ","単価":1000},
    {"itemCode":"0001","商品名":"Tシャツ","単価":1000}
  ]
}
```

### SQLで変換するメリット

正直、ExcelでSQL文を作成するのであれば、Excel内でJSON形式を作った方が速いのでは？という感じがします。
ただ、特定の条件に当てはまるデータは除きたい場合や、重複を削除したい場合、データを少し加工したい場合が面倒になります。
SQLでデータを変換する際のメリットは、不要なデータがあった際には`WHERE`句で絞り込むことができたり、重複がある際に`GROUP BY`などで取り除くことができること。
（Excelでもフィルターの機能とか駆使すればできなくはないと思いますが、私がSQLの方がやりやすいので。。）

例えば、今回のデータでは色違いによる値段の違いは無視できそうなので、色は省略して商品のコードとサイズによる値段を取得します。
また、サイズではS～LLまでは値段が同じなので、値段の違いが分かるSサイズと3Lサイズのみを取得したいとします。
その場合、`WHERE`と`GROUP BY`を使って以下のようにできます。

```sql
select concat('{"itemCode":"',商品CD,'","商品名":"',MAX(商品名),'","サイズ":"',サイズ,'","単価":',MAX(単価),'},')
from item_tmp
where サイズ in ('S', '3L')
group by 商品CD, サイズ;
```

その結果得られた内容を加工すれば、以下のようなJSONが得られます。

```json
{
  "items": [
    {"itemCode":"0001","商品名":"Tシャツ","サイズ":"S","単価":800},
    {"itemCode":"0001","商品名":"Tシャツ","サイズ":"3L","単価":1000},
    {"itemCode":"0002","商品名":"ロングTシャツ","サイズ":"S","単価":1400},
    {"itemCode":"0002","商品名":"ロングTシャツ","サイズ":"3L","単価":1600},
  ]
}
```

## データ変換の使いどころ

表形式のデータをJSON形式にしたい場面はそれほど多くないかもしれないですが、例えばWebアプリケーションでフロントを先に作りたい場合、バックエンドからのレスポンスを想定したJSONを用意しておくことで、フロントだけの開発がやりやすくなったりします。

## まとめ

データの変換には他にもいろんな方法がありそう。
業務でデータ移行する場合などで、必要に迫られてデータ変換することも少なくないかと思いますが、開発を効率よく進めるうえでも、自分の欲しい形式のデータを素早く作るための手段をいくつか持っておくと結構便利だなと思います。

また、エンジニアにとってExcelやスプレッドシートはデータ変換ツールとして使うととてｍ便利。