Linux

csvkit 終端機命令列 CSV 檔案查看、搜尋與整理工具教學

介紹如在 Linux 命令列中使用 csvkit 工具查看、搜尋與整理逗點分隔（csv）檔案內容。

csvkit 是專門用來處理逗點分隔（csv）檔案的命令列工具組，裡面包含了各種指令工具，可讓我們在終端機之中檢視以及處理逗點分隔檔。

安裝 csvkit

若在 Ubuntu Linux 中，可以使用 apt 來安裝：

# 安裝 csvkit 套件
sudo apt install csvkit

或是透過 Python 的套件管理程式 pip 來安裝亦可：

# 安裝 csvkit 套件
sudo pip install csvkit

檔案格式轉換

首先下載示範用的 Excel 資料檔案：

# 下載範例資料 Excel 檔案
curl -L -O https://raw.githubusercontent.com/wireservice/csvkit/master/examples/realdata/ne_1033_data.xlsx

in2csv 指令可將各種檔案轉換為 csv 檔案，支援的檔案格式有 csv、dbf、fixed、geojson、json、ndjson、xls、xlsx，執行時它會自動判斷檔案格式，只要指定輸入檔案名稱即可。例如將 Excel 檔案轉換為 csv 檔案：

# 將 Excel 檔轉為 CSV 檔
in2csv ne_1033_data.xlsx > data.csv

查看 CSV 檔案內容

在 Linux 的指令環境下，要查看文字檔案的內容通常都會使用 cat、head、tail 這類的指令，但是 csv 檔案內容是表格，如果欄位沒有對齊，是很難查看內容的。

csvkit 所提供的 csvlook 指令可以將 csv 檔案的內容以簡單的文字表格輸出，讓使用者很輕鬆就可以查看 csv 檔案內的表格資料：

# 查看 CSV 檔案內容
csvlook data.csv
| state | county     |   fips | nsn              | item_name                                                      | quantity | ui      | acquisition_cost | total_cost |  ship_date | federal_supply_category | federal_supply_category_name        | federal_supply_class | federal_supply_class_name                                       |
| ----- | ---------- | ------ | ---------------- | -------------------------------------------------------------- | -------- | ------- | ---------------- | ---------- | ---------- | ----------------------- | ----------------------------------- | -------------------- | --------------------------------------------------------------- |
| NE    | ADAMS      | 31,001 | 1005-00-589-1271 | RIFLE,7.62 MILLIMETER                                          |        1 | Each    |           138.00 |     138.00 | 2008-07-11 |                      10 | WEAPONS                             |                1,005 | Guns, through 30 mm                                             |
| NE    | ADAMS      | 31,001 | 1005-00-589-1271 | RIFLE,7.62 MILLIMETER                                          |        1 | Each    |           138.00 |     138.00 | 2008-07-11 |                      10 | WEAPONS                             |                1,005 | Guns, through 30 mm                                             |
[略]

如果表格比較寬，可搭配 less 指令，避免折行造成的閱讀困難：

# 搭配 less 查看 CSV 表格
csvlook data.csv | less -S

切割表格欄位

如果我們只需要使用到表格中部份欄位的資料，可以使用 csvcut 切出需要的欄位，產生一張比較小的 csv 表格。

在進行格欄位的切割與萃取前，先執行 csvcut 加上 -n 參數查看一下表格內所有欄位的編號與名稱：

# 查看 CSV 檔案表格欄位
csvcut -n data.csv
  1: state
  2: county
  3: fips
  4: nsn
  5: item_name
  6: quantity
  7: ui
  8: acquisition_cost
  9: total_cost
 10: ship_date
 11: federal_supply_category
 12: federal_supply_category_name
 13: federal_supply_class
 14: federal_supply_class_name

接著就可以使用 -c 參數來指定欄位編號，將指定的欄位切出來，建立一張新的 csv 表格：

# 切出指定欄位的資料
csvcut -c 2,3,7 data.csv
county,fips,ui
ADAMS,31001.0,Each
ADAMS,31001.0,Each
[略]

我們也可以直接以欄位名稱來指定要切出來的欄位：

# 以名稱指定欄位
csvcut -c county,fips,ui data.csv
county,fips,ui
ADAMS,31001.0,Each
ADAMS,31001.0,Each
[略]

產生出來的 csv 表格可以直接導向至 csvlook 查看資料：

