SQL loader to press text data into the database

usage of SQL Loader

1. log in to the database server and switch to the Oracle user.

2, execution command:sqlldr system/t_dpwds8 control=/opt/huawei/sqlldr/imp.ctl skip=1

Description:sqlldr is followed by the user name and password of the database user, control is followed by the address of the file , and Skip indicates how many rows were skipped before importing. This command can be written in a . bat filein a Windows system and can be written on a Linux system in a . sh file, execute.

3, the preparation of Imp.ctl file, the contents are as follows:

Load data

InFile '/opt/huawei/sqlldr/public_event.txt '

Append into table td_diag.t_diag_pre_check_publice_event

Fields terminated by X "09"

(ID "To_number (20)",

OTL_ID,

OLT_IP,

Event_tyoe,

Eventtime Date ' YYYYMMDD HH24:MI:SS '

)

Description: (1)infile is followed by the address of the file to be imported. If it is a csv file, the delimiter is comma,fieldsterminated by ","; if it is TXT file, according to the actual situation; Excel file, delimiter is a tab, Fields terminated by X' 09 '.

(2) Append means append or replace with replace .

(3) Finally, the field name of the table structure, before the citation import is a string type, if some fields in the datasheet are other types, change the type before importing, such as number, add "To_number (8)" after the field. , change the date to add "to_date (' YYYYMMDD HH24:MI:SS ') after the field

(4). Log and . bad files are automatically generated during the import process , and if the control file does not specify a name, the log file is the same as the control file name . Bad The file is the same as the imported file name, as well as the path. Note that if the user does not have write permissions, the execution will be error, you can create a good log and bad file in advance and add permissions to the file.

(5) free to take the length of a line without a comma delimiter,position

Load Data

InFile *

into Table Dept

Replace

(Deptno position (1:2),

Dname Positon (*:16),// start position of this field at the end of the previous field

Loc position (*:29),

Entier_lin position (1:29)

)

Begin data

10Accounting Virginia,usa

SQL loader to press text data into the database