将不规范电子数据整理为标准二维表的一种方式

计算机审计是以信息系统和数据库原始数据为切入点，通过对底层数据的采集、转换、清理、验证，形成中间数据表之后，再根据实际需要去运用查询方法而得出审计结果的一整套过程。审计中，数据对比是常用的计算机方法之一，但数据对比的前提是被对比的数据表为标准的二维数据表。因此，对采集到的数据进行整理，尤其是对手工制作的电子表格整理时，对数据的规范化整理就是必须要做的工作。

在某次审计工作中遇到有手工制作的电子表格，每一行是一个家庭的信息，但其中一个单元格内包含了多个成员姓名，另一个单元格内包含了多个证件号码，而且单元格里前后可能都有不定数量的空格或没有空格，各成员和证件号码之间也是不定数量的空格，而且每一行数据中成员数量是不确定的。这种形式的内容，便于肉眼观察但不能直接用于数据分析。要和外部数据表关联进行对比，首先必须把上述电子数据整理生成一个规范的二维数据表，总体思路为：将单元格中第一段有效数据取出后进行单独存放，判断其后有无所需内容，如有就继续取出再单独存放，否则就停止处理。相关步骤如下：

步骤一：整理数据表，去除多余的空格，并将分隔符的数据固定为一个空格。语句如下：

update  数据表  set  证件号码=  ltrim(rtrim(证件号码))

update  数据表  set  成员=  ltrim(rtrim(成员))

update  数据表  set  成员证件号码=  ltrim(rtrim(成员证件号码))

update  数据表  set  成员=REPLACE(成员,'    ','  ')

update  数据表  set  成员证件号码=REPLACE(成员证件号码,'    ','  ')

--多次运行上述两行，去除多余的空格

步骤二：为保证原始数据不变，故增加中间字段，对原先一个字段含多个数据的情况进行处理，把成员和成员证件号码进行拆分，使每个人的属性独立存储在一个字段中。语句如下：

alter  table  数据表  add  成员_整理中  nvarchar(255)

alter  table  数据表  add  成员证件号码_整理中  nvarchar(255)

alter  table  数据表  add  数量_整理中  float

alter  table  数据表  add  成员之一  nvarchar(255)

alter  table  数据表  add  成员证件号码之一  nvarchar(255)

alter  table  数据表  add  成员之二  nvarchar(255)

alter  table  数据表  add  成员证件号码之二  nvarchar(255)

--根据事先判断的最大人数增加对应的列数

update  数据表  set  成员_整理中=ltrim(rtrim(成员))

update  数据表  set  成员证件号码_整理中=ltrim(rtrim(成员证件号码))

update  数据表  set  数量_整理中=数量

update  数据表  set  数量_整理中  =  (len(成员)-len(replace(成员,'  ','')))+1

    where  数量-1-(len(成员)-len(replace(成员,'  ','')))  <>0

步骤三：把整理中的成员与对应的证件号码置入到对应的新字段中，最终将每个成员姓名与证件号码各自更新到上一步骤添加的字段中。语句如下：

update  数据表  set  成员之一=成员_整理中  where  数量_整理中=1

update  数据表  set  成员证件号码之一=成员证件号码_整理中  where  数量_整理中=1

update  数据表  set  成员之一=substring(成员_整理中,1,charindex('  ',成员_整理中)-1  )  where  数量_整理中>1  and  charindex('  ',成员_整理中)<>0

update  数据表  set  成员证件号码之一=substring(成员证件号码_整理中,1,charindex('  ',成员证件号码_整理中)-1)  where  数量_整理中>1  and  charindex('  ',成员证件号码_整理中)<>0

update  数据表  set  成员_整理中=substring(成员_整理中,charindex('  ',成员_整理中)+1,LEN(成员_整理中))    where  数量_整理中>1  and  charindex('  ',成员_整理中)<>0

update  数据表  set  成员证件号码_整理中=substring(成员证件号码_整理中,charindex('  ',成员证件号码_整理中)+1,LEN(成员证件号码_整理中))    where  数量_整理中>1    and  charindex('  ',成员证件号码_整理中)<>0

update  数据表  set  数量_整理中=数量_整理中-1

--以上是对成员之一进行整理的过程，成员之二、之三等等过程类似

步骤四：为便于后续对比，可将各个成员的姓名和证件号码分别合并为一列。语句如下：

select  成员之一,成员证件号码之一

    from  数据表  where  成员之一  is  not  null  and  成员证件号码之一  is  not  null

union  all

select  成员之二,成员证件号码之二

    from  数据表  where  成员之二  is  not  null  and  成员证件号码之二  is  not  null

--最多有几个成员，就需要合并几次

经过上述整理，一条记录中即可包含规范拆分后的成员和证件号码，也可以根据需要将相关字段合并后进行转置，便于下一步运用数据对比、查询分析等多种技术和方法构建模型，以发现审计疑点，从而收集审计证据，实现审计目标。