用Oracle sqlldr导入文本文件TXT 总结 xiali

最近在处理一批文本数据，规定按照接口说明导入到ORACLE数据库中，经过最近的研究，解决了一些问题，觉得我必要总结一下：

 

常规资料：

 

----------------------------------------------------

Dos 环境下使用SQl*Loader命令 加载

使用其它数据库的数据转移工具

Oracle 企业管理器中的数据加载功能

具体的技术实现

一、Dos 环境下加载

1、首先，服务器端的侦听服务必须已经开启。

测试方法：Dos 下输入

C:/>sqlplus username/password@serviceName

2、然后使用 Oracle 的 sqlldr 命令进行数据的导入

前期条件

1） Oracle 数据库端必须已经建好了需要导入的数据表的结构

2） 一个数据源文件 下面例子中为制表符分隔的文本文件 model.txt ,为Excel 表中导出的

3） 手工编辑一个XXX.CTL 的控制文件

4） 命令行加载数据

如下实例：

以下文件缺省放到C:/ 下，如果不是，就需要指明全路径

1． 命令控制文件 input.ctl 内容

命令 
说明

load data 
1、控制文件标识

infile 'model.txt' 
2、要输入的数据文件名为test.txt

append into table system.塔位属性表 
3、向表test中追加记录

fields terminated by X'09' 
4、指定分隔符，字段终止于X'09'，是一个制表符（TAB）

(编号,名称,大小) 
5、定义列对应表中顺序

 

控制文件中指定插入数据的方式关键字

insert，为缺省方式，在数据装载开始时要求表为空

append，在表中追加新记录

replace，删除旧记录，替换成新装载的记录

truncate，同上

在 Dos 窗口下使用 SQl*Loader 命令实现数据的导入

C:/>sqlldr userid=system/manager@ serviceName control=input.ctl

默认日志文件名为：input.log

默认坏记录文件为：input.bad

二、使用其它数据库转移工具

以下以SQL Server 导入导出向导为例

1、在数据的导入导出向导中设置数据源服务器，实例中选择数据源选择SQL Server

2、然后指定要导入的Oracle 数据源

3、需要配置Oracle 的属性信息

需要注意的是，登录数据库的用户信息即为数据导入之后的方案名，即导入之后的SQL Server 中的表在Oracle 中标志名为 username.表名

以下按照提示即可，可以完全导入SQl Server 中的数据表和视图，也可以使用查询语句返回你要选择的列或者行。

三、Oracle 企业管理器中的数据加载功能

登录Oracle 的控制台界面，针对单独的数据表可以使用数据加载工具

中间需要指定控制文件等，同Dos 加载一致，不再重复

 

----------------------------------------------------

向Oracle中导入文本数据时使用的控制文件格式
 
无论是使用上一篇中的哪种方式都需要有一个控制文件，下面是控制文件（ctl文件）书写的基本格式：
    
 命令
 说明
 
load data
 1、控制文件标识
 
infile 'testl.txt'　
 2、要输入的数据文件名为test.txt，此时是要导入的数据文件同控制文件在同一路径下，如果不在同一路径下则需要写完整路径名
 
append into table 表名（可以是全名也可以是同义词）
 3、向表test中追加记录
 
fields terminated by X'09'
 4、指定分隔符，字段终止于X'09'   (很重要)，是一个制表符（TAB），如果用逗号分割就将X'09'替换为','
 
(编号,名称,大小)
 5、定义列对应表中顺序
 

  控制文件中指定插入数据的方式关键字

                           insert，为缺省方式，在数据装载开始时要求表为空

                           append，在表中追加新记录

                           replace，删除旧记录，替换成新装载的记录

                           truncate，同上

控制文件的示例：
load data
infile 'test.txt'
append into table test.test
fields terminated by X'09'
(test,test1,test2)

