导入文本文件

我现在有一个文本文件,其中数据格式为:
0274780,北京紫光小店,110102441122042,0200
0274781,北京仁和摄影,110102691209206,0200
0274782,北京华西餐厅,110102660030206,0200                                                                                                                                                       ／*结束*/
想导入表OldTaxPayer中。表OldTaxPayer结构为：
PCCODE Char(8),NAME Varchar2(60),TAXREGISTRYNO Char(18),
SUBSTATIONCODE Char(4)。
文本文件中的数据按照这种顺序，但是PCCODE只有7字节，TAXREGISTRYNO只有15字节。请教怎样导入这些数据，谢谢！

14 个解决方案

#1

用sqlloader，倒入格式化文本。这是oracle提供的一个工具
非常简单，查一下相关资料。

#2

CREATE OR REPLACE PROCEDURE IMPFROMTXT()AS
  T_PCODE OldTaxPayer.PCODE%TYPE;
  T_NAME  OldTaxPayer.PNAME%TYPE;
  T_TAXREGISTRYNO OldTaxPayer.TAXREGISTRYNO%TYPE;
  T_SUBSTATIONCODE OldTaxPayer.SUBSTATIONCODE%TYPE;
  TMPSTR VARCHAR2(200);
   J NUMBER;
   K NUMBER;
BEGIN
FILE:=UTL_FILE.FOPEN('C:\AA','FILNAME.DAT','R');
   DELETE OldTaxPayer;
   LOOP
    UTL_FILE.GET_LINE(FILE,TMPSTR);
    EXIT WHEN (INSTR(TMPSTR,',',1)=0);
    J:=INSTR(TMPSTR,',',1,1);
    T_PCCODE:=LTRIM(SUBSTR(TMPSTR,1,J-1));
    K:=J+1;
    J:=INSTR(TMPSTR,',',1,2);
    T_NAME:=LTRIM(SUBSTR(TMPSTR,K,J-K));
    K:=J+1;
    J:=INSTR(TMPSTR,',',1,3);
    T_:=LTRIM(SUBSTR(TMPSTR,K,J-K));
    K:=J+1;
    J:=INSTR(TMPSTR,',',1,4);
    T_TAXREGISTRYNO:=LTRIM(SUBSTR(TMPSTR,K,J-K));
    K:=J+1;
    J:=INSTR(TMPSTR,',',1,5);
    T_SUBSTATIONCODE:=LTRIM(SUBSTR(TMPSTR,K,LENGTH(TMPSTR)-K+1));
    INSERT INTO OldTaxPayer(PCCODE,NAME,TAXREGISTRYNO,SUBSTATIONCODE)
    VALUES(T_PCCODE,T_NAME,T_TAXREGISTRYNO,T_SUBSTATIONCODE);
  END LOOP;
  UTL_FILE.FCLOSE(FILE);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
  UTL_FILE.FCLOSE(FILE);
  COMMIT;
  WHEN OTHERS THEN
  NULL;
END;

#3

注意：不过此过程中文件必须放在服务端。

#4

就用sql server 的DTS倒也很不错，如果表的字段太小，就用PLSQL develpment修改一下。

#5

还是SQL*loader比较好用

#6

多谢各位，因为这个功能要的很急而我对Oracle又不熟，所以希望能说得详细些。
3yugui(我不适合搞it) 兄，我在数据库中添加你的存储过程，提示创建的过程带有编译错误。

我按照http://www.oradb.net/tran/fox2ora_001.htm中所说，更名为*.ctl,
添加以下代码
LOAD DATA
   INFILE *
   APPEND
   INTO TABLE bjds.oldtaxpayer
   FIELDS TERMINATED BY ","
   (pccode,name,taxregistryno,substationcode)
   BEGINDATA
.....数据。

执行 sqlldr userid=system/manager@taxdb control=d:\q.ctl log=d:\q.log bad=d:\q.bad
显示：
达到提交点，逻辑记录计数21
达到提交点，逻辑记录计数22
在q.log中提示信息为：
记录 1: 被拒绝 - 表 BJDS.OLDTAXPAYER 的列 PCCODE 出现错误。
在逻辑记录结束之前未找到列（使用 TRAILING NULLCOLS）
记录 2: 被拒绝 - 表 BJDS.OLDTAXPAYER 的列 SUBSTATIONCODE 出现错误。
ORA-01401: 插入的值对于列过大
不知我错在哪里？

#7

首先你的文本文件要工整，去除那些空格（因为空格占字节输，
你明明看到‘0200’是4个字节，但它后面有大量的空格，
实际上它不止4个字节）。

0274780,北京紫光小店,110102441122042,0200
0274781,北京仁和摄影,110102691209206,0200
0274782,北京华西餐厅,110102660030206,0200

检查你的表结构，这四个字段（pccode,name,taxregistryno,substationcode）
的宽度，是否分别大于等于7、12、15、4

还要检查你的汉字的长度，用SELECT LENGTHB(NAME) FROM bjds.oldtaxpayer;
看看有汉字的长度是多少，比如NAME 的值为‘北京’而
SELECT LENGTHB(rtrim(ltrim(NAME)))
FROM bjds.oldtaxpayer;的结果若为 6
那么你的字符集就有问题了。若为 4 那么就没问题。

上述问题解决后
假设你的文本文件名为TEST.TXT
那么再建立一个控制文件 TEST.CTL
LOAD DATA
INFILE 'TEST.TXT'
APPEND
INTO TABLE bjds.oldtaxpayer
FIELDS TERMINATED BY ','
(pccode,name,taxregistryno,substationcode)

把控制文件和文本文件放在统一个目录下，如 \ORACLE\ORA81\BIN\ 下

最后执行

sqlldr userid=system/manager@taxdb control=test.ctl

好运哦！

#8

ORA-01401: 插入的值对于列过大

也就是说你的列宽度如为8，而你要插入一个值的宽度为9，那就不行了。

#9

谢谢ATCG(ATCG).我把那些空格删掉,然后按你的语句,载入成功.
附加问题,不知道能不能在程序里去掉空格? :)

#10

你指的是什么程序？PL/SQL存储过程？

rtrim(ltrim(FIELD))

#11

我的意思是在
LOAD DATA
INFILE 'TEST.TXT'
APPEND
INTO TABLE bjds.oldtaxpayer
FIELDS TERMINATED BY ','
(pccode,name,taxregistryno,substationcode)
中处理，不知有没有办法？

#12

对呀，你不用考虑空格什么的，你可以指定数据的起始位和结束位，这个也是sqlloader的用法

#13

1、用POSITION来指定相对位置即可
如substationcode POSITION(38:41) VARCHAR2(4)
2、指定FIELD的界定符，
substationcode terminated by whitespace

详细信息可参阅相关书籍，书上说得很详细的。

#14

ok，多谢各位。结贴

#1