用Oracle sqlldr匯入文字檔案TXT 總結

最近在處理一批文字資料，規定按照介面說明匯入到ORACLE資料庫中，經過最近的研究，解決了一些問題，覺得我必要總結一下：

 

常規資料：

 

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

Dos 環境下使用SQl*Loader命令 載入

使用其它資料庫的資料轉移工具

Oracle 企業管理器中的資料載入功能

具體的技術實現

一、Dos 環境下載入

1、首先，伺服器端的偵聽服務必須已經開啟。

測試方法：Dos 下輸入

C:/>sqlplus 

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  control=test.ctl(此時控制檔案test.ctl存在C:/路徑下)
在命令控制符下進入Oracle        C:/>sqlplus 

 

 

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
)

 

------------------------------------------轉載於-----&gt>
http://blog.csdn.net/programpoet/article/details/4516377