Use SQL * Loader to import CSV file data to the Oracle data table

Use SQL * Loader to import CSV file data to the Oracle data table

Use SQL * Loader to import CSV file data to the Oracle data table

Use SQL * Loader to import CSV file data to the Oracle data table

1. Environment Conditions and operation objectives

(1) An Employee table exists in Oracle, which contains four fields:
Emp_id number
Emp_name varchar2
Emp_date date
Dpt_id number

(2) A csvfile, emp.csv, contains the relevant data in the preceding table. The field order is consistent with the preceding
The table fields are the same. The content is as follows:

1001, "James", "2011/04/08", 100
1001, "Tom", "2010/10/15", 100
1001, "Larry", "2009/01/02", 101

(3) The task target uses SQL * loader to import data from emp.csv to the table "employee ".

2. SQL * Loader command description

SQL * Loader is a command line tool released and installed along with Oracle. The command name is sqlldr.
This tool is used to import external file data to ORACLE.

When running SQL * Loader, you must specify a control file and a data file,
In addition to outputting data to Oracle, other files can be output to indicate the running status,
Including:
Log File: a Log File used to output message logs during running. When the operation fails,
You can determine the cause of failure accordingly.
Bad Files: incorrect data row. When a row of data in the data file fails to be imported, this row of data
Will be output to the BadFile.

When SQL * Loader is run with filtering conditions to import data (the data filtering conditions are in the control file
), SQL * Loader also outputs the following files as needed:
Discard Files: Files that contain data records discarded by filtering conditions.

Common SQL * Loader command parameters:> SQLLDR keyword = value [, keyword = value,...]
Userid: username/password, for example, scott/tiger @ orcl
Control: name of the input control file
Log: Output log File Name
Bad: Output Error Data File Name
Data: input data file name
Discard: name of the output discarded data file
Errors: number of errors allowed. If the number of error records exceeds this value, the program exits immediately.
Slient: hides messages during running.

3. Import Data

The control file (control. ctl) contains the following content:

LOAD DATA
INFILE 'it.csv'
Into table employee
Fields terminated by ', 'optionally enclosed '"'
(
EMD_ID,
EMP_NAME,
EMD_DATE DATE "YYYY/MM/DD ",
DPT_ID
)

Execute SQL * Loader:
> Sqlldr userid = username/password @ orcl control = control. ctl

,