电商IT审计中的第三方支付数据清洗

最近在做一个电商项目的 IT 审计，其中涉及很多业财数据核对工作。

例如:

• 第三方电商平台与电商 ERP 系统订单核对
• 电商 ERP 系统订单与出库单核对
• 电商 ERP 系统订单与第三方支付流水数据核对
• 电商 ERP 系统订单与财务收入核对

等等。

这期间，我关注到一个以前我并没有太关注的一个问题，那就是数据清洗。

尤其是第三方支付数据，比如微信支付、支付宝支付，企业提供的这些数据，面临几个困难点。

1. 文件多。一个账号可能按月导就 12 个文件，那么多个账号文件数量就比较庞大了。
2. 脏数据多。导出来的文件前几行是一些非数据的说明文字，文件后几行是非数据的汇总信息。中间数据中也有些特殊符号，如`直接导入数据库会影响计算。

其实这种数据清洗工作，由小朋友来处理，并不能处理非常好。因为他们采用人工处理，而人工必定会出错。

因此，针对这些数据清洗的问题，我们采用 Bash 脚本来批量处理。

如果你使用的 Mac 或者 Linux 系统，天然自带 Bash 脚本(shell)，也就是终端。

如果你使用的 Windows 系统，你可以在软件商店安装 wsl ，也就是一个 Linux 子系统,如 ubuntu 发行版。

我们以上图中支付宝流水为例，介绍如何进行数据清洗工作。

假设文件名为 test.csv

掐头

文件中数据是从第 5 行开始的，因此我们需要丢弃掉前 4 行数据。

我们在终端中输入命令:

tail -n +5 test.csv > new.csv

tail 命令用于打印文件的末尾几行。

-n 选项用于指定要打印的行数。在 -n 选项后使用+符号，后跟一个数字，表示从该行数开始打印文件的末尾行。例如，tail -n +5 表示打印文件从第 5 行开始到文件末尾的所有行。

(注：tail -n 数字本身表示打印文件末尾几行）

执行命令后，我们可以看到已经把头掐掉了。

去尾

但是文件末尾的 4 行是我们不需要的，我们还要去尾。

我们在终端中输入命令：

head -n -4 test.csv > new.csv

head 命令用于打印文件的前几行。

-n 选项用于指定要打印的行数。- 符号，后跟一个数字，表示打印一定数量的行，但不包括文件的最后几行。

（注：head -n 数字，本身表示打印文件前几行）

执行后，我们可以看到，已经把尾去掉了。

掐头去尾

当然，以上是分开的两步，实际上我们可以合成一步操作。

只需要用到管道命令|。

tail -n +5 test.csv | head -n -4 > new.csv

这里的管道命令|，可以将一个命令的输出作为另一个命令的输入，从而使多个命令可以协作完成某个任务。

执行命令后：

这样，我们就完成了单个文件的任务。

批量处理文件

然而，我们需要这样处理的文件不只是一个，可能是几十上百个。

我们只需要在上面知识基础上增加一个循环。

在 bash 脚本中，循环的语法是：

for variable in list
do
   commands
done

还有需要注意的是，每个文件第 5 行都是表头，我合并的时候，只需要保留一个表头，要实现这一点，我们还需要一个变量，让第一个文件从第 5 行输出，后面的文件都从第 6 行输出。

而 Bash 脚本中使用变量很简单：

a=1
echo $a

我们要给变量赋值只需要a=1 ,而要引用变量时，就加个 $符号。

上述代码执行会，打印显示出数字 1 。

我们将以上所有知识串联起来：

offset=5
head=4
for file in *.csv; do
    tail -n +$offset "$file" | head -n -$head >> 账务明细汇总.csv
    offset=6
done

将上面代码粘贴到终端回车运行后，将处理所有的 csv 文件并输出到账务明细汇总.csv文件中。

需要注意的是>输出到文件会覆盖已存在的，而>>输出到文件是 追加，我们这里是想合并所有文件，所以使用的追加。

可以看出短短几行命令，将可以将成千上万的文件处理并合并。

这比让小朋友一个一个手工处理准确、高效多了。

特殊符号处理

上面讲了掐头去尾的工作,但实际上这些数据中间还有很多特殊字条，如果不处理，将在后面的数据核对中带来很多问题。

例如，每个字段后都有一个制表符。还有像微信流水中每个字段前面都会多一个`特殊符号。

我看到同事在导数前并没有去处理这些，而是在导入数据库后，再用update更新需要用到的字段。个人认为这是非常麻烦的。

而在终端中用 Bash 脚本进行这些文本处理是非常高效的。

我们这里需要使用sed命令。

sed -i 's/被替换的字符/替换的字符/g' 文件名

(注：-i参数代表在原文件上修改，注意备份数据）

我们以去除制表符为例。

sed -i 's/\t//g' new.csv

s 代表 sed 命令的替换操作,这里\t代表制表符，g 代表全局替换。

我们以去除特殊符号`为例。

sed -i 's/`//g' new.csv

这个命令很简洁，而且需要替换的字符还可以使用正则表达式，非常灵活。

结语

本文介绍了几个实用的 Bash 脚本命令，可以方便解决数据清洗中的难题。

勤快人有勤快人的干法，懒人有懒人的干法，

而我宁愿做一个懒人。