The use of SQL LOADER TXT file import very fast

Prerequisite, you need to install the Oracle client locally

Control file Cms.ctl

Load data
CHARACTERSET UTF8
InFile ' Oracle.txt '
APPEND into TABLE JR
Fields terminated by ' | '
(
Bukrs,
Lonno,
Waers,
Bldat,
Budat,
SHKZG,
Dlcod,
Hkont,
FPGRP,
Fpnam,
LONPR,
WRBTR,
Ecnma,
Dlnam,
EVTCD,
SEVTC,
SEVTD,
CITYC,
City,
LONTP,
Payer,
Region
)

CD D:/XLS2
Sqlldr Userid=oaadmin/[email protected] Control=cms.ctl
Exit

One: Features of SQL loader
Oracle itself has a number of tools that can be used for data migration, backup, and recovery. But each tool has its own characteristics.
For example, exp and IMP can export and export the data in the database, it is a good tool for database backup and recovery, so it is mainly used in the hot backup and recovery of database. With fast, easy to use, quick advantages, but also have some shortcomings, such as the export between the different versions of the database, the import process, there will always be such or that the problem, this may be the Oracle Company's own product compatibility issues.
The SQL Loader tool does not have this problem, it can import some text format data into the Oracle database, it is a very convenient and common tool for data migration between different databases. The disadvantage is slow, in addition to the BLOB and other types of data is a bit troublesome.

II: Help with SQL Loader

C:\>sqlldr

Sql*loader:release 9.2.0.1.0-production on Saturday October 9 14:48:12 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Usage: Sqlldr keyword=value [, Keyword=value,...]

Valid keywords:

UserID-ORACLE Username/password
Control--Control file name
Log--Log file name
Bad-Bad file name
Data--Data file name
Discard--Discard file name
Discardmax--number of discards to allow (all default)
Skip--Number of logical records to skip (default 0)
Load--Number of logical records to load (all default)
Errors--Number of errors to allow (default 50)
Rows-number of rows in conventional path bind array or between direct p
Ath Data saves
(Default: Normal path 64, all direct paths)
Bindsize--Size of conventional path bind array in bytes (default 256000)
Silent--Suppress messages during run (Header,feedback,errors,discards,part
Itions)
Direct-use direct path (default false)
Parfile--Parameter file:name of file that contains parameter specification
S
Parallel--Do parallel load (default false)
FILE--file to allocate extents from
Skip_unusable_indexes--Disallow/allow unusable indexes or index partitions (default
Acknowledge false)
Skip_index_maintenance--Do not maintain indexes, mark affected indexes as Unus
Able (default false)
ReadSize--Size of Read buffer (default 1048576)
External_table--Use external table for load; not_used, Generate_only, EXECUTE (
Default not_used)
Columnarrayrows--Number of rows for direct path column array (Default 5000)
Streamsize--Size of direct path stream buffer in bytes (default 256000)
Multithreading--use multithreading in direct path
Resumable-Enable or disable resumable for current session (default false)
Resumable_name--text string to help identify resumable statement
Resumable_timeout--wait time (in seconds) for resumable (default 7200)
Date_cache--size (in entries) of date conversion cache (default 1000)

Please note: command line parameters can be specified by location or keyword
。 The former example is ' Sqlload
Scott/tiger foo '; An example of the latter case is ' Sqlldr Control=foo
Userid=scott/tiger '. Position specifies that the parameter must be earlier than
But not later than the parameters specified by the keyword. For example
Allow ' Sqlldr scott/tiger control=foo logfile=log ', but
Do not allow ' Sqlldr scott/tiger control=foo log ', even if
The parameter ' log ' is in the correct position.

C:\>

Three: SQL Loader use example
A) Sqlloader to export Excel data to Oracle
1. Create the required files for the Sql*loader input data, and save them to C: \ and edit with Notepad.
Control file: Input.ctl, the content is as follows:

