數(shù)據(jù)導(dǎo)入文檔

1、檔案數(shù)據(jù)入庫(kù)操作一、 文書數(shù)據(jù)入庫(kù)1. 刪除原有表格內(nèi)容選中表T_WS_DOCINDEX內(nèi)所有內(nèi)容進(jìn)行刪除2. 導(dǎo)入mdb索引也可能是excle文件（1）打開PLSQL，工具ODBC導(dǎo)入器： （2）ODBC導(dǎo)入器具體設(shè)置：“來自O(shè)DBC的數(shù)據(jù)”：8 選擇“MS Access Database”；8 輸入數(shù)據(jù)庫(kù)登陸信息；8 點(diǎn)擊connect ，選擇需要導(dǎo)入的mdb文件；8 選擇具體的表。如下圖所示填寫選擇并導(dǎo)入正確的索引表 “到Oracle的數(shù)據(jù)”： 8 輸入用戶與導(dǎo)入的數(shù)據(jù)庫(kù)表名；8 指定字段對(duì)應(yīng)關(guān)系以及字段類型；8 點(diǎn)擊import導(dǎo)入。如下圖所示填寫選擇。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條，因此導(dǎo)入后將增加文書檔案43條；SELECT count(distinct batchid) FROM T_WS_DOCINDEX8 因pdf命名與mdb表中的docname存在出入，執(zhí)行以下語(yǔ)句使其保持一致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 臨時(shí)表處理select ws.* from t_ws_docindex ws order by ws.batchid,ws.docname（2） 字段填充：-更新分類號(hào)為“A”update t_ws_docindex ws set ws.flh='A'update t_ws_docindex ws set ws.bmh=substr(ws.batchid,0,2);-更新到目錄號(hào)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);-查找不重復(fù)的編目號(hào)、目錄號(hào)select distinct ws.bmh,ws.mlh from t_ws_docindex ws order by ws.bmh-導(dǎo)入到 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. 檔案入庫(kù)（1）工具應(yīng)用： 8 PDF源文件路徑選擇 8 目標(biāo)文件夾和日志文件夾自行建立 8 Oracle數(shù)據(jù)庫(kù)相關(guān)數(shù)據(jù)填寫 8 選擇相應(yīng)的索引數(shù)據(jù)表名點(diǎn)擊導(dǎo)入按鈕進(jìn)行數(shù)據(jù)導(dǎo)入,具體參數(shù)如下圖所示：（3） 將目標(biāo)文件夾中生成的文件復(fù)制到F:SouthArchiveFTPArchiveFTPme_official文件夾中。二、 其他數(shù)據(jù)入庫(kù)其他數(shù)據(jù)入庫(kù)的方式流程與文書數(shù)據(jù)一致，不過在刪除的表格也要選擇與之相匹配的表格內(nèi)容，使用ODBC導(dǎo)入器時(shí)應(yīng)注意將要導(dǎo)入的索引文件的格式并選擇相應(yīng)的“用戶/系統(tǒng)”。下面是各數(shù)據(jù)字段填充的腳本文件。1. 監(jiān)察8 檢查batchid

9、與目錄名是否對(duì)應(yīng)8 判斷是否已經(jīng)有相同batchid的檔案SELECT count(*) FROM TA_ARCHIVE where archiveno in (select batchid from t_jc_docindex)如為0時(shí)繼續(xù)往下操作；8 處理t_jc_docindex執(zhí)行腳本“索引數(shù)據(jù)處理_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-更新到分類號(hào) FLH 字段，為“F”update t_jc_docindex jc set jc.flh='F'-更新到編目號(hào) BMH 字段update t_jc_docindex jc set jc.bmh='F'-更新到目錄號(hào) 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)-設(shè)置索引表的 ARCHIVETYPE CLASSID CATALOGNO三個(gè)的值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;-更新案卷號(hào)，取batchid第二個(gè)'-'后的部分update t_jc_docindex jc set jc.ajh