# 搭配 csvlook 查看資料
csvcut -c county,fips,ui data.csv | csvlook
| county     |   fips | ui      |
| ---------- | ------ | ------- |
| ADAMS      | 31,001 | Each    |
| ADAMS      | 31,001 | Each    |
[略]

如果資料列很多，也可以搭配 head 指令，讓它只顯示前幾行：

# 搭配 head 顯示前 5 行
csvcut -c county,fips,ui data.csv | csvlook | head -n 5
| county     |   fips | ui      |
| ---------- | ------ | ------- |
| ADAMS      | 31,001 | Each    |
| ADAMS      | 31,001 | Each    |
| ADAMS      | 31,001 | Each    |

去除重複資料

若要去除表格中重複的資料列，可以搭配 Linux 標準的 sort 與 uniq 指令：

# 去除重複資料
csvcut -c county,fips,ui data.csv | sort | uniq | csvlook
| ADAMS      | 31001.0 | Each    |
| ---------- | ------- | ------- |
| BUFFALO    | 31019.0 | Each    |
| BURT       | 31021.0 | EA      |
[略]

基本統計量

csvstat 指令可以仿照 R 語言的 summary() 函數，計算表格中各欄位資料的基本統計量：

# 計算 CSV 表格資料基本統計量
csvcut -c county,acquisition_cost,ship_date data.csv | csvstat
  1. "county"

	Type of data:          Text
	Contains null values:  False
	Unique values:         35
	Longest value:         10 characters
	Most common values:    DOUGLAS (760x)
	                       DAKOTA (42x)
	                       CASS (37x)
	                       HALL (23x)
	                       LANCASTER (18x)

  2. "acquisition_cost"

	Type of data:          Number
	Contains null values:  False
	Unique values:         75
	Smallest value:        0
	Largest value:         412000
	Sum:                   5430787.55
	Mean:                  5242.072925
	Median:                6000
	StDev:                 13368.078368
	Most common values:    6800 (304x)
	                       10747 (195x)
	                       6000 (105x)
	                       499 (98x)
	                       0 (81x)

  3. "ship_date"

	Type of data:          Date
	Contains null values:  False
	Unique values:         84
	Smallest value:        2006-03-07
	Largest value:         2014-01-30
	Most common values:    2013-04-25 (495x)
	                       2013-04-26 (160x)
	                       2008-05-20 (28x)
	                       2012-04-16 (26x)
	                       2006-11-17 (20x)

Row count: 1036

篩選資料列

如果要以關鍵字搜尋表格內容，篩選出符合條件的資料列，可以使用 csvgrep 指令，並以 -c 參數指定要搜尋的欄位，-m 參數指定關鍵字。例如搜尋 county 欄位，尋找含有 LANCASTER 關鍵字的列：

# 篩選資料列
csvcut -c county,item_name,total_cost data.csv \
  | csvgrep -c county -m LANCASTER \
  | csvlook
| county    | item_name                      | total_cost |
| --------- | ------------------------------ | ---------- |
| LANCASTER | RIFLE,5.56 MILLIMETER          |        120 |
| LANCASTER | RIFLE,5.56 MILLIMETER          |        120 |
| LANCASTER | RIFLE,5.56 MILLIMETER          |        120 |
| LANCASTER | RIFLE,5.56 MILLIMETER          |        120 |
| LANCASTER | RIFLE,5.56 MILLIMETER          |        120 |
| LANCASTER | RIFLE,5.56 MILLIMETER          |        120 |
| LANCASTER | RIFLE,5.56 MILLIMETER          |        120 |
| LANCASTER | RIFLE,5.56 MILLIMETER          |        120 |
| LANCASTER | RIFLE,5.56 MILLIMETER          |        120 |
| LANCASTER | RIFLE,5.56 MILLIMETER          |        120 |
| LANCASTER | LIGHT ARMORED VEHICLE          |          0 |
| LANCASTER | LIGHT ARMORED VEHICLE          |          0 |
| LANCASTER | LIGHT ARMORED VEHICLE          |          0 |
| LANCASTER | MINE RESISTANT VEHICLE         |    412,000 |
| LANCASTER | IMAGE INTENSIFIER,NIGHT VISION |      6,800 |
| LANCASTER | IMAGE INTENSIFIER,NIGHT VISION |      6,800 |
| LANCASTER | IMAGE INTENSIFIER,NIGHT VISION |      6,800 |
| LANCASTER | IMAGE INTENSIFIER,NIGHT VISION |      6,800 |

