数据导入文档

1、档案数据入库操作一、 文书数据入库1. 删除原有表格内容选中表T_WS_DOCINDEX内所有内容进行删除2. 导入mdb索引也可能是excle文件（1）打开PLSQL，工具ODBC导入器： （2）ODBC导入器具体设置：“来自ODBC的数据”：8 选择“MS Access Database”；8 输入数据库登陆信息；8 点击connect ，选择需要导入的mdb文件；8 选择具体的表。如下图所示填写选择并导入正确的索引表 “到Oracle的数据”： 8 输入用户与导入的数据库表名；8 指定字段对应关系以及字段类型；8 点击import导入。如下图所示填写选择。3. 字段填充（1） 填充前：8

2、 判断是否存在相同的档案：select count(*) FROM TA_ARCHIVE where archivetype=3 and archiveno in (SELECT distinct batchid FROM T_WS_DOCINDEX);8 查询 不同的batchid共43条，因此导入后将增加文书档案43条；SELECT count(distinct batchid) FROM T_WS_DOCINDEX8 因pdf命名与mdb表中的docname存在出入，执行以下语句使其保持一致select * from T_WS_DOCINDEX ws where length(docna

3、me)<3;update t_ws_docindex set docname=lpad( docname,3,'0') where length(docname)<38 临时表处理select ws.* from t_ws_docindex ws order by ws.batchid,ws.docname（2） 字段填充：-更新分类号为“A”update t_ws_docindex ws set ws.flh='A'update t_ws_docindex ws set ws.bmh=substr(ws.batchid,0,2);-更新到目录号up

4、date t_ws_docindex ws set ws.mlh=substr(ws.batchid,instr(ws.batchid,'.',1,1)+1,instr(ws.batchid,'-',1,2)-instr(ws.batchid,'.',1,1)-1)select ws.* from t_ws_docindex ws where ws.mlh is null-update archivetypeupdate t_ws_docindex ws set ws.archivetype=3;-update classidupdate t_w

5、s_docindex ws set ws.classid=(select cls.classid from ta_classdef cls where cls.classcode=ws.bmh);-update catalognoupdate t_ws_docindex ws set ws.catalogno=ws.mlh;-update ajhupdate t_ws_docindex ws set ws.ajh=substr(ws.batchid,instr(ws.batchid,'-',1,2)+1);select * from t_ws_docindex ws where

6、 ws.ajh is null-update ndselect substr(ws.mlh,instr(ws.mlh,'-',1,1)+1) from t_ws_docindex ws;update t_ws_docindex ws set ws.nd=substr(ws.mlh,instr(ws.mlh,'-',1,1)+1);-查找不重复的编目号、目录号select distinct ws.bmh,ws.mlh from t_ws_docindex ws order by ws.bmh-导入到 ta_catalognoselect distinct cdf.

7、classid,ws.mlh,1,1,1 from t_ws_docindex ws join ta_classdef cdf on cdf.classcode=ws.bmh order by cdf.classidinsert into ta_catalogno cno (cno.catalogno,cno.classid,cno.curboxno,cno.recordesign,cno.status) (select distinct ws.mlh,cdf.classid,1,1,1 from t_ws_docindex ws join ta_classdef cdf on cdf.cla

8、sscode=ws.bmh);4. 档案入库（1）工具应用： 8 PDF源文件路径选择 8 目标文件夹和日志文件夹自行建立 8 Oracle数据库相关数据填写 8 选择相应的索引数据表名点击导入按钮进行数据导入,具体参数如下图所示：（3） 将目标文件夹中生成的文件复制到F:SouthArchiveFTPArchiveFTPme_official文件夹中。二、 其他数据入库其他数据入库的方式流程与文书数据一致，不过在删除的表格也要选择与之相匹配的表格内容，使用ODBC导入器时应注意将要导入的索引文件的格式并选择相应的“用户/系统”。下面是各数据字段填充的脚本文件。1. 监察8 检查batchid

9、与目录名是否对应8 判断是否已经有相同batchid的档案SELECT count(*) FROM TA_ARCHIVE where archiveno in (select batchid from t_jc_docindex)如为0时继续往下操作；8 处理t_jc_docindex执行脚本“索引数据处理_t_jc_docindex.sql”select count(*) from t_jc_docindex jc order by jc.ajh descselect jc.* from t_jc_docindex jc order by jc.ajh descselect jc.* fro

