使用 sqlite 处理 excel 数据。

安装

下载后解压到 /Users/your-user-name/bin/sqlite3/

配置环境变量

export PATH="/Users/your-user-name/bin/sqlite3/:$PATH"

新建 sql 处理脚本

function sql() {
  echo "CREATE VIRTUAL TABLE t USING xlite ( FILENAME '$1', WORKSHEET 'Sheet1' );"
  echo ".excel to output excel format"

 /Users/your-user-name/bin/sqlite3/sqlite3 -cmd ".mode table" -cmd ".load /Users/your-user-name/bin/sqlite3/libxlite" -cmd "CREATE VIRTUAL TABLE t USING xlite ( FILENAME '$1', WORKSHEET 'Sheet1' );"
}

执行

sql ./test.xlsx

测试

$ sql Book4.xlsx
CREATE VIRTUAL TABLE t USING xlite ( FILENAME 'Book4.xlsx', WORKSHEET 'Sheet1' );
.excel to output excel format
SQLite version 3.46.0 2024-05-23 13:25:27
Enter ".help" for usage hints.
sqlite>
sqlite>
sqlite> select * from t;
+---+-----+
| A |  B  |
+---+-----+
| a | 姓名a |
| b | 姓名b |
| a | 姓名a |
+---+-----+
sqlite> select distinct * from t;
+---+-----+
| A |  B  |
+---+-----+
| a | 姓名a |
| b | 姓名b |
+---+-----+
sqlite> .excel
sqlite> select distinct * from t;
sqlite>

References