直接用 SQLite 查詢 Excel 檔案的 XLite (還有 dsq)

Hacker News 上看到「Xlite: Query Excel and Open Document spreadsheets as SQLite virtual tables (github.com/x2bool)」這個專案,就如同說明,是一個支援讀取 Excel 檔案的 SQLite extension,原網站在 x2bool/xlite 這邊。

依照說明支援舊的 .xls 與新的 .xlsx 的格式,但不知道公式運算支援到什麼程度...

先 load extension:

sqlite3 # will open SQLite CLI
> .load libxlite

接著是建立 virtual table:

CREATE VIRTUAL TABLE class_data USING xlite(
    FILENAME './path/to/example.xlsx',
    WORKSHEET 'Class Data',
    RANGE 'A2:F'
);

接下來就可以搞事了:

SELECT COUNT(*), D FROM class_data GROUP BY D ORDER BY COUNT(*);

看起來是用 Rust + C 寫的,然後作者有提醒這是寫興趣的專案:

This project is experimental, use at your own risk. The project is developed in my free time as a way to learn Rust and database systems.

反倒是 Hacker News 討論串裡面提到了 multiprocessio/dsq 這個專案,看起來發展的比較久,支援度也比較完整了:

Commandline tool for running SQL queries against JSON, CSV, Excel, Parquet, and more

不過就不是綁 SQLite 了 (雖然還是有關),從範例可以看到他是獨立的程式:

$ dsq testdata.json "SELECT * FROM {} WHERE x > 10"

Under the hood dsq uses DataStation as a library and under that hood DataStation uses SQLite to power these kinds of SQL queries on arbitrary (structured) data.

如果是真的要用的話,這套看起來應該會好一些...

用 TSV 而不用 CSV

最近常常需要提供資料給其他部門 (非技術類的部門),有時候需要提供一些表格類的資料,傳統大多數人比較熟的是產生 CSV 格式的資料讓使用者可以用 Excel 打開,但這個格式其實有很多問題,最常見的就是 encoding 與資料有逗號 comma 的問題。

如果是在 Python 下,其中一個解法是用 openpyxl 直接產生 .xlsx,但用起來還是沒那麼有下面提到的方法順手。

如果是 shell script 時就比較麻煩了,像我這次手上有一堆影片檔,要用 FFmpeg 確認每個影片的 resolution 與 framerate 再提供給同事,這時候如果還是想產生 .xlsx 就累了...

下面要提到的解法好像記得是在 K 社的時候同事教的,用 TSV 檔格式 (當然檔名要取 .tsv),然後 encoding 用 UTF-16 (LE) 就可以解決上面提到的兩個問題,產生出來的檔案可以讓 Excel 直接打開。

StackOverflow 上的「Is it possible to force Excel recognize UTF-8 CSV files automatically?」這邊翻一翻,會發現裡面提到比較好的解法其實都是產生 TSV。

這邊另外推薦,就算是寫程式,也還是可以先產生出 UTF-8 的版本 (通常副檔名我都會先取 .txt),然後用 iconv 或是 piconv 轉成 UTF-16 (LE):

iconv -f utf8 -t utf16le a.txt > a.tsv

包到 Makefile 裡面用起來其實還蠻順手的...