Using SQLLDR to import data from a file into a database

1. Create a data file:

For example, to create a zhaozhenlong.txt file in D:\, the file content is:

11,12,13
21,22,23
31,32,33

2. Create the control file:

For example, to create a zhaozhenlong.ctl file in D:\, the file content is:

Load da ta
InFile ' D:\zhaozhenlong.txt '
Append into table Zhaozhenlong
Fields terminated by ', '
(C1,C2,C3)

3. Create a table in the database:

CREATE TABLE Zhaozhenlong (c1 varchar), C2 varchar, C3 varchar2 (20));

4. In ' Start '/' Run ', execute:

Sqlldr userid=cs/[email protected] Control=d:\zhaozhenlong.ctl

5, Query the database:

SELECT * FROM Zhaozhenlong

The result is:

? C1 C2 C3
1 11 12 13
2 21 22 23
3 31 32 33

?

Example 2:

?

? About the use of SQLLDR in Oracle

2005-12-30 21:13:49
Big Small
These days studied the use of Sqlldr, written here today for the needs of the peer to enjoy.
Now there is a file Output.txt content as follows:
?
Ivy,lam,pr02004,2005,09,21,17,32,00,i,d,main DOOR,PR,
Carmen,siu,ac04023,2005,09,21,17,32,01,i,d,main Door,account,
Xing,lee,cm06021,2005,09,21,17,32,02,i,d,main Door,communications,
Barry,yu,mk05006,2005,09,21,17,32,02,i,d,main door,marketing,
Overtime,nil,ov0001,2005,09,21,17,32,07,i,d,main Door,product,
Sammy,mow,sa02322,2005,09,21,17,32,09,i,d,main Door,sales,
Emily,fu,cm06024,2005,09,21,17,32,10,i,d,main Door,communications,
Stella,chow,pt02145,2005,09,21,17,32,11,i,d,main Door,product,
?
is a regular data, the middle is "," separate, and now I want to import this file into Oracle:
?
The first step, of course, is to build a table that has several fields on it with a few rows.
?
Two, write a *.ctl file, the content is as follows
?
Load da ta
InFile ' D:\owen\work\CardAttendence\Completed\windows\Output.txt '
Badfile ' D:\owen\work\CardAttendence\Completed\windows\Output.bad '
Append
into table system.card_time_original
Fields terminated by ","
(lname,fname,emp_id,year,month,day,hour,minute,second,inout,status,doorname,dept)

Three, enter the following command under the Doc window
?
Sqlldr userid=system/[email protected] Control=d:\owen\work\cardattendence\completed\windows\imp Ortone.ctl Log=d:\owen\work\cardattendence\completed\windows\imp Ort.log
?
Note: This must be the case if the current user has all the permissions on this table, and must not use the SYS user, or error
?
If you want to import this data into two tables (the same structure), you can change the control file to the following:
?
Load da ta
InFile ' D:\owen\work\CardAttendence\Completed\windows\Output.txt '
Badfile ' D:\owen\work\CardAttendence\Completed\windows\Output.bad '
Append
into table system.card_time_original
When LName! = "
Fields terminated by ","
(LName POSITION (1), fname,emp_id,year,month,day,hour,minute,second,inout,status,doorname,dept)
into table System.card_time_original_bak
When LName! = "
Fields terminated by ","
(LName POSITION (1), fname,emp_id,year,month,day,hour,minute,second,inout,status,doorname,dept)
?

Faq_sqlldr Usage Summary

In the process of data Warehouse project implementation, ETL is an important link, the implementation of good and bad, related to project success or not.
In the field of data extraction, the corresponding tools are IBM Datastage,informatica PowerCenter, which introduces Oracle's own Sqlloder,
This is the easiest tool to master in the Oracle Data Warehouse implementation process. Before you introduce, consider the following questions:

One, attention sqlldr to solve the problem, that is to realize the problem of ETL process

1. The data type of the imported table is the date type

2. Conversion issues in the process of importing data

3. Filtering problems in the process of importing data

4. Interception issues in the process of importing data

5. Import empty column problems you may encounter when converting

6, the Import data separator problem, such as commas, tabs, white space characters are common

Usage:

Ii. control File: A script file that controls commands, usually ending with a CTL, with the following test.ctl:

?

LOAD DA TA

INFILE ' F:\SQLLDR\da ta.txt '

INFILE ' F:\SQLLDR\da ta2.txt '//can import data from multiple files

INFILE *//import content after the last begindata of this file is the import content

Into TABLE sdata. T_test

?

Four modes of loading, four selected one

APPEND//The data on the original table is added to the back.

INSERT//Load Empty table if the original table has data Sqlloader will stop the default value

REPLACE//original table with data the original data will be deleted all

TRUNCATE//Specifies that the same content as replace will delete the existing data with the TRUNCATE statement

?

Filtering the loaded data

When t_date = ' 2007-07-02 '//greater than and less than other comparisons have not been resolved

?

Specify a separator

Fields TERMINATED by ', '//X ' 09 ' (tab)

--Fields TERMINATED by whitespace//blank segmentation, the actual separation of dangerous

Optionally enclosed by ' "'

TRAILING nullcols//table field does not have a corresponding value when allowed to be empty, this sentence is very useful, the default plus good

?

Table field, you can make a fuss inside

(

t_id FILLER,//FILLER keyword The value of this column will not be loaded

T_date "Case when:t_date was null then to_date (' 2999-12-31 ', ' yyyy-mm-dd ') ELSE to_date (: t_date, ' yyyy-mm-dd ') END",

?//Date type special description, and to specify its format

The more general format is t_date DATE ' yyyy-mm-dd '

T_name POSITION (3:6) "UPPER (: T_name)",//Use the location POSITION to tell the loaded data

"UPPER (: T_name)" is converted to uppercase, note colon

T_sex position (*:8),//Start position of this field at the end of the previous field

Entire_line "UPPER (: t_name| |:t_sex)"//Connect and convert to uppercase

)

?

/**** can continue inserting data into other tables

Into TABLE sdata. T_test

INSERT

When t_date = ' 2007-07-02 '? More than and less than other comparisons have not been resolved

. //begindata // with the front INFILE? * Corresponding, generally do not use it ?

1,ajsdlkfjsdkl,0?

?

Three, the Operation method

Command Window execution:

Sqlldr userid=sdata/[email protected] control=f:\sqlldr\test.ctl?

For convenience, generally write a bat file, enter the above content in the bat file, and finally run the bat file in the Command window, the principle is the same.

In short, this is actually a usage in Oracle.

Three

Handling of irregular data sources

?

--trailing nullcols//table field does not have a corresponding value when allowed to be empty, this sentence is useful

For example:

When exporting to a CSV file from an Excel file, there is no data at the end of the line that will be missing the delimiter, which will be an error in the DS, SQLLD

Using SQLLDR to import data from a file into a database