Here we use excel Data for the simplest test.
1) Save the excel data as a t.txt file. Note that the file suffix is. txt.
1 jhchen 11/07/2005 20:04:00 2005-11-7 20:04
2 jhchen 11/07/2005 20:04:00 2005-11-7 20:04
3 jhchen 11/07/2005 20:04:00 2005-11-7 20:04
2)SQL> create table t_load (
Id number,
Name varchar2 (10 ),
Dat1 date,
Dat2 date,
Dat3 date
);
Table created.
3) The control file t. ctl is as follows:
Load data
Infile 't.txt'
Badfile 't. bad'
Append into table t_load
Fields terminated by X '09'
Trailing nullcols
(
Id,
Name,
Dat1 date "mm/dd/yyyy hh24: mi: ss ",
Dat2 date "yyyy-mm-dd hh24: mi: ss"
)
Here, '09' is a TAB, TAB key, and trailing nullcols indicates that if the table field does not have a corresponding value, it can be blank.
4)C: \ Documents ents and Settings \ c133> sqlldr userid = jhchen/oracle control = t. ctl
SQL * Loader: Release 9.2.0.6.0-Production on Monday November 7 20:20:00 2005
Copyright (c) 1982,200 2, Oracle Corporation. All rights reserved.
Reaching the submission point, the logical record count is 3
5)SQL> select * from t_load;
Id name DAT1 DAT2 DAT3
-----------------------------------------------
1 jhchen 07-NOV-05 07-NOV-05
2 jhchen 07-NOV-05 07-NOV-05
3 jhchen 07-NOV-05 07-NOV-05
Sqlldr userid = lgone/tiger control = a. ctl
LOAD DATA
INFILE't. dat // file to be imported
// INFILE 'tt. date' // import multiple files
// INFILE * // the content to be imported is after BEGINDATA in the control file.
Into table table_name // specify the mounted TABLE
BADFILE 'C: \ bad.txt '// specify the bad file address
* ************ The following are four ways to load tables.
APPEND // Add the data in the original table to the end
// INSERT // load an empty table. If the original table has data, sqlloader stops the default value.
// REPLACE // all data in the original table will be deleted
// TRUNCATE // if the specified content is the same as that of replace, the truncate statement is used to delete the existing data.
* ************ The specified TERMINATED can be either at the beginning of the table or in the internal fields of the table.
Fields terminated by ', 'optionally enclosed '"'
// Load this data: 10, lg, "" lg "," lg, lg"
// Result in the Table: 10 lg "lg" lg, lg
// Terminated by x '09' // in hexadecimal format '09'
// Terminated by writespace // load this data: 10 lg
Trailing nullcols ************** when the table field does not have a corresponding value, it can be blank
* ************ The following table fields
(
Col_1, col_2, col_filler FILLER // The value of the keyword column is not loaded
// Lg, lg, and not result lg
)
// When fields terminated by ',' is not declared
//(
// Col_1 [interger external] terminated ',',
// Col_2 [date "dd-mon-yyy"] terminated ',',
// Col_3 [char] terminated by ', 'optionally enclosed by 'lg'
//)
// When fields terminated by 'is not declared,' the location is used to tell the field to load data.
//(
// Col_1 position ),
// Col_2 position (3: 10 ),
// Col_3 position (*: 16), // the start position of this field.
// Col_4 position (1:16 ),
// Col_5 position () char (8) // specify the field type
//)
BEGINDATA // corresponding to the starting INFILE * the content to be imported is in the control file
10, SQL, what
20, lg, show
========================================================== ========================================================== =====
////////// Note that there must be no space before the value after inindata
1 * normal loading
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
Fields terminated by ', 'optionally enclosed '"'
(DEPTNO,
DNAME,
LOC
)
BEGINDATA
10, Sales, "" USA """
20, Accounting, "Virginia, USA"
30, Consulting, Virginia
40, Finance, Virginia
50, the "Finance", "", Virginia // loc column will be empty
60, "Finance", Virginia // loc column will be empty
2 ***** fields terminated by whitespace and fields terminated by x '09'
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY WHITESPACE
-- Fields terminated by x '09'
(DEPTNO,
DNAME,
LOC
)
BEGINDATA
10 Sales Virginia
3 ***** specify not to load that column
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
Fields terminated by ', 'optionally enclosed '"'
(DEPTNO,
FILLER_1 FILLER, // The "Something Not To Be Loaded" below will Not Be Loaded
DNAME,
LOC
)
BEGINDATA
20, Something Not To Be Loaded, Accounting, "Virginia, USA"
4 * position Column
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
(DEPTNO position ),
DNAME position (*: 16), // the start position of this field.
LOC position (*: 29 ),
ENTIRE_LINE position (1:29)
)
BEGINDATA
10 Accounting Virginia, USA
* Use a function date to express the use of trailing nullcols.
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
Fields terminated ','
Trailing nullcols // In fact, the following ENTIRE_LINE does not directly correspond to the data after BEGINDATA
// If the value of the column is changed to 10, Sales, Virginia, 1/5/2000, trailing nullcols is not required.
(DEPTNO,
DNAME "upper (: dname)", // use the Function
LOC "upper (: loc )",
LAST_UPDATED date 'dd/mm/yyyy', // A expression of the date, such as 'dd-mon-yyyy '.
ENTIRE_LINE ": deptno |: dname |: loc |: last_updated"
)
BEGINDATA
10, Sales, Virginia, 1/5/2000
20, Accounting, Virginia, 21/6/1999
30, Consulting, Virginia, 5/1/2000
40, Finance, Virginia, 15/3/2001
6 ***** use a custom function // to solve the time problem
Create or replace
Function my_to_date (p_string in varchar2) return date
As
Type fmtArray is table of varchar2 (25 );
L_fmts fmtArray: = fmtArray ('dd-mon-yyyy ', 'dd-month-yyyy ',
'Dd/mm/yyyy ',
'Dd/mm/yyyy hh24: mi: ss ');
Rochelle return date;
Begin
For I in 1 .. l_fmts.count
Loop
Begin
L_return: = to_date (p_string, l_fmts (I ));
Exception
When others then null;
End;
EXIT when l_return is not null;
End loop;
If (l_return is null)
Then
Rochelle return: =
New_time (to_date ('20140901', 'ddmmyyyy') + 01011970/1/24 *
P_string, 'gmt', 'est ');
End if;
Return l_return;
End;
/
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
Fields terminated ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper (: dname )",
LOC "upper (: loc )",
LAST_UPDATED "my_to_date (: last_updated)" // use a custom function
)
BEGINDATA
10, Sales, Virginia, 01-10000l-2001
20, Accounting, Virginia, 13/04/2001
30, Consulting, Virginia, 14/04/2001 12:02:02
40, Finance, Virginia, 987268297
50, Finance, Virginia, 02-apr-2001
60, Finance, Virginia, Not a date
* Merge multiple rows into one row
LOAD DATA
INFILE *
Concatenate 3 // use the keyword concatenate to treat several rows of records as one row of records
INTO TABLE DEPT
Replace
Fields terminated ','
(DEPTNO,
DNAME "upper (: dname )",
LOC "upper (: loc )",
LAST_UPDATED date 'dd/mm/yyyy'
)
BEGINDATA
10, Sales, // In fact, the three rows are regarded as a row of 10, Sales, Virginia, 1/5/2000
Virginia,
1/5/2000
// This column uses continueif list = "," or
Tell sqlldr to find a comma at the end of each line and then append the next line to the previous line.
LOAD DATA
INFILE *
Continueif this () = '-' // find whether there are connection characters at the beginning of each line. If yes, connect the next line to a line.
// For example,-10, Sales, Virginia,
// 1/5/2000 is a row of 10, Sales, Virginia, 1/5/2000
// Indicates that there is still continueif next but the continueif list is the most ideal starting from the first line and ending in the first line.
INTO TABLE DEPT
Replace
Fields terminated ','
(DEPTNO,
DNAME "upper (: dname )",
LOC "upper (: loc )",
LAST_UPDATED date 'dd/mm/yyyy'
)
BEGINDATA // but it seems that it cannot be used as in the right
-10, Sales, Virginia,-10, Sales, Virginia,
1/5/2000 1/5/2000
-40, 40, Finance, Virginia, 13/04/2001
Finance, Virginia, 13/04/2001
8 * load the row number of each line
Load data
Infile *
Into table t
Replace
(Seqno RECNUM // load the row number of each row
Text Position (1:1024 ))
BEGINDATA
Fsdfasj // automatically assigns a line number to the seqno field of the loaded table t. This behavior 1
Fasdjfasdfl // This behavior 2...
9 ***** load data with line breaks
Note: unix and windows are different \ n &/n
<1> use a non-linefeed character
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
Fields terminated ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper (: dname )",
LOC "upper (: loc )",
LAST_UPDATED "my_to_date (: last_updated )",
COMMENTS "replace (: comments, '\ n', chr (10)" // use replace to help convert line breaks
)
BEGINDATA
10, Sales, Virginia, 01-10000l-2001, This is the Sales \ nOffice in Virginia
20, Accounting, Virginia, 13/04/2001, This is the Accounting \ nOffice in Virginia
30, Consulting, Virginia, 12:02:02, 14/04/2001, This is the Consulting \ nOffice in Virginia
40, Finance, Virginia, 987268297, This is the Finance \ nOffice in Virginia
<2> use the fix attribute
LOAD DATA
INFILE demo17.dat "fix 101"
INTO TABLE DEPT
REPLACE
Fields terminated ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper (: dname )",
LOC "upper (: loc )",
LAST_UPDATED "my_to_date (: last_updated )",
COMMENTS
)
Demo17.dat
10, Sales, Virginia, 01-0000l-2001, This is the Sales
Office in Virginia
20, Accounting, Virginia, 13/04/2001, This is the Accounting
Office in Virginia
30, Consulting, Virginia, 14/04/2001 12:02:02, This is the Consulting
Office in Virginia
40, Finance, Virginia, 987268297, This is the Finance
Office in Virginia
// In this case, the method that loads the line break into the database will not, but the data format is required to be different.
LOAD DATA
INFILE demo18.dat "fix 101"
INTO TABLE DEPT
REPLACE
Fields terminated by ', 'optionally enclosed '"'
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper (: dname )",
LOC "upper (: loc )",
LAST_UPDATED "my_to_date (: last_updated )",
COMMENTS
)
Demo18.dat
10, Sales, Virginia, 01-0000l-2001, "This is the Sales
Office in Virginia"
20, Accounting, Virginia, 13/04/2001, "This is the Accounting
Office in Virginia"
30, Consulting, Virginia, 14/04/2001 12:02:02, "This is the Consulting
Office in Virginia"
40, Finance, Virginia, 987268297, "This is the Finance
Office in Virginia"
<3> use the var attribute
LOAD DATA
INFILE demo19.dat "var 3"
// 3 tell the first three bytes of each record to indicate the record length. For example, 071 of the first record indicates that the record has 71 bytes.
INTO TABLE DEPT
REPLACE
Fields terminated ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper (: dname )",
LOC "upper (: loc )",
LAST_UPDATED "my_to_date (: last_updated )",
COMMENTS
)
Demo19.dat
07110, Sales, Virginia, 01-Hangzhou L-2001, This is the Sales
Office in Virginia
07820, Accounting, Virginia, 13/04/2001, This is the Accounting
Office in Virginia
08730, Consulting, Virginia, 14/04/2001 12:02:02, This is the Consulting
Office in Virginia
07140, Finance, Virginia, 987268297, This is the Finance
Office in Virginia
<4> use the str attribute
// The most flexible one can define a new line terminator win carriage return line break: chr (13) | chr (10)
Records in this column end with a | \ r \ n
Select utl_raw.cast_to_raw ('| chr (13) | chr (10) from dual;
Result 7C0D0A
LOAD DATA
INFILE demow.dat "str X '7c0d0a '"
INTO TABLE DEPT
REPLACE
Fields terminated ','
TRAILING NULLCOLS
(DEPTNO,
DNAME "upper (: dname )",
LOC "upper (: loc )",
LAST_UPDATED "my_to_date (: last_updated )",
COMMENTS
)
Demow.dat
10, Sales, Virginia, 01-0000l-2001, This is the Sales
Office in Virginia |
20, Accounting, Virginia, 13/04/2001, This is the Accounting
Office in Virginia |
30, Consulting, Virginia, 14/04/2001 12:02:02, This is the Consulting
Office in Virginia |
40, Finance, Virginia, 987268297, This is the Finance
Office in Virginia |
========================================================== ==============================================
Use the nullif clause for such data
10-jan-200002350Flipper seemed unusually hungry today.
10510-jan-200009945Spread over three meals.
Id position () nullif // here it can be blanks or another expression
// The following is the first row of another column. The first row of the column will become null in the database.
LOAD DATA
INFILE *
INTO TABLE T
REPLACE
(N position () integer external nullif n = '1 ',
V position (3: 8)
)
BEGINDATA
1 10
20lg
