1 用UTL_FILE包:例子如下:
create or replace procedure txt_into as
f UTL_FILE.file_type;
path varchar2(50);
name varchar2(30);
s varchar2(200);
SHRQ VARCHAR2(10);
KHDM VARCHAR2(30);
KHMC VARCHAR2(30);
SPDM VARCHAR2(30);
SPMC VARCHAR2(30);
SL00 NUMBER;
N NUMBER;
K NUMBER;
I NUMBER;
begin
path:='f:';
name:='1.csv';
f:=utl_file.fopen('F:','1.CSV','R');
I:=0;
loop
utl_file.get_line(f,s);
I:=I+1;
N:=INSTR(S,',',1,1);
K:=N+1;
SHRQ:=SUBSTR(S,1,N-1);
N:=INSTR(S,',',1,2);
KHDM:=LTRIM(SUBSTR(S,K,N-1));
K:=N+1;
N:=INSTR(S,',',1,3);
KHMC:=LTRIM(SUBSTR(S,K,N-1));
K:=N+1;
N:=INSTR(S,',',1,4);
SPDM:=LTRIM(SUBSTR(S,K,N-1));
K:=N+1;
N:=INSTR(S,',',1,2);
SPMC:=LTRIM(SUBSTR(S,K,N-1));
K:=N+1;
N:=INSTR(S,',',1,2);
SL00:=TO_NUMBER(SUBSTR(S,K,N-1));
K:=N+1;
INSERT INTO TMPDATE VALUES(SHRQ,KHDM,KHMC,SPDM,SPMC,SL00);
IF I=5000 THEN
COMMIT;
I:=0;
END IF;
end loop;
utl_file.fclose(f);
COMMIT;
EXCEPTION
when NO_DATA_FOUND then
utl_file.fclose(f);
COMMIT;
end;
/2 用delphi写一个读入过程:
procedure TForm1.Button1Click(Sender: TObject);
var
f:TextFile;
s:String; SHRQ: String;
KHDM: String;
KHMC: String;
SPDM: String;
SPMC: String;
SL00: String;
N : Integer;
I : Integer;begin if OpenD_1.Execute then
begin
AssignFile(F, OpenD_1.FileName); { File selected in dialog }
Reset(F);
end; I:=0;
repeat
Readln(F, S);
I:=I+1;
N:=POS(',',S);
SHRQ:=copy(S,1,N-1);
S:=copy(S,N+1,Length(S));
N:=POS(',',S);
KHDM:=copy(S,1,N-1);
S:=copy(S,N+1,Length(S));
N:=POS(',',S);
KHMC:=copy(S,1,N-1);
S:=copy(S,N+1,Length(S));
N:=POS(',',S);
SPDM:=copy(S,1,N-1);
S:=copy(S,N+1,Length(S));
N:=POS(',',S);
SPMC:=copy(S,1,N-1);
S:=copy(S,N+1,Length(S));
SL00:=S;
ADOinsert.SQL.Clear;
ADOinsert.SQL.Add('INSERT INTO TMPDATE(shrq00,khdm00,khmc00,spdm00,spmc00,sl0000) VALUES('''+SHRQ+''','''+KHDM+''','''+KHMC+''','''+SPDM+''','''+SPMC+''','+SL00+')');
ADOinsert.ExecSQL;
label1.Caption:=IntToStr(i);
until Eof(F); CloseFile(f);
end;
create or replace procedure txt_into as
f UTL_FILE.file_type;
path varchar2(50);
name varchar2(30);
s varchar2(200);
SHRQ VARCHAR2(10);
KHDM VARCHAR2(30);
KHMC VARCHAR2(30);
SPDM VARCHAR2(30);
SPMC VARCHAR2(30);
SL00 NUMBER;
N NUMBER;
K NUMBER;
I NUMBER;
begin
path:='f:';
name:='1.csv';
f:=utl_file.fopen('F:','1.CSV','R');
I:=0;
loop
utl_file.get_line(f,s);
I:=I+1;
N:=INSTR(S,',',1,1);
K:=N+1;
SHRQ:=SUBSTR(S,1,N-1);
N:=INSTR(S,',',1,2);
KHDM:=LTRIM(SUBSTR(S,K,N-1));
K:=N+1;
N:=INSTR(S,',',1,3);
KHMC:=LTRIM(SUBSTR(S,K,N-1));
K:=N+1;
N:=INSTR(S,',',1,4);
SPDM:=LTRIM(SUBSTR(S,K,N-1));
K:=N+1;
N:=INSTR(S,',',1,2);
SPMC:=LTRIM(SUBSTR(S,K,N-1));
K:=N+1;
N:=INSTR(S,',',1,2);
SL00:=TO_NUMBER(SUBSTR(S,K,N-1));
K:=N+1;
INSERT INTO TMPDATE VALUES(SHRQ,KHDM,KHMC,SPDM,SPMC,SL00);
IF I=5000 THEN
COMMIT;
I:=0;
END IF;
end loop;
utl_file.fclose(f);
COMMIT;
EXCEPTION
when NO_DATA_FOUND then
utl_file.fclose(f);
COMMIT;
end;
/2 用delphi写一个读入过程:
procedure TForm1.Button1Click(Sender: TObject);
var
f:TextFile;
s:String; SHRQ: String;
KHDM: String;
KHMC: String;
SPDM: String;
SPMC: String;
SL00: String;
N : Integer;
I : Integer;begin if OpenD_1.Execute then
begin
AssignFile(F, OpenD_1.FileName); { File selected in dialog }
Reset(F);
end; I:=0;
repeat
Readln(F, S);
I:=I+1;
N:=POS(',',S);
SHRQ:=copy(S,1,N-1);
S:=copy(S,N+1,Length(S));
N:=POS(',',S);
KHDM:=copy(S,1,N-1);
S:=copy(S,N+1,Length(S));
N:=POS(',',S);
KHMC:=copy(S,1,N-1);
S:=copy(S,N+1,Length(S));
N:=POS(',',S);
SPDM:=copy(S,1,N-1);
S:=copy(S,N+1,Length(S));
N:=POS(',',S);
SPMC:=copy(S,1,N-1);
S:=copy(S,N+1,Length(S));
SL00:=S;
ADOinsert.SQL.Clear;
ADOinsert.SQL.Add('INSERT INTO TMPDATE(shrq00,khdm00,khmc00,spdm00,spmc00,sl0000) VALUES('''+SHRQ+''','''+KHDM+''','''+KHMC+''','''+SPDM+''','''+SPMC+''','+SL00+')');
ADOinsert.ExecSQL;
label1.Caption:=IntToStr(i);
until Eof(F); CloseFile(f);
end;
解决方案 »
- 关于将ORACLE审计的表移动到其它表空间的问题
- 如何在windows2003下安装oracle9i
- 关于decode
- 初学者,关于角色和权限的问题搞不清楚
- PRO*C的问题:EXEC SQL COMMIT什么时候用,什么时候不用啊
- windws XP home edition 能否安装Oracle9i database,Oracle9IAS?
- 启动数据库时报 监听程序无法启动专用服务器进程???ORACLE 9.2.0.1.0
- pro*C/C++ 怎么预编译C++程序?
- 高手回答,有关于oracle8的系统问题?
- 在OEM 中如何建立DBLINK
- 求助!修改SHARE_POOL_SIZE的方法
- Oracle8i 在p4 系统是2000 安装出错???
用sqlserver的DTS,指定源数据和目标数据
当然,你的文本要有一定的格式,在DTS里根据格式来调节一下就行了,很好用的,什么程序都不用写,我处理导入导出很多都是用DTS来做的,强烈推荐
假如该文本(test.txt)中的数据是用‘,’分开的,文件存放在D:\下,
数据将导入TEST表,先编控制文件
test.ctl
-----------------------------------------------------
load data
infile 'd:\test.txt'
into table test
fileds terminated by ',' optionally enclosed by '"'
(col1,col2,col3)
------------------------------------------------------
键入下列命令激活sql*loader:
sqlldr system/manager control=test.ctl log=test.log
bad=test.bad discard=test.dsc
http://expert.csdn.net/Expert/topic/2022/2022582.xml?temp=.4900782