本站原创在计算机审计的数据准备阶段,首先需要从被审计单位数据库抽取数据,转换加工,然后才能加载到审计数据库使用.在被审计单位数据库与审计数据库之间无法直接连接,或者受异构数据库格式不兼容的影响丢失数据甚至出错的情况下,就需要使用第三方的文件存储作为过渡,通用的过渡载体是文本格式文件.目前,可视化的数据库文本加载工具主要有DTS、SSIS等,还不能针对指定目录批量加载数据,而手工对文本文件逐个进行加载,不仅处理效率低下,而且增加了数据质量风险.为了弥补这方面的不足,笔者引入BCP工具.BCP是大容量复制程序的简称,是Sybase和Microsoft的数据库管理系统附带工具,专用于数据库表一级数据高效快速地复制,适用于数据库文件与文本文件格式之间批量转换,在DOS命令提示符下执行.在计算机审计中,最常见的用途就是把大量的文本数据通过BCP批量导入到Microsof SQL Server空表中.
一、BCP的语法简要范例
Microsoft SQL Server帮助文件相似度检测绍了一个文本数据导入的范例:
bcp "Northwind.Jane Doe.Jane's Orders" out "Jane's Orders.txt" -c -q -U "Jane Doe" -P "go dba"
其含义是把Norhwind数据库下Jane Doe所拥有的Jane's Orders表导出到本目录下的Jane's Orders.txt文件内,使用于符型格式输出,登陆数据库的权限是Jane Doe用户,登陆是godba,由于引号内含有空格,所以使用“-q”参数,其他使用默认的字段间逗号分隔,行间回车换行分隔.
而我们在实际工作中,经常会碰到逗号或者回车换行出现在某字段内,比如审批意见等字段甚至还会偶然遇到“|”竖线,另外繁体字和半个字符等数据也会引起文本文件导入的错误,超过了范例的使用范围,这就需要对BCP的参数详细了解,完整的BCP语法如下:
Bcp{[[database_name.][schema].]{table_name|view_name｝|"query"｝
Bcp{in|out|queryout|format｝data_fle
Bcp[-m max_errors][-f format_fie][-x][-e err_fie]
Bcp[-F first_row][-L last_row][-b batch_sie]
Bcp[-n][-c][-N][-w][-V(70|80|90)]
Bcp[-q][-C{ACP|OEM|RAW|code_page｝]
[-tfeld_term]
Bcp[-r row_term][-i input_fle][-o output_fle]
[-a packet_size]
Bcp[-S server_name[\instance_name]][-U login_id]
[P password]
Bcp[-T][-v][-R][-k][-E][-h"hint[,等n]"]
根据Transact-SQL语法关系图的规则,大括号中的部分是必选项,中括号内是可选项,"|"是并列关系其中选一的意思.在这些参数中,计算机审计需要掌握的有十个,分别是:-m-e-b-c-t-r-o-S-U-P.
二、实际工作中遇到的问题
由于字段格式不兼容等原因,经常会导致被审计单位数据库某表格无法正常装入审计数据库,如我们在最近一次审计数据准备阶段就遇到了这种问题.我们取得了被审计单位导出的文本格式数据,发现中间某些字段有乱码导致无法正常装入审计数据库,样本如下表.我们仔细研究了表格数据存放的格式,发现出现乱码的字段是出现在“意见1”、“意见2”、“意见3”和“意见4”,了解其字段含义得知一个超长文本的中文字符串格式意见书被切割为四份,每个包含250个字符,如果意见书中有半角字符出现,那么在第250位字符切割的时候就会把最后一位中文字符切割为两半,这就是从被审计单位数据库中导出的文本文件出现乱码的原因.原因找到了,检测设采用通常的字段对应字段导入方式,那么在处理到第250位是半个中文字符的时候会自动与紧接着的分隔符结合形成新的字符串,这样字段的对应关系就被打乱了.
三、解决方案
我们分为两个阶段完成,第一阶段是被审计单位数据库表一级数据BCP导出文本的过程,第二阶段是把文本文件装入到审计数据库的过程,介绍的重点放在第二阶段.
第一阶段:建立被审计单位数据库导出文本的脚本.
第一步,建立一个视图V_ALCHECKADV把原表的四个字段通过位运算连接起来,参照脚本如下:
create view V_ALCHECKDV as select 申请书编码,顺序号,意见顺序号,意见1+意见2+意见3+意见4 as 意见书,操作员代码,操作日期 from ALCHECKDV,
第二步,定义字段分割符号和行分隔符用以区别意见书内部可能包含的"回车、换行、竖线、制表符"等内容,本例选择字段分割使用"|！",行分割使用"|@\n",("\r"的含义是回车,"\n"的含义是换行),语句如下:
bcp "ALCHECKADV.dbo. V_ALCHECKADV" out "E:\V_ALCHECKD.datc"-m 0 -e "E:\alcheckad.datc_error.txt"-m 10000 -e "E:\贷款意见表\01alcheckadv20090430.datc_error1.txt" -c -t"|！" -b 10000 -r"|@" -U "sa" -P"",
其含义是把ALCHECKADV数据库下dbo用户的V_ALCHECKADV视图(或表)导出存放为E盘下的V_ALCHECKAD.datc,每10000行提交一次校验,执行过程中没有被正常导入的文件输出存放到E盘下的alcheckad.datc error.txt,使用字符型格式输出,"|！"分割字段,"|@\n"换行,用户名sa,为空.
把上述脚本交由被审计单位系统管理员去测试运行,提取数据.
第二阶段:建立审计单位数据库文本导入的脚本.
第一步:建立等同于被审计单位V_ALCHECKADV等同的表,把字符型数据调增宽度,比如char(250)变更为nvarchar(5000)以提高容错率,即使是在连续五行字段分隔符识别错误的情况下正常导入.
第二步:提取文本文件目录下所有文件的全名.
E:\贷款意见表\DATAl>dir>list.txt
第三步:使用ultraedit的块复制功能把文件列表的全名复制岛出来,直接粘贴到EXCEL中,然后使用拖拉工具填充最终在F2形成批处理脚本:
BCP "ALCHECKADVdbo.ALCHECKDV" in "E:\贷款意见表\DATAl\22alcheckadv.datc" -m 10000 -e "E:\贷款意见表\DATAl\error\22alcheckadv.datc_error.txt" -c -t"|！" -b 10000 -r"|@" -U "sa" _P"",
第四步:拷贝F2列粘贴到记事本中另存为“bcp_sql.bat”批处理文件.
第五步:切换到BCP目录路径执行下列语句.
C:\Program Files\Microsoft SQL Server\80\Tools
Binn>bcp_sqlbat > E:\贷款意见表DATAl\screencopy.txt
四、注意事项
(一)在计算机审计时使用BCP工具主要因素是大批量的文本导入,结果正确和运行稳定是第一位的,所以不建议使用“-f”、“queryout”、“-o”等特殊定义的参数,原因如下:不用“-f”是因为格式文件比较复杂,修改起来也比较繁琐,不如编写语句直观高效,不用“queryout”时因为嵌入SQL语句比较凌乱,而且其功能完全可以由新建视图代替,不用“-o”是因为批处理需要指定每一个路径,而实际操作中只需要一个汇总的日志,所以在DOS批处理命令行后加上“>某日志.TXT”足以代替它.
(二)综合使用Ularedit、Excel等工具发挥其文本编辑的各自优势,Ularedit在打开大文本文件编辑半个中文字符等乱码、繁体字和文本块复制方面有优势,而Excel具备的函数编辑功能俨和文本选择性粘贴等功能使其在批处理命令编辑方面有优势.
(三)由于BCP执行过程需要运行sql server目标怎么写作器,建议先挑选较小的文本测试脚本运行成功后,在生成批处理文件提交到怎么写作器上夜间运行以减轻系统压力.(作者单位:陕西省宁强县审计局)