xlsxファイルに対してSQLを実できるxlsxsqlというツールを作りました。
GitHubのxlsxsqlからダウンロードできます。
これは何？
xlsxsqlは、xlsxファイルに対してSQLを実行するツールです。
また、CSV,LTSV,JSON,YAMLといったファイルに対してSQLを実行することもでき、その結果をxlsxファイルに出力することもできます。
trdsqlにxlsxファイルの読み書き機能を追加したものになります。
使い方
単純にファイルをテーブルとして指定できます。
-oまたは-outオプションは出力ファイル形式を指定します。
CSV, LTSV, JSON, JSONL, YAML, TBLN, AT, MD等が指定できます。
$ xlsxsql query -o CSV "SELECT * FROM test.xlsx"
id,name
1,apple
2,orange
3,melon
最初の行をヘッダーとして扱う場合は -H オプションを付けます。
$ xlsxsql query -o JSON -H "SELECT * FROM test.xlsx"
[
{
"id": "1",
"name": "apple"
},
{
"id": "2",
"name": "orange"
},
{
"id": "3",
"name": "melon"
}
]
シート名の指定
ファイルを指定しただけの場合は、最初のシート全体が対象となります。
シート名を指定する場合は、ファイル名のあとに::を付けてシート名を付けます
$ xlsxsql query -o CSV "SELECT * FROM test.xlsx::Sheet2"
シートのリストは listサブコマンドで確認できます。
$ xlsxsql list test.xlsx
Sheet1
Sheet2
セルの指定
セルを指定することもできます。セルを指定した場合は、値が一つもない行または値が一つもない列に達するまでをテーブルとして扱います。
セルはシート名のあとに.を付けて指定します。
$ xlsxsql query -o CSV "SELECT * FROM test.xlsx::Sheet2.B2"
シート名を省略した場合は、最初のシートが対象となります。'::.セル名'と書けます。
$ xlsxsql query -o CSV "SELECT * FROM test.xlsx::.B2"
xlsxファイルに出力
-oオプションで出力ファイル形式を指定できますが、xlsxファイルに出力する場合はファイル名が必要になります。
さらにファイル名は.xlsxで終わる必要があります。そのため出力ファイル名を指定するオプション--out-fileに.xlsxのついたファイル名を指定すれば
-o xlsxは省略できます。
$ xlsxsql query --out-file out.xlsx "SELECT * FROM test.xlsx"
また、trdsqlで使用できるファイル形式は全て使用できます。そのため、CSVファイルをxlsxファイルに変換することもできます。
$ xlsxsql query --out-file out.xlsx "SELECT * FROM test.csv"
シートのクリア
指定したファイルが無かった場合は、新規に作成されます。ファイルがあった場合は、そのファイルに出力されます。単純に元のファイルのセルに埋めていくだけなので、出力分だけ上書きされ、それ以外のセルは変更されません。
書き出すシートを出力する内容だけにしたい場合は、--clear-sheetオプションを付けます。
シートの指定、セルの指定
xlsxファイルに出力する場合は、シート名を指定できます（デフォルトはSheet1です）。また、セルを指定することもできます。
$ xlsxsql query --out-file out.xlsx --out-sheet Sheet3 --out-cell F6 --out-header "SELECT * FROM test.csv"
--out-headerオプションを付けると、ヘッダー行も出力します。
SQL文の省略
SELECT * FROMはよく使われるので、tableサブコマンドが用意されています。（tableはSELECT * FROMの省略形です）
$ xlsxsql table -o AT -H test.xlsx
+----+--------+
| c1 | c2 |
+----+--------+
| 1 | Orange |
| 2 | Melon |
| 3 | Apple |
+----+--------+
ATはASCII Tableの出力です。
JOIN
JOINもできます。これまでの指定を組み合わせてみます。
まずシートの全体をテーブルで確認します。
$ xlsxsql table -o AT test3.xlsx
+----+----+--------+--------+----+----+-------+
| A1 | B1 | C1 | D1 | E1 | F1 | G1 |
+----+----+--------+--------+----+----+-------+
| | | id | name | | | |
| | | 1 | apple | | | |
| | | 2 | orange | | | |
| | | 3 | melon | | id | price |
| | | | | | 1 | 100 |
| | | | | | 2 | 50 |
| | | | | | 3 | 500 |
+----+----+--------+--------+----+----+-------+
C1からD4までにid,nameのテーブルがあり、F4からG7までにid,priceのテーブルがあります。
この２つのテーブルをidで1でJOINしてみます。
$ xlsxsql query -o AT -H "SELECT a.id,a.name,b.price
FROM test3.xlsx::.C1 AS a
LEFT JOIN test3.xlsx::.F4 AS b ON a.id=b.id"
+----+--------+-------+
| id | name | price |
+----+--------+-------+
| 1 | apple | 100 |
| 2 | orange | 50 |
| 3 | melon | 500 |
+----+--------+-------+
もちろん、この内容をxlsxファイルに出力できます。入力のファイル名を出力のファイル名にすれば、同じファイルに出力できます。
$ xlsxsql query --out-file test3.xlsx --out-header --out-sheet Sheet1 --out-cell B10 -H
"SELECT a.id,a.name,b.price
FROM test3.xlsx::.C1 AS a
LEFT JOIN test3.xlsx::.F4 AS b ON a.id=b.id"
もう一度、シート全体を確認すると以下のようになります。
$ xlsxsql table -o AT test3.xlsx
+----+----+--------+--------+----+----+-------+
| A1 | B1 | C1 | D1 | E1 | F1 | G1 |
+----+----+--------+--------+----+----+-------+
| | | id | name | | | |
| | | 1 | apple | | | |
| | | 2 | orange | | | |
| | | 3 | melon | | id | price |
| | | | | | 1 | 100 |
| | | | | | 2 | 50 |
| | | | | | 3 | 500 |
| | | | | | | |
| | | | | | | |
| | id | name | price | | | |
| | 1 | apple | 100 | | | |
| | 2 | orange | 50 | | | |
| | 3 | melon | 500 | | | |
+----+----+--------+--------+----+----+-------+
CSVファイルとのJOINも可能です。
別にCSVファイルがあるとします。
id,price
1,120
2,130
3,140
さきほどのxlsxファイルとCSVファイルをJOINしてみます。test3.xlsx::.F4をCSVファイルに変更します。
xlsxsql query --out-file test3.xlsx --out-header --out-sheet Sheet1 --out-cell B10 -H
"SELECT a.id,a.name,b.price
FROM test3.xlsx::.C1 AS a
LEFT JOIN test.csv AS b ON a.id=b.id"
結果は以下のようになります。
$ xlsxsql table -o MD test3.xlsx
MDはMarkdown Table形式の出力です。
| A1 | B1 | id | name | E1 | F1 | G1 |
|---|---|---|---|---|---|---|
| 1 | apple | |||||
| 2 | orange | |||||
| 3 | melon | id | price | |||
| 1 | 100 | |||||
| 2 | 50 | |||||
| 3 | 500 | |||||
| id | name | price | ||||
| 1 | apple | 120 | ||||
| 2 | orange | 130 | ||||
| 3 | melon | 140 |
複数クエリー
2023年12月17日にリリースされたバージョン0.4.0から複数のクエリーを実行できるようになりました。
UPDATEやDELETEした後に結果を出力できます。
$ xlsxsql query --header --out-header
"UPDATE test.xlsx SET Age=Age+1 WHERE Name='Alice';
SELECT * FROM test.xlsx"
Name,Age
Alice,21
Bob,25
Carol,30
更新クエリーを実行した後にSELECTクエリーを実行しないと、結果は出力されません。
同じxlsxファイルに出力する場合もUPDATEだけでは結果は反映されないので、SELECTクエリーによって結果を出力する必要があります。
まとめ
xlsxsqlのソースコードはそれほど大きくなく、trdsqlに機能追加しただけですが、SQLの強力な構文に加えてCSV,JSON,YAML等の変換ができるので、規模の割にできることが多いツールになっていると思います。