xlsxファイルにSQLを実行するxlsxsql

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等の変換ができるので、規模の割にできることが多いツールになっていると思います。