Load data--1, control file ID
InFile ' Test.txt '--2, the data file to be entered is named Test.txt
Append into table Test--3, append record to form test
Fields terminated by X ' 09 '--4, field terminated by X ' 09 ', is a tab (tab)
(ID,USERNAME,PASSWORD,SJ)-----Define the order of columns

A, insert, default, requires the table to be empty at the beginning of the data load
b, append, append a new record to the table
c, replace, delete the old record, replace the new loaded record
D, Truncate, ibid.

Use the Sql*loader command to enter data in a DOS window

C:\>sqlldr Userid=system/manager Control=input.ctl
The default log file name is: Input.log
Default bad record file is: Input.bad

2. There is another way
You can save the Excel file as a CSV (comma delimited) (*.csv), and the control file will be separated by commas.
LOAD DATA
INFILE ' D:\car.csv '
APPEND into TABLE t_car_temp
Fields TERMINATED by ","
(Phoneno,vip_car)

b) Import data directly into the control file

1, control the contents of the file Test.ctl
--The format for executing this file with SQL Loader is:
--Sqlldr control= is sure to substitute your
--version of SQL LOADER and the filename for this file.
LOAD DATA
INFILE *
Badfile ' C:\Documents and settings\jackey\ desktop \wmcountry. Bad '
Discardfile ' C:\Documents and settings\jackey\ desktop \wmcountry. DSC '
INSERT into TABLE emccountry
Fields terminated by ";" Optionally enclosed by ' "'
(
Countryid Nullif (countryid= "NULL"),
CountryCode,
CountryName,
Continentid Nullif (continentid= "NULL"),
MAPID Nullif (mapid= "NULL"),
Createtime DATE "mm/dd/yyyy HH24:MI:SS" Nullif (createtime= "NULL"),
Lastmodifiedtime DATE "mm/dd/yyyy HH24:MI:SS" Nullif (lastmodifiedtime= "NULL")
)
Begindata
1; " JP ";" Japan "; 1;9;" 09/16/2004 16:31:32 "; Null
2; " CN ";" China "; 1;10;" 09/16/2004 16:31:32 "; Null
3; " In ";" India "; 1;11;" 09/16/2004 16:31:32 "; Null
4; " AU ";" Australia "; 6;12;" 09/16/2004 16:31:32 "; Null
5; " CA ";" Canada "; 4;13;" 09/16/2004 16:31:32 "; Null
6; " US ";" States "; 4;14;" 09/16/2004 16:31:32 "; Null
7; " MX ";" Mexico "; 4;15;" 09/16/2004 16:31:32 "; Null
8; " GB ";" Kingdom "; 3;16;" 09/16/2004 16:31:32 "; Null
9; " DE ";" Germany "; 3;17;" 09/16/2004 16:31:32 "; Null
10; " FR ";" France "; 3;18;" 09/16/2004 16:31:32 "; Null
11; " IT ";" Italy "; 3;19;" 09/16/2004 16:31:32 "; Null
12; " ES ";" Spain "; 3;20;" 09/16/2004 16:31:32 "; Null
13; " FI ";" Finland "; 3;21;" 09/16/2004 16:31:32 "; Null
14; " SE ";" Sweden "; 3;22;" 09/16/2004 16:31:32 "; Null
15; " IE ";" Ireland "; 3;23;" 09/16/2004 16:31:32 "; Null
16; " NL ";" Netherlands "; 3;24;" 09/16/2004 16:31:32 "; Null
17; " DK ";" Denmark "; 3;25;" 09/16/2004 16:31:32 "; Null
18; " BR ";" Brazil "; 5;85;" 09/30/2004 11:25:43 "; Null
19; " KR ";" Korea, Republic of "; 1;88;" 09/30/2004 11:25:43 "; Null
20; " NZ ";" New Zealand "; 6;89;" 09/30/2004 11:25:43 "; Null
21; " Be ";" Belgium "; 3;79;" 09/30/2004 11:25:43 "; Null
22; " At ";" Austria "; 3;78;" 09/30/2004 11:25:43 "; Null
23; " NO ";" Norway "; 3;82;" 09/30/2004 11:25:43 "; Null
24; " LU ";" Luxembourg "; 3;81;" 09/30/2004 11:25:43 "; Null
25; " PT ";" Portugal "; 3;83;" 09/30/2004 11:25:43 "; Null
26; " GR ";" Greece "; 3;80;" 09/30/2004 11:25:43 "; Null
27; " IL ";" Israel "; 1;86;" 09/30/2004 11:25:43 "; Null
28; " CH ";" Switzerland "; 3;84;" 09/30/2004 11:25:43 "; Null
29; " A1 ";" Anonymous Proxy "; 0;0;" 09/30/2004 11:25:43 "; Null
30; " A2 ";" Satellite Provider "; 0;0;" 09/30/2004 11:25:43 "; Null
31; " AD ";" Andorra "; 3;0;" 09/30/2004 11:25:43 "; Null
32; " AE ";" Arab Emirates "; 1;0;" 09/30/2004 11:25:43 "; Null
33; " AF ";" Afghanistan "; 1;0;" 09/30/2004 11:25:43 "; Null
34; " AG ";" Antigua and Barbuda "; 7;0;" 09/30/2004 11:25:43 "; Null
35; " AI ";" Anguilla "; 7;0;" 09/30/2004 11:25:43 "; Null
36; " AL ";" Albania "; 3;0;" 09/30/2004 11:25:43 "; Null
37; " AM ";" Armenia "; 3;0;" 09/30/2004 11:25:43 "; Null
38; " an ";" Netherlands Antilles "; 3;0;" 09/30/2004 11:25:43 "; Null
39; " AO ";" Angola "; 2;0;" 09/30/2004 11:25:43 "; Null
40; " AP ";" Asia/pacific region "; 2;0;" 09/30/2004 11:25:43 "; Null
41; " AQ ";" Antarctica "; 8;0;" 09/30/2004 11:25:43 "; Null
42; " AR ";" Argentina "; 5;0;" 09/30/2004 11:25:43 "; Null
43; " as ";" American Samoa "; 6;0;" 09/30/2004 11:25:43 "; Null
44; " AW ";" Aruba "; 5;0;" 09/30/2004 11:25:43 "; Null
45; " AZ ";" Azerbaijan "; 1;0;" 09/30/2004 11:25:43 "; Null
46; " BA ";" Bosnia and Herzegovina "; 3;0;" 09/30/2004 11:25:43 "; Null
47; " BB ";" Barbados "; 5;0;" 09/30/2004 11:25:43 "; Null
48; " BD ";" Bangladesh "; 1;0;" 09/30/2004 11:25:43 "; Null
49; " BF ";" Burkina Faso "; 2;0;" 09/30/2004 11:25:43 "; Null
50; " BG ";" Bulgaria "; 3;0;" 09/30/2004 11:25:43 "; Null
51; " BH ";" Bahrain "; 1;0;" 09/30/2004 11:25:43 "; Null
52; " BI ";" Burundi "; 2;0;" 09/30/2004 11:25:43 "; Null
53; " BJ ";" Benin "; 2;0;" 09/30/2004 11:25:43 "; Null
54; " BM ";" Bermuda "; 4;0;" 09/30/2004 11:25:43 "; Null
55; " BN ";" Brunei Darussalam "; 1;0;" 09/30/2004 11:25:43 "; Null
56; " BO ";" Bolivia "; 5;0;" 09/30/2004 11:25:43 "; Null
57; " BS ";" Bahamas "; 7;0;" 09/30/2004 11:25:43 "; Null
58; " BT ";" Bhutan "; 1;0;" 09/30/2004 11:25:43 "; Null
59; " BV ";" Bouvet Island "; 5;0;" 09/30/2004 11:25:43 "; Null
60; " BW ";" Botswana "; 2;0;" 09/30/2004 11:25:43 "; Null
61; " By ";" Belarus "; 3;0;" 09/30/2004 11:25:43 "; Null
2. Execute the Import command
C:\>sqlldr Userid=system/manager Control=test.ctl

The use of SQL LOADER TXT file import very fast