10、m t_jc_docindex jc order by jc.batchid,jc.docname-更新到分类号 FLH 字段，为“F”update t_jc_docindex jc set jc.flh='F'-更新到编目号 BMH 字段update t_jc_docindex jc set jc.bmh='F'-更新到目录号 MLH 字段BATCHID的年度update t_jc_docindex jc set jc.mlh=substr(jc.batchid,instr(jc.batchid,'-',1,1)+1,instr(jc.batc

11、hid,'-',1,2)-instr(jc.batchid,'-',1,1)-1)-设置索引表的 ARCHIVETYPE CLASSID CATALOGNO三个的值update t_jc_docindex jc set jc.archivetype=7;update t_jc_docindex jc set jc.classid=31;update t_jc_docindex jc set jc.catalogno=jc.mlh;-更新案卷号，取batchid第二个'-'后的部分update t_jc_docindex jc set jc.ajh

12、=substr(jc.batchid,instr(jc.batchid,'-',1,2)+1)-是否存在空的案卷号select * from t_jc_docindex jc where jc.ajh is null-更新档案件数，同一个文档包中的件数select jc.batchid,count(*) from t_jc_docindex jc group by jc.batchid -t_batchinfo作业单位提供的，如果没有提供就没有，页数就不能计算了update t_jc_docindex jc set jc.jianshu=(select b.docnum fro

13、m t_batchinfo b where b.batchid=jc.batchid);update t_jc_docindex jc set jc.zys=(select b.papernum from t_batchinfo b where b.batchid=jc.batchid)select * from t_jc_docindex jc where jc.jianshu is null-查找不重复的目录号select distinct jc.mlh from t_jc_docindex jc order by jc.mlh;-导入到 ta_catalognoinsert into t

14、a_catalogno cno (cno.catalogno,cno.classid,cno.curboxno,cno.recordesign,cno.status) (select distinct jc.mlh,cdf.classid,1,1,1 from t_jc_docindex jc join ta_classdef cdf on cdf.classcode=jc.bmh);2. 交易出让、划拨-出让划拨数据select count(*) from t_jyclht_docindex jc order by jc.ajh desc;select jc.* from t_jyclht_

15、docindex jc order by jc.batchid,jc.docname;-更新到分类号 FLH 字段，为“JE”update t_jyclht_docindex jc set jc.flh='JE'-更新到编目号 BMH 字段(出让 JE1，划拨 JE2)update t_jyclht_docindex jc set jc.bmh='JE1' where substr(jc.batchid,instr(jc.batchid,'.',1,2)+1,1)='1'update t_jyclht_docindex jc se

16、t jc.bmh='JE2' where substr(jc.batchid,instr(jc.batchid,'.',1,2)+1,1)='2'-更新到目录号 MLH 字段BATCHID的年度update t_jyclht_docindex jc set jc.mlh=substr(jc.batchid,instr(jc.batchid,'-',1,1)+1,instr(jc.batchid,'-',1,2)-instr(jc.batchid,'-',1,1)-1)-设置索引表的 ARCHIVET

17、YPE CLASSID CATALOGNO三个的值update t_jyclht_docindex jc set jc.archivetype=5;update t_jyclht_docindex jc set jc.classid=19 WHERE substr(jc.batchid,instr(jc.batchid,'.',1,2)+1,1)='1'update t_jyclht_docindex jc set jc.classid=20 WHERE substr(jc.batchid,instr(jc.batchid,'.',1,2)+1,

18、1)='2'update t_jyclht_docindex jc set jc.catalogno=jc.mlh;-更新案卷号，取batchid第二个'-'后的部分update t_jyclht_docindex jc set jc.ajh=substr(jc.batchid,instr(jc.batchid,'-',1,2)+1)-是否存在空的案卷号select * from t_jyclht_docindex jc where jc.ajh is null-更新档案件数，同一个文档包中的件数select jc.batchid,count(*

19、) from t_jyclht_docindex jc group by jc.batchid -t_batchinfo作业单位提供的，如果没有提供就没有，页数就不能计算了update t_jyclht_docindex jc set jc.jianshu=(select b.docnum from t_batchinfo b where b.batchid=jc.batchid);update t_jyclht_docindex jc set jc.zys=(select b.papernum from t_batchinfo b where b.batchid=jc.batchid)sel

