shell script 处理 CSV 文件 (Excel)

DevOps · lvjian700 · Created at · Last by hkliya Replied at · 3265 hits
30

CSV 是一种非常方便的数据交换格式。业务人员可以方便的在 Excel 进行编辑,然后上传到业务系统中。但是对于 Developer,Excel 略显笨重,并且编程方便并不那么友好。

本文将介绍一种方式,在 shell script 中处理 CSV 文件。内容涉及:

  • 提取数据行
  • 统计行数
  • 输出指定列
  • 按列排序
  • 比较两列数据

其中将使用如下指令:

  • cat
  • grep
  • awk
  • wc
  • sort
  • vimdiff

提取数据行

中国城市近 4 年房产价格:

这里 陕西 的拼音 Shaanxi 是, 山西的拼音是 Shanxi

City,2013,2014,2015,2016
Beijing,22000,30000,35000,38000
Shanghai,20000,25000,30000,35000
Shaanxi-xi'an,6000,5800,5700,6000
Shaanxi-baoji,1000,1200,2000,2000

提取 Shaanxi 价格

cat apartment_prices.csv | grep ^Shaanxi
# output
# Shaanxi-xi'an,6000,5800,5700,6000
# Shaanxi-baoji,1000,1200,2000,2000

提取非 Shaanxi 价格

cat apartment_prices.csv | grep -v ^Shaanxi
# output
# City,2013,2014,2015,2016
# Beijing,22000,30000,35000,38000
# Shanghai,20000,25000,30000,35000

cat 用于输出 apartment_prices.csv 文件内容,grep 用于按照正则过滤我们需要的文件。

  • grep ^Shaanxi:提取以 Shaanxi 开头的数据行,^ 在 Regex 中用于指定行首。
  • grep -v ^Shaanxi-v 参数为 --invert-match,提取不满足 Regex 条件的数据。

统计行数

中国城市近 4 年房产价格:

City,2013,2014,2015,2016
Beijing,22000,30000,35000,38000
Shanghai,20000,25000,30000,35000
Shaanxi-xi'an,6000,5800,5700,6000
Shaanxi-baoji,1000,1200,2000,2000

总数据行:

cat apartment_prices.csv | wc -l
# 5
  • wc -lwc(Word Count) 用于行数,字数等数据统计,-l 代表按行数统计。

数据行数(没有header)

cat apartment_prices.csv | tail -n +2 
# Beijing,22000,30000,35000,38000
# Shanghai,20000,25000,30000,35000
# Shaanxi-xi'an,6000,5800,5700,6000
# Shaanxi-baoji,1000,1200,2000,2000

cat apartment_prices.csv | tail -n +2 | wc -l
# 4
  • tail -n +2tail 用于从文件尾部读取数据,-n +2 指定从第二行读取到行尾。我们常用 tail -f logfile.log 来监控 log 输出。

Shaanxi数据量:

cat apartment_prices.csv | grep ^Shaanxi | wc -l
# 2

输出指定列

中国城市近 4 年房产价格:

City,2013,2014,2015,2016
Beijing,22000,30000,35000,38000
Shanghai,20000,25000,30000,35000
Shaanxi-xi'an,6000,5800,5700,6000
Shaanxi-baoji,1000,1200,2000,2000

输出 2014 年的数据:

cat apartment_prices.csv | awk -F, '{ print $3; }'
  • awk -F, '{ print $3; }'awk 是 linux 中非常强大的列表处理工具,linux 系统中的几乎所有输出都可以用 awk 处理。
    • -F,:指定列分隔符为 ',' (CSV格式),默认为空格,制表符。
    • pring $3: 输出 第3列 数据。

output:

# 2014
# 30000
# 25000
# 5800
# 1200
# 输出前 3 列
cat apartment_prices.csv | awk -F, '{ print $1","$2","$3; }'

output:

City,2013,2014
Beijing,22000,30000
Shanghai,20000,25000
Shaanxi-xi'an,6000,5800
Shaanxi-baoji,1000,1200

按列排序

中国城市近 4 年房产价格:

City,2013,2014,2015,2016
Beijing,22000,30000,35000,38000
Shanghai,20000,25000,30000,35000
Shaanxi-xi'an,6000,5800,5700,6000

排序 2014 年数据:

# 仅输出数据 2014 的数据
cat apartment_prices.csv | tail -n +2 | awk -F, '{ print $3 }' | sort -g
  • sort -gsort 用于排序数据,默认按照数据长度排序,-g 指定按照数字值排序。

output:

1200
5800
25000
30000
# 输出 city 信息
cat apartment_prices.csv | tail -n +2 | awk -F, '{ print $1 "," $3 }' | sort -t ',' -k 2 -g
  • sort -t ',' -k 2 -g-k 2 指定按照第二列排序,-t , 指定列分隔符。

output:

Shaanxi-baoji,1200
Shaanxi-xi'an,5800
Shanghai,25000
Beijing,30000

比较两列数据

比较数据需要将数据输出到文件,然后使用git diff 或者 vim -d file1 file2 来比较。

比如比较 2014 和 2015 的数据

# 1. 提取 2014 年的数据
cat apartment_prices.csv | tail -n +2 | awk -F, '{ print $4; }' > 2014
# 2. 提取 2015 年的数据
cat apartment_prices.csv | tail -n +2 | awk -F, '{ print $5; }' > 2015
# 3. git diff 2014 2015
git diff 2014 2015

output diff:

diff --git a/2014 b/2015
index 162645c..6accdc5 100644
--- a/2014
+++ b/2015
@@ -1,4 +1,4 @@
+38000
 35000
-30000
-5700
+6000
 2000

实战应用

最近业务人员需要更新产品的数据,通常情况,业务人员使用 Excel 做好产品数据,然后使用 CSV 格式导入到系统中。但是由于遗留系统的原因,CSV数据需要导入到不同的系统中。遗留系统的数据同步需要 Developer 手动进行。

在整个数据同步过程中,涉及数据提取,比较,校验等过程。由于 Excel 对于 Developer 并不那么友好,因此用到了本文中的处理方法,使用 shell script 处理 CSV 文件

参考资料


「软件匠艺社区」旨在传播匠艺精神,通过分享好的「工作方式」和「习惯」以帮助程序员更加快乐高效地编程。
共收到 3 条回复
1

跟着练习了一遍,都比较好理解,不明白的都可以通过 tldr 找到答案。
文中 Shanxi 拼写错了,第一个例子就没跑起来,差点就让我怀疑人生了 : )
用好 Linux 这些工具做数据处理,效率没得说啊。

30
lvjian700 · #2 ·

#1楼 @hkliya 为你的实践精神赞一个!
应该说明一下,Shanxi山西Shaanxi陕西

1
hkliya · #3 ·

#2楼 @lvjian700 原来是这样,我 naive 了。

需要 Sign In 后回复方可回复, 如果你还没有账号你可以 Sign Up 一个帐号。