12、=substr(jc.batchid,instr(jc.batchid,'-',1,2)+1)-是否存在空的案卷號(hào)select * from t_jc_docindex jc where jc.ajh is null-更新檔案件數(shù)，同一個(gè)文檔包中的件數(shù)select jc.batchid,count(*) from t_jc_docindex jc group by jc.batchid -t_batchinfo作業(yè)單位提供的，如果沒有提供就沒有，頁(yè)數(shù)就不能計(jì)算了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-查找不重復(fù)的目錄號(hào)select distinct jc.mlh from t_jc_docindex jc order by jc.mlh;-導(dǎo)入到 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. 交易出讓、劃撥-出讓劃撥數(shù)據(jù)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;-更新到分類號(hào) FLH 字段，為“JE”update t_jyclht_docindex jc set jc.flh='JE'-更新到編目號(hào) 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'-更新到目錄號(hào) 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)-設(shè)置索引表的 ARCHIVET

17、YPE CLASSID CATALOGNO三個(gè)的值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;-更新案卷號(hào)，取batchid第二個(gè)'-'后的部分update t_jyclht_docindex jc set jc.ajh=substr(jc.batchid,instr(jc.batchid,'-',1,2)+1)-是否存在空的案卷號(hào)select * from t_jyclht_docindex jc where jc.ajh is null-更新檔案件數(shù)，同一個(gè)文檔包中的件數(shù)select jc.batchid,count(*

19、) from t_jyclht_docindex jc group by jc.batchid -t_batchinfo作業(yè)單位提供的，如果沒有提供就沒有，頁(yè)數(shù)就不能計(jì)算了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-查找不重復(fù)的目錄號(hào)select distinct jc.mlh from t_jyclht_docindex jc order by jc.mlh;-導(dǎo)入到 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 檢查檔案號(hào)是否重復(fù)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. 交易（轉(zhuǎn)讓、轉(zhuǎn)拍、掛牌、轉(zhuǎn)掛、礦出、其他、轉(zhuǎn)讓舊、轉(zhuǎn)掛舊）類型分類標(biāo)識(shí)編目標(biāo)識(shí)編目原數(shù)據(jù)量轉(zhuǎn)

22、讓521JE32450轉(zhuǎn)拍522JE410掛牌523JE5198轉(zhuǎn)掛524JE6249礦出525JE711其他526JE80轉(zhuǎn)讓舊527JE918轉(zhuǎn)掛舊530JE1088 臨時(shí)表數(shù)據(jù)處理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-更新到分類號(hào) FLH 字段，為“JE

23、”update T_JYZRHT_DOCINDEX jc set jc.flh='JE'-更新到編目號(hào) BMH 字段(轉(zhuǎn)讓 JE3，轉(zhuǎn)拍 JE4，掛牌 JE5，轉(zhuǎn)掛 JE5，礦出 JE7，其他 JE8，轉(zhuǎn)讓舊 JE9，轉(zhuǎn)掛舊 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) ;-更新到目錄號(hào) 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)-設(shè)置索引表的 ARCHIVETYPE CLASSID CATALOGNO三個(gè)的值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;-更新案卷號(hào)，取batchid第二個(gè)'-'后的部分update T_JYZRHT_DOCINDEX jc set jc.ajh=substr(jc.batchid,instr(jc.batchid,'-',1,2)+1)-是否存在空的案卷號(hào)select * from T_JYZRHT_DOCINDEX jc where jc.ajh is null-更新檔案件數(shù)，同一個(gè)文檔包中的件數(shù)select jc.batchid,count(*) from T_JYZRHT_DOCINDEX jc