csvgrep 也可以用 -r 參數以正規表示法（regular expression）來比對資料，例如搜尋 county 為 R 或 S 開頭的資料列：

# 以正規表示法篩選資料列
csvcut -c county,item_name,total_cost data.csv \
  | csvgrep -c county -r ^[RS] \
  | csvlook
| county     | item_name             | total_cost |
| ---------- | --------------------- | ---------- |
| RED WILLOW | RIFLE,7.62 MILLIMETER |        138 |
| RED WILLOW | RIFLE,7.62 MILLIMETER |        138 |
| RED WILLOW | RIFLE,7.62 MILLIMETER |        138 |
| RED WILLOW | RIFLE,7.62 MILLIMETER |        138 |
| SARPY      | RIFLE,5.56 MILLIMETER |        120 |
| SARPY      | RIFLE,5.56 MILLIMETER |        120 |
| SARPY      | RIFLE,5.56 MILLIMETER |        120 |
| SARPY      | RIFLE,5.56 MILLIMETER |        120 |
| SARPY      | RIFLE,5.56 MILLIMETER |        120 |
| SARPY      | RIFLE,5.56 MILLIMETER |        120 |
| SARPY      | TRUCK,UTILITY         |     47,069 |
| SAUNDERS   | RIFLE,5.56 MILLIMETER |        499 |

排序資料列

csvsort 可以用來排序資料列，使用時要以 -c 參數指定排序依據的欄位，若要反向排序則可加上 -r 參數。例如根據 total_cost 反向排序資料列：

# 排序資料列
csvcut -c county,item_name,total_cost data.csv \
  | csvgrep -c county -m LANCASTER \
  | csvsort -c total_cost -r \
  | csvlook
| county    | item_name                      | total_cost |
| --------- | ------------------------------ | ---------- |
| LANCASTER | MINE RESISTANT VEHICLE         |    412,000 |
| LANCASTER | IMAGE INTENSIFIER,NIGHT VISION |      6,800 |
| LANCASTER | IMAGE INTENSIFIER,NIGHT VISION |      6,800 |
| LANCASTER | IMAGE INTENSIFIER,NIGHT VISION |      6,800 |
| LANCASTER | IMAGE INTENSIFIER,NIGHT VISION |      6,800 |
| LANCASTER | RIFLE,5.56 MILLIMETER          |        120 |
| LANCASTER | RIFLE,5.56 MILLIMETER          |        120 |
| LANCASTER | RIFLE,5.56 MILLIMETER          |        120 |
| LANCASTER | RIFLE,5.56 MILLIMETER          |        120 |
| LANCASTER | RIFLE,5.56 MILLIMETER          |        120 |
| LANCASTER | RIFLE,5.56 MILLIMETER          |        120 |
| LANCASTER | RIFLE,5.56 MILLIMETER          |        120 |
| LANCASTER | RIFLE,5.56 MILLIMETER          |        120 |
| LANCASTER | RIFLE,5.56 MILLIMETER          |        120 |
| LANCASTER | RIFLE,5.56 MILLIMETER          |        120 |
| LANCASTER | LIGHT ARMORED VEHICLE          |          0 |
| LANCASTER | LIGHT ARMORED VEHICLE          |          0 |
| LANCASTER | LIGHT ARMORED VEHICLE          |          0 |

合併表格

csvjoin 可以合併兩張 csv 表格，其作用類似 SQL 的 JOIN。

假設我們現在有 data1.csv 與 data2.csv 兩個 csv 表格，其原始資料如下：

# 查看資料
csvlook data1.csv
| id | name  |
| -- | ----- |
|  1 | Mary  |
|  2 | Joe   |
|  3 | Abbot |
|  4 | Alan  |
# 查看資料
csvlook data2.csv
| id | age |
| -- | --- |
|  3 |  56 |
|  4 |  12 |
|  5 |  32 |
|  6 |   9 |

兩個表格中儲存了不同的資料，中間靠著 id 欄位來做對應，若要將兩張表格合併起來，就可以使用 csvjoin 指令，並以 -c 參數指定要依據哪一個欄位來作為資料對應的依據：

