SQLLDR Load Data Implementation code _MSSQL

Here is the simplest test with Excel data.
1 Save Excel data as T.txt file, note file suffix named. 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 Control file T.ctl 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"
)
where the X ' 09 ' is a tab, the TAB key, trailing nullcols indicates that the table's fields are allowed to be empty if they do not have corresponding values.
4)C:\Documents and Settings\cjh>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, 2002, Oracle Corporation. All rights reserved.
Reach submission point, logical record Count 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 '//files to be imported
INFILE ' tt.date '//import of multiple files
INFILE *//the content to be imported is in the control file the following begindata is the imported content
into table table_name//Specify loaded Tables
Badfile ' c:\bad.txt '//designated Bad file address
Here are 4 ways of loading tables
APPEND//original table with data added to the back
INSERT//Load Empty table if the original table has data Sqlloader will stop the default value
REPLACE//original table has data the original data will be deleted
The TRUNCATE//specified content and replace will delete the existing data with the TRUNCATE statement
The specified terminated can be at the beginning of the table and also in the Inner field section of the table
FIELDS terminated by ', ' optionally enclosed by ' '
Load this data: 10,LG, "" "LG" "", "LG,LG"
Results in the table: LG "LG" LG,LG
Terminated by X ' 09 '//in hexadecimal format ' 09 '
Terminated by Writespace//loading this data: LG LG
Trailing Nullcols ************* table fields do not have corresponding values to allow null
The following is a table field
(
Col_1, col_2, Col_filler filler//filler keyword The value of this column will not be loaded
such as: Lg,lg,not results LG LG
)
When it is not stated that fields terminated by ', '
// (
Col_1 [Interger external] terminated by ', ',
col_2 [Date "dd-mon-yyy"] terminated by ', ',
Col_3 [char] terminated by ', ' optionally enclosed by ' LG '
// )
When no statement fields terminated by ', ' use location to tell field to load data
// (
Col_1 position (1:2),
Col_2 position (3:10),
Col_3 position (*:16),//The starting position of this field is at the end of the previous field
Col_4 position (1:16),
Col_5 position (3:10) char (8)//type of the specified field
// )
Begindata//corresponds to the starting INFILE * The content to be imported is in the control file
10,sql,what
20,lg,show
=====================================================================================
Note that the value after the Begindata cannot be preceded by a space
1 * * * ordinary load
LOAD DATA
INFILE *
Into TABLE DEPT
REPLACE
FIELDS terminated by ', ' optionally enclosed by ' '
(DEPTNO,
Dname,
LOC
)
Begindata
10,sales, "" "" USA "" "
20,accounting, "Virginia,usa."
30,consulting,virginia
40,finance,virginia
, "Finance", "", Virginia//LOC column will be empty
, "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
Ten Sales Virginia
3 * * * Specifies not to load that column
LOAD DATA
INFILE *
Into TABLE DEPT
REPLACE
FIELDS terminated by ', ' optionally enclosed by ' '
(DEPTNO,
Filler_1 filler,//below "something not to be Loaded" will not be loaded
Dname,
LOC
)
Begindata
20,something is loaded,accounting, "Virginia,usa"
4 the position of * * * * *
LOAD DATA
INFILE *
Into TABLE DEPT
REPLACE
(DEPTNO position (1:2),
Dname position (*:16),//The starting position of this field is at the end of the previous field
LOC position (*:29),
Entire_line position (1:29)
)
Begindata
10Accounting Virginia,usa
5 * * * Use the function date of an expression trailing nullcols use
LOAD DATA
INFILE *
Into TABLE DEPT
REPLACE
FIELDS terminated by ', '
Trailing Nullcols//In fact, the following entire_line in the begindata behind the data is not directly corresponding
If the first row of the value of the column is changed to 10,sales,virginia,1/5/2000, you don't have to trailing nullcols.
(DEPTNO,
Dname "Upper (:d name)",//Use function
LOC "Upper (: LOC)",
last_updated Date ' dd/mm/yyyy ', an expression of the date and ' dd-mon-yyyy '
Entire_line ":d eptno| |:d name| |: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 * * * Using custom functions//Resolved time issues
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 ');
L_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 are NOT null;
End Loop;
if (L_return is null)
Then
L_return: =
New_time (to_date (' 01011970 ', ' ddmmyyyy ') + 1/24/60/60 *
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 (:d name)",
LOC "Upper (: LOC)",
Last_updated "My_to_date (: last_updated)"//Using Custom functions
)
Begindata
10,sales,virginia,01-april-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 * * * Combine multiple lines of records into one row
LOAD DATA
INFILE *
Concatenate 3//Through the keyword concatenate the records of several lines as one line record
Into TABLE DEPT
Replace
FIELDS terminated by ', '
(DEPTNO,
Dname "Upper (:d name)",
LOC "Upper (: LOC)",
last_updated Date ' dd/mm/yyyy '
)
Begindata
10,sales,//In fact, these 3 lines as a line 10,sales,virginia,1/5/2000
Virginia,
1/5/2000
This column uses Continueif list= "," or you can
Tell Sqlldr to find commas at the end of each line. Append the next line to the previous line
LOAD DATA
INFILE *
Continueif this (1:1) = '-'/Find out if there is a connection character at the beginning of each line-there is a line to connect the next line
such as -10,sales,virginia,
1/5/2000 is a line of 10,sales,virginia,1/5/2000
Where 1:1 indicates that the first line starts and ends at the first line and Continueif next but Continueif list is ideal
Into TABLE DEPT
Replace
FIELDS terminated by ', '
(DEPTNO,
Dname "Upper (:d name)",
LOC "Upper (: LOC)",
last_updated Date ' dd/mm/yyyy '
)
Begindata//But it doesn't seem to work like the right side.
-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 to load the line number of each line
Load data
InFile *
into table T
Replace
(seqno recnum//Load the line number of each row
Text Position (1:1024))
Begindata
FSDFASJ//Automatically assigns a row number to the Seqno field that loads table T this row is 1
FASDJFASDFL//This row is 2 ...
9 * * Load the data with line feed
Note: UNIX and Windows differ \\n &/N

< 1 > Use a character that is not a line feed
LOAD DATA
INFILE *
Into TABLE DEPT
REPLACE
FIELDS terminated by ', '
Trailing Nullcols
(DEPTNO,
Dname "Upper (:d name)",
LOC "Upper (: LOC)",
Last_updated "My_to_date (: last_updated)",
COMMENTS "replace (: COMMENTS, ' \ n ', CHR)"//replace use Help to convert line breaks
)
Begindata
10,sales,virginia,01-april-2001,this is the Sales\noffice in Virginia
20,accounting,virginia,13/04/2001,this is the Accounting\noffice in Virginia
30,consulting,virginia,14/04/2001 12:02:02,this is the Consulting\noffice in Virginia
40,finance,virginia,987268297,this is the Finance\noffice in Virginia

< 2 > Use the Fix property
LOAD DATA
INFILE demo17.dat "Fix 101"
Into TABLE DEPT
REPLACE
FIELDS terminated by ', '
Trailing Nullcols
(DEPTNO,
Dname "Upper (:d name)",
LOC "Upper (: LOC)",
Last_updated "My_to_date (: last_updated)",
COMMENTS
)
Demo17.dat
10,sales,virginia,01-april-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 way the load will load the newline character into the database, but the format of the data is not the same.
LOAD DATA
INFILE demo18.dat "Fix 101"
Into TABLE DEPT
REPLACE
FIELDS terminated by ', ' optionally enclosed by ' '
Trailing Nullcols
(DEPTNO,
Dname "Upper (:d name)",
LOC "Upper (: LOC)",
Last_updated "My_to_date (: last_updated)",
COMMENTS
)
Demo18.dat
10,SALES,VIRGINIA,01-APRIL-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 > using the var attribute
LOAD DATA
INFILE Demo19.dat "VAR 3"
3 tells the first 3 bytes of each record that the length of the record, as 071 of the first record, indicates that the record has 71 bytes
Into TABLE DEPT
REPLACE
FIELDS terminated by ', '
Trailing Nullcols
(DEPTNO,
Dname "Upper (:d name)",
LOC "Upper (: LOC)",
Last_updated "My_to_date (: last_updated)",
COMMENTS
)
Demo19.dat
07110,sales,virginia,01-april-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 > Using the str attribute
The most flexible one can define a new line end character win carriage return line: Chr (13) | | Chr (10)
The records in this column end with a|\r\n
Select Utl_raw.cast_to_raw (' | ' | | Chr (13) | | Chr (a)) from dual;
Results 7c0d0a
LOAD DATA
INFILE demo20.dat "str X ' 7c0d0a '"
Into TABLE DEPT
REPLACE
FIELDS terminated by ', '
Trailing Nullcols
(DEPTNO,
Dname "Upper (:d name)",
LOC "Upper (: LOC)",
Last_updated "My_to_date (: last_updated)",
COMMENTS
)
Demo20.dat
10,sales,virginia,01-april-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|
==============================================================================
Data like this with the NULLIF clause
10-jan-200002350flipper seemed unusually hungry today.
10510-jan-200009945spread over three meals.
ID position (1:3) Nullif//This can be blanks or some other expression.
Here's another one. 1 of the first row will be null in the database
LOAD DATA
INFILE *
Into TABLE T
REPLACE
(n Position (1:2) integer external nullif n= ' 1 ',
V Position (3:8)
)
Begindata
1 10
20lg