31、 group by jc.batchid -t_batchinfo作業(yè)單位提供的，如果沒有提供就沒有，頁(yè)數(shù)就不能計(jì)算了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-查找不重復(fù)的目錄號(hào)select distinct jc.mlh from T_JYZRHT_DOCINDEX jc order by jc.mlh;-導(dǎo)入到 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 轉(zhuǎn)讓 需添加“ZRBH“擴(kuò)展索引字段；l 檢查檔案號(hào)是否重復(fù)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)如果沒有重復(fù)，則可執(zhí)行工具。4. 用地導(dǎo)入時(shí)由于數(shù)據(jù)問題需注意：8 將T_YD_DOCINDEX表中YH 字段長(zhǎng)度設(shè)置為100；否則有些數(shù)據(jù)導(dǎo)入不進(jìn)數(shù)據(jù)庫(kù)8 導(dǎo)入后將部分?jǐn)?shù)據(jù)修改下如下：select batchid,yh,length(yh) from T_YD_DOCINDEX order by length(yh) desc;將batchid為紅線框住的三條記錄的YH字段修改下，設(shè)置為空或者0；8 共計(jì)將增加檔案792條；select count(distinct batchid) from T_YD_DOCINDEX;8 臨時(shí)表數(shù)據(jù)處理s

35、elect t.* from t_yd_docindex t order by t.batchid,t.docname-更新分類號(hào)為“E”update t_yd_docindex t set t.flh='E'-更新編目號(hào)update t_yd_docindex t set t.bmh=substr(t.batchid,1,instr(t.batchid,'.',1,1)-1); -更新到目錄號(hào)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-設(shè)置索引表的 ARCHIVETYPE CLASSID CATALOGNO三個(gè)的值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;-更新案卷號(hào)update t_yd_docindex t set t.ajh=substr(t.batchid,instr(t.batchid,'-',1,2)+1)-查找不重復(fù)的目錄號(hào)select distinct t.mlh from t_yd_docindex t order by t.mlh;-導(dǎo)入到 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 臨時(shí)表數(shù)據(jù)處理：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-更新到分類號(hào) FLH 字段，為“F”update T_XF_DOCINDEX jc set jc.flh='F'-更新到編目號(hào) BMH 字段update T_XF_DOCINDEX jc set jc.bmh='A9'-更新到目錄號(hào) 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)-設(shè)置索引表的 ARCHIVETYPE CLASSID CATALOGNO三個(gè)的值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;-更新案卷號(hào)，取batchid第二個(gè)'-'后的部分upd

41、ate T_XF_DOCINDEX jc set jc.ajh=substr(jc.batchid,instr(jc.batchid,'-',1,2)+1)-是否存在空的案卷號(hào)select * from T_XF_DOCINDEX jc where jc.ajh is null-更新檔案件數(shù)，同一個(gè)文檔包中的件數(shù)select jc.batchid,count(*) from T_XF_DOCINDEX jc group by jc.batchid -t_batchinfo作業(yè)單位提供的，如果沒有提供就沒有，頁(yè)數(shù)就不能計(jì)算了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-查找不重復(fù)的目錄號(hào)select distinct jc.mlh from T_XF_DOCINDEX jc order by jc.mlh

43、;-導(dǎo)入到 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 臨時(shí)表數(shù)據(jù)處理select t.* from t_dj_docindex t order by t.batchid,t.docname-

44、更新分類號(hào)為“E”update t_dj_docindex t set t.flh='C'-更新編目號(hào)update t_dj_docindex set t.bmh=substr(t.batchid,1,instr(t.batchid,'.',1,1)-1); -更新到目錄號(hào)update t_dj_docindex t set t.mlh= substr(t.batchid,5,19);select * from t_dj_docindex t where t.mlh is null-設(shè)置索引表的 ARCHIVETYPE CLASSID CATALOGNO三個(gè)的值

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;-查找不重復(fù)的目錄號(hào)select distinct t.mlh from t_yd_docindex t order by t.mlh;-更新docname值，需將其與附件名保持一致update t_dj_docindex set docname=附件名稱（很重要）；-導(dǎo)入到 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