PDI 处理 Excel 脏数据

frank0 2011-01-06

在数据整合过程中,经常可以遇到脏数据,所谓脏数据就是数据格式不符合要求的数据。

脏数据的来源

1. 录入过程中的错误

如将数值型的负号'-'录成了全角状态下下的'-',将小数点'.'录成了句号'。'

如拷贝粘贴的时候将空格、回车、换行等一些不可见字符也同时拷贝过来。

如身份证号码、出生日期、手机号码等有相对固定格式的数据,录入的数据格式不正确。

2. 不同系统之间的差异

有的数据在一个数据库系统下是合法的,而在其它数据库下是非法的,这是由系统之间的差异决定的。

如MySQL数据库可以保存0000-00-0000:00:00时间戳类型的数据,而对于其他的一些数据库而言,则不能保存这样的时间数据。

如SQLServer数据库在汉字字符串中可以存在半个汉字,而有的数据库系统,在遇到半个汉字时会认为是非法数据。

如 不同的数据库数据类型的最大长度、精度、刻度都是不同的。

对于一些录入时检查比较严格的应用系统来说,脏数据产生的可能性小一些。但是对于一些录入时检查不严格,甚至没有检查的系统来说,产生脏数据的可能性是很大的,如文本文件、EXCEL 文件。

脏数据的问题太广泛,本文主要谈一下如何使用 PDI 处理 Excel 中的脏数据。

在 PDI 中, 可以通过下面几个步骤可以检查出 EXCEL 文件中的脏数据

1.设置数据类型:为Sheet中的每一列设置正确数据类型以及长度、精度,并选定"类型检查"。

2.数据预览:从Excel中按照预先设定的数据类型读取数据,无法转换的数据就是非法数据,错误信息和数据都会被打印出来。通过这种方法就可以检查出数据类型不正确的数据,如录入时将字母"O"误当成数字"o"。

3.日志:将日志设置为最详细的“行级日志”(Rowlevel),这样可以打印出从Excel读数据时发生的错误位于哪一行那一列。

4. 错误文件:对于读取错误的行,行号、列号和相应的数据会被保存到错误文件中,便于以后检查。

脏数据的清洗

1.手工方式:当检查出脏数据后,如果脏数据比较少,可以使用手工的方式先修改Excel文件,然后再执行转换任务。

2.自动方式:如果希望在执行一个转换任务的的过程中,发现脏数据后就立刻进行清洗,那么就不必在"Excel输入"步骤中进行类型检查。

可以在"Excel输入"步骤中将所有数据都以字符串类型读出,然后通过"Java脚本"步骤中的一些字符串转换函数来定义清洗方法,

如常用的replace、substr、trim等,清洗后再通过"选择列"步骤,将字符串数据转换为指定的数据类型。

相关推荐