20、ect * from t_jyclht_docindex jc where jc.jianshu is null-查找不重复的目录号select distinct jc.mlh from t_jyclht_docindex jc order by jc.mlh;-导入到 ta_catalognoinsert into ta_catalogno cno (cno.catalogno,cno.classid,cno.curboxno,cno.recordesign,cno.status) (select distinct jc.mlh,cdf.classid,1,1,1 from t_jyclht

21、_docindex jc join ta_classdef cdf on cdf.classcode=jc.bmh);8 检查档案号是否重复SELECT count(*) FROM TA_ARCHIVE where archiveno in (select 'JE'|substr(jc.batchid,instr(jc.batchid,'.',1,2)+1,2)|jc.mlh|'-'|jc.ajh from t_jyclht_docindex jc)3. 交易（转让、转拍、挂牌、转挂、矿出、其他、转让旧、转挂旧）类型分类标识编目标识编目原数据量转

22、让521JE32450转拍522JE410挂牌523JE5198转挂524JE6249矿出525JE711其他526JE80转让旧527JE918转挂旧530JE1088 临时表数据处理select count(*) from T_JYZRHT_DOCINDEX jc order by jc.ajh descselect jc.* from T_JYZRHT_DOCINDEX jc order by jc.ajh descselect jc.* from T_JYZRHT_DOCINDEX jc order by jc.batchid,jc.docname-更新到分类号 FLH 字段，为“JE

23、”update T_JYZRHT_DOCINDEX jc set jc.flh='JE'-更新到编目号 BMH 字段(转让 JE3，转拍 JE4，挂牌 JE5，转挂 JE5，矿出 JE7，其他 JE8，转让旧 JE9，转挂旧 JE10)update T_JYZRHT_DOCINDEX jc set jc.bmh='JE'| substr(jc.batchid,instr(jc.batchid,'.',1,2)+1,instr(jc.batchid,'-',1,1)-instr(jc.batchid,'.',1,2)

24、-1) ;-更新到目录号 MLH 字段BATCHID的年度update T_JYZRHT_DOCINDEX jc set jc.mlh=substr(jc.batchid,instr(jc.batchid,'-',1,1)+1,instr(jc.batchid,'-',1,2)-instr(jc.batchid,'-',1,1)-1)-设置索引表的 ARCHIVETYPE CLASSID CATALOGNO三个的值update T_JYZRHT_DOCINDEX jc set jc.archivetype=5;update T_JYZRHT_DO