对有时间类型的数据导入的示例控制文件：
load data 
infile 'h:/TB_FACT_PHS_TICKET_DAY.txt'
Append into TABLE TB_FACT_PHS_TICKET_DAY
fields terminated by X'09'
(Time_Id            
,Region_Id         
,Cust_Type_Id      
,Prod_Type_Id      
,Acct_Item_Type_Id 
,Acct_Item_Type_Cd 
,Exchange97_Cd     
,Latn_Cd           
,Call_Duration     
,Access_In_Duration
,Income
,In_Date  Date "YYYY-MM-DD"
)

文件导入命令                    C:/>sqlldr userid=test/test@test control=test.ctl(此时控制文件test.ctl存在C:/路径下)
在命令控制符下进入Oracle        C:/>sqlplus username/password@serviceName

 

 

oracle导入txt数据文件2008年07月30日 星期三 17:21把txt文件格式的数据文件导入oracle的方法是利用sqlloader工具。

第一步：把文本格式的数据文件放入C盘。如,test.txt
第二步：建立控制文件append.ctl。（名字可以随便取,放C盘下）

append.ctl的内容如下：
　　load data　　　　　　　　　　   --1、控制文件标识 
   　infile   'test.txt'　　　　　　 --2、要输入的数据文件名为test.txt 
   　append into table CTXSYS.test　　　--3、向CTXSYS表空间中的表test中追加记录 
   　fields terminated by X'09' --4、字段终止于X'09'，是一个制表符
(id,username,password,sj)　　   -----定义列对应顺序

   其中append为数据装载方式，还有其他选项： 
   a、insert，为缺省方式，在数据装载开始时要求表为空 
   b、append，在表中追加新记录 
   c、replace，删除旧记录，替换成新装载的记录 
   d、truncate，同上

第三步：在命令提示符下输入命令。
C:/>sqlldr userid=username/password

control=c:/append.ctl        数据库中用名的用户名和密码
或者 C:/>sqlldr userid=system/manager@ serviceName control=input.ctl 

 

----------------------------------------------------

 

一些经常出现的问题：

 

1。关于日期格式的问题：

 

ctl基本写法诸如：

 

load data
infile 'C:/TP_LOANCONTRACTSUM.txt'
insert into table TP_LOANCONTRACTSUM
fields terminated by '|!'
(
column01,
column02,
column03,
column04 "to_date(:column04,'''yyyy-mm-dd hh24:mi:ss''')",
column05 "to_date(:column05,'''yyyy-mm-dd hh24:mi:ss''')",
column06,
column07,
column08,
column09,
column10,
column11,
column12 "to_date(:column12,'''yyyy-mm-dd hh24:mi:ss''')",
column13
)
 

 

2。关于长字符串问题，CTL默认情况下是256位（或者256位左右），所以长字符串时会在log里报错，提示所输入的值超过最大长度，解决办法，在ctl文件里再指定大小，注意个情况，不能写VARCHAR只能写CHAR，否则报错，诸如：

 

load data
infile 'C:/TP_PLEDGECONTRACTINFO.txt'
insert into table TP_PLEDGECONTRACTINFO
fields terminated by '|!'
(
column01,
column02,
column03,
column04,
column05,
column06,
column07 "to_date(:column07,'''yyyy-mm-dd hh24:mi:ss''')",
column08,
column09,
column10 "to_date(:column10,'''yyyy-mm-dd hh24:mi:ss''')",
column11,
column12,
column13,
column14,
column15 "to_date(:column15,'''yyyy-mm-dd hh24:mi:ss''')",
column16,
column17,
column18 "to_date(:column18,'''yyyy-mm-dd hh24:mi:ss''')",
column19,
column20,
column21,
column22,
column23,
column24,
column25 "to_date(:column25,'''yyyy-mm-dd hh24:mi:ss''')",
column26 CHAR(500),
column27,
column28
)

转:http://blog.csdn.net/programpoet/article/details/4516377