Oracle SQL load data import

Import script:

Load data
Infile 'e: \ data.csv'
Into Table Fund
Replace
Fields terminated by ', 'optionally enclosed '"'
(ID recnum, -- auto-incrementing Sequence
CompanyName,
Personname,
Identitynum,
Fundnum,
Sendnum,
Customservicename,
Rate,
Createdate date 'yyyy/MM/dd', -- Date Format
Basenum
)

Run the following command in cmd:

D: \ oracleapp \ product \ 11.2.0 \ dbhome_1 \ bin> sqlldr userid = username/password control = E
: \ Data. CTL log = E: \ log.txt

======================================= A piece of SQL LoadArticle======================================

A small Summary of SQL Load
The following is my summary of some of the shortcomings of SQL load.

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 ', 'optionally enclosed by '"'
(deptno,
dname,
loc
)
begindata
10, sales, "USA"
20, accounting, "Virginia, USA"
30, consulting, Virginia
40, finance, Virginia
50, "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

5 ***** 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 value of
// if the first row is changed to 10, sales, Virginia, 1/5/2000, you don't need to trailing nullcols
(deptno,
dname "upper (: dname )", // use the function
Loc "upper (: Loc)",
last_updated date 'dd/MM/yyyy ', // A expression of 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 by ','
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-Hangzhou L-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

7 **** Merge multiple rows of records into one row
load data
infile *
concatenate 3 // use the keyword concatenate record as a row of record
into Table dept
replace
fields terminated ', '
(deptno,
dname "upper (: dname)",
Loc "upper (: Loc )",
last_updated date 'dd/MM/yyyy'
)
begindata
10, sales, // In fact, these three rows are regarded as a row of 10, sales, virginia, 1/5/2000
Virginia,
1/5/2000
// use continueif list = ", "You can also
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 (1:1) = '-' // check whether a connection character exists 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
// among them, indicates that the continueif next but 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 row

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-116l-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

// This method will load the linefeed into the database, but the data format is not required.

load data
infile demo18.dat "Fix 101"
into Table dept
replace
fields terminated ', 'optionally enclosed by '"'
trailing nullcols
(deptno,
dname" upper (: dname) ",
Loc" upper (: Loc) ",
last_updated" my_to_date (: last_updated) ",
comments
)
demo18.dat
10, sales, Virginia, 01-10000l-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
)
demo1_dat
10, sales, Virginia, 01-Hangzhou L-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 id = balanks // here it can be either blks 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
------------------------------------------------------------

If the log format is English, you may need to modify the environment variable nls_lang or nls_date_format.