Shulou(Shulou.com)06/01 Report--

Recently, I am dealing with a batch of text data, which is required to be imported into the ORACLE database in accordance with the interface instructions. After recent research, I have solved some problems. I feel that I need to sum up:

General information:

Use SQl*Loader command to load in Dos environment

Use data transfer tools from other databases

Data loading function in Oracle Enterprise Manager

Specific technical implementation

1. Loading in Dos environment

1. First of all, the listening service on the server side must be enabled.

Test method: input under Dos

CRV / > sqlplus username/password@serviceName

2. Then use the sqlldr command of Oracle to import the data

Prophase condition

1) the Oracle database must have established the structure of the data table to be imported.

2) A data source file the tab-delimited text file model.txt in the following example is exported from the Excel table

3) manually edit a control file of XXX.CTL

4) the command line loads data

The following is an example:

By default, the following files are placed under Corex /. If not, you need to specify the full path.

1. Command control file input.ctl content

Command

Description

Load data

1. Control file identification

Infile 'model.txt'

2. The name of the data file to be imported is test.txt

Append into table system. Tower location property sheet

3. Append records to the table test

Fields terminated by Xbox 09'

4. Specify the delimiter. The field terminates at Xsepar.It is a tab (TAB).

(number, name, size)

5. Define the order in the corresponding table of columns

Keywords that specify how data is inserted in the control file

Insert, which is the default, requires the table to be empty at the beginning of data loading

Append, append new records to the table

Replace, delete the old record and replace it with the newly loaded record

Truncate, ditto

Use SQl*Loader command to import data under Dos window

CRV / > sqlldr userid=system/manager@ serviceName control=input.ctl

The default log file name is: input.log

The default bad record file is: input.bad

Second, use other database transfer tools

Here is an example of the SQL Server Import and Export Wizard

1. Set the data source server in the data import and export wizard, and select the data source in the instance and select SQL Server

2. Then specify the Oracle data source to import

3. Attribute information of Oracle needs to be configured.

It should be noted that the user information logged in to the database is the scheme name after the data is imported, that is, the table in the imported SQL Server is marked as username. Exe in Oracle. Table name

Following the prompts, you can completely import the data tables and views in SQl Server, or you can use the query statement to return the columns or rows you want to select.

III. Data loading function in Oracle Enterprise Manager

Log in to the console interface of Oracle, and you can use data loading tools for individual data tables

Control files need to be specified in the middle, which is consistent with Dos loading and will not be repeated.

Control file format used when importing text data into Oracle

No matter which way you use in the previous article, you need to have a control file. Here is the basic format for writing the control file (ctl file):

Command

Description

Load data

1. Control file identification

Infile 'testl.txt'

2. The name of the data file to be imported is test.txt. At this time, the data file to be imported is in the same path as the control file. If it is not in the same path, you need to write the full path name.

Append into table table name (can be a full name or a synonym)

3. Append records to the table test

Fields terminated by Xbox 09'

4. Specify the delimiter. The field terminates at Xsepar09' (very important), which is a tab (TAB). Replace Xsepar09' with','if separated by a comma.

(number, name, size)

5. Define the order in the corresponding table of columns

Keywords that specify how data is inserted in the control file

Insert, which is the default, requires the table to be empty at the beginning of data loading

Append, append new records to the table

Replace, delete the old record and replace it with the newly loaded record

Truncate, ditto

Example of a control file:

Load data

Infile 'test.txt'

Append into table test.test

Fields terminated by Xbox 09'

(test,test1,test2)

Sample control file for data import with time type:

Load data

Infile'hjouxt 'txt'

Append into TABLE TB_FACT_PHS_TICKET_DAY

Fields terminated by Xbox 09'

(Time_Id

, Region_Id

, Cust_Type_Id

, Prod_Type_Id

, Acct_Item_Type_Id

, Acct_Item_Type_Cd

, Exchange97_Cd

, Latn_Cd

, Call_Duration

, Access_In_Duration

, Income

, In_Date Date "YYYY-MM-DD"

)

File import command test.ctl / > sqlldr userid=test/test@test control=test.ctl (in this case, the control file CRAV / path exists)

Enter Oracle / > sqlplus username/password@serviceName under the command controller

Oracle Import txt data File on Wednesday, July 30, 2008 17:21 the way to import a data file in txt file format into oracle is to use the sqlloader tool.

Step 1: put the data file in text format on disk C. For example, test.txt

Step 2: create the control file append.ctl. (you can take your name and put it on disk C)

The append.ctl is as follows:

Load data-1. Control file identification

Infile 'test.txt'-2. The name of the data file to be imported is test.txt

Append into table CTXSYS.test-- 3. Append records to table test in CTXSYS tablespace

The field ends at fields terminated by 09'- 4, which is a tab.

(id,username,password,sj)-defines the corresponding order of columns

Append is the data loading method, and there are other options:

A, insert, which is the default mode, requires the table to be empty at the beginning of data loading

B, append, append new records to the table

C, replace, delete the old record and replace it with the newly loaded record

D, truncate, ditto

Step 3: enter the command at the command prompt.

CRV / > sqlldr userid=username/password

Username and password of the name in the control=c:/append.ctl database

Or CRV / > sqlldr userid=system/manager@ serviceName control=input.ctl

Some common problems:

one. Questions about date format:

Ctl is written in basic ways such as:

Load data

Infile'Cvug market TPcharts LOANCONTRACTSUM.txt'

Insert into table TP_LOANCONTRACTSUM

Fields terminated by'|!'

(

Column01

Column02

Column03

Column04 "to_date (: column04,'''yyyy-mm-dd hh34:mi:ss''')"

Column05 "to_date (: column05,'''yyyy-mm-dd hh34:mi:ss''')"

Column06

Column07

Column08

Column09

Column10

Column11

Column12 "to_date (: column12,'''yyyy-mm-dd hh34:mi:ss''')"

Column13

)

two. With regard to the problem of long strings, CTL defaults to 256bits (or about 256bits), so an error will be reported in log when a long string is long, indicating that the value entered exceeds the maximum length. The solution is to specify the size in the ctl file again. Note that VARCHAR can only write CHAR, otherwise an error will be reported, such as:

Load data

Infile'Cvuddy TPPM PLEDGecNTRACTINFO.txt'

Insert into table TP_PLEDGECONTRACTINFO

Fields terminated by'|!'

(

Column01

Column02

Column03

Column04

Column05

Column06

Column07 "to_date (: column07,'''yyyy-mm-dd hh34:mi:ss''')"

Column08

Column09

Column10 "to_date (: column10,'''yyyy-mm-dd hh34:mi:ss''')"

Column11

Column12

Column13

Column14

Column15 "to_date (: column15,'''yyyy-mm-dd hh34:mi:ss''')"

Column16

Column17

Column18 "to_date (: column18,'''yyyy-mm-dd hh34:mi:ss''')"

Column19

Column20

Column21

Column22

Column23

Column24

Column25 "to_date (: column25,'''yyyy-mm-dd hh34:mi:ss''')"

Column26 CHAR (500)

Column27

Column28

)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.