Linux 命令行工具处理电子表格数据

这里的电子表格数据是指有固定字段的数据表格,对字段进行筛选,排序,分隔,合并等操作,使用 Linux 下的命令行工具,包括:grep,sed,sort 等。当数据量和文件数量巨大,自动化处理的 Linux 命令行工具具有极高的效率。

这里的电子表格以 Windows 下的 Excel 为例,Linux 命令行工具以 Ubuntu 10.11 仓库自带的为例。
一个 Excel 表格导出的 CSV 文件:
人民渠.csv

支,斗,农,分水桩号(地点),设计流量(m3/s),现状过水流量(m3/s),渠道长(m),合计,田,土,其中:提灌面积(亩),实际灌面(亩),备注,类别编码,其它
鲁联干渠,,,,5,5,28509,85300,38385,46915,,61905,,2,
,,鲁联1#,鲁联干渠35+664,0.1,0.08,808,95,43,52,,76,,1,
,,大明1#,大明支渠0+500,0.05,0.02,434,84,38,46,,34,,1

一个支渠名单表:
支渠名.txt

鲁联
大明

导出 Excel 表格中的数据为 csv 文本文件要注意:单元格中不含单元格内的换行符,不能含有csv文件的字段分隔符,因为是把 Windows 下的 数据放在 Linux 下的 zh_CN.UTF-8 环境下处理,所以先进行格式转换:
iconv -f GB18030 -t UTF-8 人民渠.csv | dos2unix > 人民渠utf8.csv

然后为了数据处理方便,把“人民渠utf8.csv“表格中的第一行标题删除,并把标题的内容写入另一个文件:
标题.txt

支,斗,农,分水桩号(地点),设计流量(m3/s),现状过水流量(m3/s),渠道长(m),合计,田,土,其中:提灌面积(亩),实际灌面(亩),备注,类别编码,其它

下面是一些主要处理命令:
1、查找"支渠名.txt"的每行支渠名称,把“人民渠utf8.csv”中第三字段包含前面支渠名称的行分别写入各自的支渠名称表格中

cat 支渠名.txt | while read line ; do grep -E \(.*,\){3}$line 人民渠utf8.csv > ../支渠/$line.csv; done

2、为每条支渠文件加上第一行的标题

for file in *.csv; do cat 标题.txt $file > ../加标题支渠/$file ; done #加上头部标题行

3、把第3字段“大明支渠0+500”分解成“大明支渠,0,500” 3 个字段

for file in *.csv; do sed "s/\([0-9]\{1,2\}\)+\([0-9]\{1,3\}\)/,\1,\2/" $file > ../temp/$file; done

4、根据公里桩(第5字段,第一关键字段)和米桩(第6字段,第二关键字段)从小到大排序
文件格式:
大明.csv

,,大明21#,大明支渠,11,486,0.1,0.09,999,194,87,107,,175,,1

,风洞沟上斗渠,,大明支渠,10,033,0.15,0.12,1667,350,158,192,,280,,2

排序命令:

for file in *.csv; do sort -t "," -n -k 5,5 -k 6,6 $file > ../已排序/$file; done

其中 sort 命令的版本:sort (GNU coreutils) 8.5

5、定位特定字段,合并字段
把第5、6字段的内容用加号连接后形成的字符串添加到第4字段后面,如下:
" ,,大明1#,大明支渠,0,500,0.05,0.02,434,84,38,46,,34,,1 "变为
“ ,,大明1#,大明支渠0+500,0,500,0.05,0.02,434,84,38,46,,34,,1 ”

mkdir ../已添加桩号 ; for file in *.csv; do sed "s/\(\([^,]*,\)\{3\}\)\([^,]*,\)\([0-9]\{1,2\}\),\([0-9]\{1,3\}\)/\1\3\4+\5,\4,\5/" $file > ../已添加桩号/$file; done

注意:前面“[^,]“表示模式匹配中不包含“,“的其它字符,否则“,”也将被当作普通字符匹配从而不能定位特定字段。

6、在每行的末尾添加一个“,”,形成一个空白字段以供添加新内容

for file in *.csv; do sed "s/$/,/" $file > ../加位置/$file; done

注意:一定把确保文件的换行格式是unix模式的,再执行 sed"s/$/,/" 才有效,即如果是 Windows 下的文件,一定要 dos2unix 命令转换一下。

7、把 zh_CN.UTF-8 Linux 环境下的文件转为 GB18030 编码和 Windows 换行符的可在 Windows 下使用的文件:

iconv -f UTF-8 -t GB18030 in.txt | unix2dos > out.txt

2011.05.03 补充:
在使用 iconv 命令时,如果文件内容包含非 GB2312 编码的汉字,那么相应的就要使用比 GB2312 包含更多汉字的字符集,比如:GB18030;否则就会出现因为不能识别的字符而导致转换失败。