25、CINDEX jc set jc.classid=21 WHERE substr(jc.batchid,instr(jc.batchid,'.',1,2)+1,instr(jc.batchid,'-',1,1)-instr(jc.batchid,'.',1,2)-1)='3'update T_JYZRHT_DOCINDEX jc set jc.classid=22 WHERE substr(jc.batchid,instr(jc.batchid,'.',1,2)+1,instr(jc.batchid,'-&

26、#39;,1,1)-instr(jc.batchid,'.',1,2)-1)='4'update T_JYZRHT_DOCINDEX jc set jc.classid=23 WHERE substr(jc.batchid,instr(jc.batchid,'.',1,2)+1,instr(jc.batchid,'-',1,1)-instr(jc.batchid,'.',1,2)-1)='5'update T_JYZRHT_DOCINDEX jc set jc.classid=24 WHERE su

27、bstr(jc.batchid,instr(jc.batchid,'.',1,2)+1,instr(jc.batchid,'-',1,1)-instr(jc.batchid,'.',1,2)-1)='6'update T_JYZRHT_DOCINDEX jc set jc.classid=25 WHERE substr(jc.batchid,instr(jc.batchid,'.',1,2)+1,instr(jc.batchid,'-',1,1)-instr(jc.batchid,'.

28、9;,1,2)-1)='7'update T_JYZRHT_DOCINDEX jc set jc.classid=26 WHERE substr(jc.batchid,instr(jc.batchid,'.',1,2)+1,instr(jc.batchid,'-',1,1)-instr(jc.batchid,'.',1,2)-1)='8'update T_JYZRHT_DOCINDEX jc set jc.classid=27 WHERE substr(jc.batchid,instr(jc.batchid,

29、9;.',1,2)+1,instr(jc.batchid,'-',1,1)-instr(jc.batchid,'.',1,2)-1)='9'update T_JYZRHT_DOCINDEX jc set jc.classid=30 WHERE substr(jc.batchid,instr(jc.batchid,'.',1,2)+1,instr(jc.batchid,'-',1,1)-instr(jc.batchid,'.',1,2)-1)='10'update T_JYZR

30、HT_DOCINDEX jc set jc.catalogno=jc.mlh;-更新案卷号，取batchid第二个'-'后的部分update T_JYZRHT_DOCINDEX jc set jc.ajh=substr(jc.batchid,instr(jc.batchid,'-',1,2)+1)-是否存在空的案卷号select * from T_JYZRHT_DOCINDEX jc where jc.ajh is null-更新档案件数，同一个文档包中的件数select jc.batchid,count(*) from T_JYZRHT_DOCINDEX jc

31、 group by jc.batchid -t_batchinfo作业单位提供的，如果没有提供就没有，页数就不能计算了update T_JYZRHT_DOCINDEX jc set jc.jianshu=(select b.docnum from t_batchinfo b where b.batchid=jc.batchid);update T_JYZRHT_DOCINDEX jc set jc.zys=(select b.papernum from t_batchinfo b where b.batchid=jc.batchid)select * from T_JYZRHT_DOCINDE

32、X jc where jc.jianshu is null-查找不重复的目录号select distinct jc.mlh from T_JYZRHT_DOCINDEX jc order by jc.mlh;-导入到 ta_catalognoinsert into ta_catalogno cno (cno.catalogno,cno.classid,cno.curboxno,cno.recordesign,cno.status) (select distinct jc.mlh,cdf.classid,1,1,1 from T_JYZRHT_DOCINDEX jc join ta_classd

33、ef cdf on cdf.classcode=jc.bmh);l 转让 需添加“ZRBH“扩展索引字段；l 检查档案号是否重复SELECT count(*) FROM TA_ARCHIVE where archiveno in (select 'JE'|substr(jc.batchid,instr(jc.batchid,'.',1,2)+1,instr(jc.batchid,'-',1,1)-instr(jc.batchid,'.',1,2)-1)|'-'|jc.mlh|'-'|jc.ajh f

34、rom T_JYZRHT_DOCINDEX jc)如果没有重复，则可执行工具。4. 用地导入时由于数据问题需注意：8 将T_YD_DOCINDEX表中YH 字段长度设置为100；否则有些数据导入不进数据库8 导入后将部分数据修改下如下：select batchid,yh,length(yh) from T_YD_DOCINDEX order by length(yh) desc;将batchid为红线框住的三条记录的YH字段修改下，设置为空或者0；8 共计将增加档案792条；select count(distinct batchid) from T_YD_DOCINDEX;8 临时表数据处理s

35、elect t.* from t_yd_docindex t order by t.batchid,t.docname-更新分类号为“E”update t_yd_docindex t set t.flh='E'-更新编目号update t_yd_docindex t set t.bmh=substr(t.batchid,1,instr(t.batchid,'.',1,1)-1); -更新到目录号update t_yd_docindex t set t.mlh=substr(t.batchid,instr(t.batchid,'.',1,1)+1,

36、instr(t.batchid,'-',1,2)-instr(t.batchid,'.',1,1)-1)select * from t_yd_docindex t where t.mlh is null-设置索引表的 ARCHIVETYPE CLASSID CATALOGNO三个的值update t_yd_docindex t set t.archivetype=4;update t_yd_docindex t set t.classid=(select cls.classid from ta_classdef cls where cls.classcode=t

37、.bmh);update t_yd_docindex t set t.catalogno=t.mlh;-更新案卷号update t_yd_docindex t set t.ajh=substr(t.batchid,instr(t.batchid,'-',1,2)+1)-查找不重复的目录号select distinct t.mlh from t_yd_docindex t order by t.mlh;-导入到 ta_catalognoselect distinct t.mlh from t_yd_docindex t where t.mlh is not nullinsert

38、into ta_catalogno cno (cno.catalogno,cno.classid,cno.curboxno,cno.recordesign,cno.status) (select distinct yd.mlh,cdf.classid,1,1,1 from t_yd_docindex yd join ta_classdef cdf on cdf.classcode=yd.bmh);5. 信访8 临时表数据处理：select count(*) from T_XF_DOCINDEX jc order by jc.ajh descselect jc.* from T_XF_DOCIN

39、DEX jc order by jc.ajh descselect jc.* from T_XF_DOCINDEX jc order by jc.batchid,jc.docname-更新到分类号 FLH 字段，为“F”update T_XF_DOCINDEX jc set jc.flh='F'-更新到编目号 BMH 字段update T_XF_DOCINDEX jc set jc.bmh='A9'-更新到目录号 MLH update T_XF_DOCINDEX jc set jc.mlh=substr(jc.batchid,instr(jc.batchid,&

40、#39;-',1,1)+1,instr(jc.batchid,'-',1,2)-instr(jc.batchid,'-',1,1)-1)-设置索引表的 ARCHIVETYPE CLASSID CATALOGNO三个的值update T_XF_DOCINDEX jc set jc.archivetype=7;update T_XF_DOCINDEX jc set jc.classid=18;update T_XF_DOCINDEX jc set jc.catalogno=jc.mlh;-更新案卷号，取batchid第二个'-'后的部分upd

41、ate T_XF_DOCINDEX jc set jc.ajh=substr(jc.batchid,instr(jc.batchid,'-',1,2)+1)-是否存在空的案卷号select * from T_XF_DOCINDEX jc where jc.ajh is null-更新档案件数，同一个文档包中的件数select jc.batchid,count(*) from T_XF_DOCINDEX jc group by jc.batchid -t_batchinfo作业单位提供的，如果没有提供就没有，页数就不能计算了update T_XF_DOCINDEX jc set

42、jc.jianshu=(select b.docnum from t_batchinfo b where b.batchid=jc.batchid);update T_XF_DOCINDEX jc set jc.zys=(select b.papernum from t_batchinfo b where b.batchid=jc.batchid)select * from T_XF_DOCINDEX jc where jc.jianshu is null-查找不重复的目录号select distinct jc.mlh from T_XF_DOCINDEX jc order by jc.mlh

43、;-导入到 ta_catalognoinsert into ta_catalogno cno (cno.catalogno,cno.classid,cno.curboxno,cno.recordesign,cno.status) (select distinct jc.mlh,cdf.classid,1,1,1 from T_XF_DOCINDEX jc join ta_classdef cdf on cdf.classcode=jc.bmh);6. 宗地8 临时表数据处理select t.* from t_dj_docindex t order by t.batchid,t.docname-

44、更新分类号为“E”update t_dj_docindex t set t.flh='C'-更新编目号update t_dj_docindex set t.bmh=substr(t.batchid,1,instr(t.batchid,'.',1,1)-1); -更新到目录号update t_dj_docindex t set t.mlh= substr(t.batchid,5,19);select * from t_dj_docindex t where t.mlh is null-设置索引表的 ARCHIVETYPE CLASSID CATALOGNO三个的值

45、update t_dj_docindex t set t.archivetype=2;update t_dj_docindex t set t.classid=1;update t_dj_docindex t set t.catalogno=t.mlh;-查找不重复的目录号select distinct t.mlh from t_yd_docindex t order by t.mlh;-更新docname值，需将其与附件名保持一致update t_dj_docindex set docname=附件名称（很重要）；-导入到 ta_catalognoselect distinct t.mlh

46、from t_yd_docindex t where t.mlh is not null；insert into ta_catalogno cno (cno.catalogno,cno.classid,cno.curboxno,cno.recordesign,cno.status) (select distinct yd.mlh,cdf.classid,1,1,1 from t_yd_docindex yd join ta_classdef cdf on cdf.classcode=yd.bmh);7. 查封delete from t_cf_docindex t select * from ta_clas