# 合併兩張 CSV 表格
csvjoin -c id data1.csv data2.csv | csvlook
| id | name  | age |
| -- | ----- | --- |
|  3 | Abbot |  56 |
|  4 | Alan  |  12 |

在預設的情況下，csvjoin 會以 inner join 的方式合併（只留下同時存在於兩張表格內的資料），如果需要 left、right 或 outer 這些 join 方式，可以加上對應的參數： 

# 合併兩張 CSV 表格（Left Outer Join）
csvjoin --left -c id data1.csv data2.csv | csvlook
| id | name  | age |
| -- | ----- | --- |
|  1 | Mary  |     |
|  2 | Joe   |     |
|  3 | Abbot |  56 |
|  4 | Alan  |  12 |
# 合併兩張 CSV 表格（Right Outer Join）
csvjoin --right -c id data1.csv data2.csv | csvlook
| id | age | name  |
| -- | --- | ----- |
|  3 |  56 | Abbot |
|  4 |  12 | Alan  |
|  5 |  32 |       |
|  6 |   9 |       |
# 合併兩張 CSV 表格（Right Full Join）
csvjoin --outer -c id data1.csv data2.csv | csvlook
| id | name  | id2 | age |
| -- | ----- | --- | --- |
|  1 | Mary  |     |     |
|  2 | Joe   |     |     |
|  3 | Abbot |   3 |  56 |
|  4 | Alan  |   4 |  12 |
|    |       |   5 |  32 |
|    |       |   6 |   9 |

上下合併表格

假設我們有另外一張 data3.csv 表格，其欄位跟 data1.csv 相同，只是因為資料及過於龐大，將資料分成多個檔案儲存：

# 查看資料
csvlook data3.csv
| id | name  |
| -- | ----- |
|  7 | Jack  |
|  8 | Harry |
|  9 | Oscar |

如果想要將 data1.csv 與 data3.csv 上下合併，可以使用 csvstack 指令：

# 合併兩張 CSV 表格（上下合併）
csvstack data1.csv data3.csv | csvlook
| id | name  |
| -- | ----- |
|  1 | Mary  |
|  2 | Joe   |
|  3 | Abbot |
|  4 | Alan  |
|  7 | Jack  |
|  8 | Harry |
|  9 | Oscar |

SQL 查詢

csvsql 指令可以自動根據 csv 表格產生各類 SQL 資料庫建立資料表的指令，-i 可以用來指定資料庫類型，常見的 sqlite、mysql、postgresql、mssql、oracle、firebird 都有支援：

# 從 CSV 表格產生 SQL 資料表
csvsql -i sqlite data1.csv
CREATE TABLE data1 (
        id DECIMAL NOT NULL,
        name VARCHAR(5) NOT NULL
);

csvsql 亦可直接連接資料庫，建立資料表之後，同時匯入 csv 的資料：

# 建立 SQLite 資料庫，並匯入資料
csvsql --db sqlite:///data1.db --insert data1.csv

若要從 SQL 查詢資料，並以 csv 格式輸出結果，可以使用 sql2csv 指令：

# 從資料庫查詢，以 CSV 格式輸出結果
sql2csv --db sqlite:///data1.db --query "SELECT * FROM data1;" | csvlook
| id | name  |
| -- | ----- |
|  1 | Mary  |
|  2 | Joe   |
|  3 | Abbot |
|  4 | Alan  |

參考資料：StackOverflow
Share
Published by
Office Guide
5 年 ago

Recent Posts

Python 使用 PyAutoGUI 自動操作滑鼠與鍵盤

本篇介紹如何在 Python ...

1 年 ago

Ubuntu Linux 以 WireGuard 架設 VPN 伺服器教學與範例

本篇介紹如何在 Ubuntu ...

1 年 ago

Linux 網路設定 ip 指令用法教學與範例

本篇介紹如何在 Linux 系...

1 年 ago

Windows 使用 TPM 虛擬智慧卡保護 SSH 金鑰教學與範例

本篇介紹如何在 Windows...

1 年 ago

Linux 以 Shamir’s Secret Sharing 分割保存金鑰教學與範例

介紹如何在 Linux 中使用...

1 年 ago

Linux 以 Cryptsetup、LUKS 加密 USB 隨身碟教學與範例

介紹如何在 Linux 系統中...

1 年 ago