Use SQL * Loader to export Excel Data to Oracle

Objective: To insert data in an excel file into an oracle database

 

Steps:

 

1. Open MicroSoft Excel 2000

 

2. File (F) → new (N) → workbook →

 

3. After the data is input, the storage disk is test.xls,

 

4. File (F) → save as (A) →

 

The storage type is: delimiter, the name is text.txt, and saved to C:

 

 

 

5. Create a table structure first:

Connect to SQL * Plus and Log On As A system/manager User,

SQL> conn system/manager

 

Create Table Structure

SQL> create table test

(

Id number, -- serial number

Username varchar2 (10), -- User Name

Password varchar2 (10), -- password

Sj varchar2 (20) -- Build date

);

 

6. Create the files required for SQL * Loader input data and save them to C:. use NotePad to edit them:

Control File: input. ctl. The content is as follows:

 

Load data -- 1. Control File ID

Infile 'test.txt '-the name of the data file to be input is test.txt

Append into table test -- 3. append records to table test

Fields terminated by X '09' -- 4. The field is terminated on X '09' and is a TAB)

(Id, username, password, sj) ----- defines the column Sequence

 

A. insert, which is the default mode. The table is required to be empty when data loading starts.

B. append: Add a new record to the table

C. replace: delete old records and replace them with newly loaded records.

D. truncate, same as above

 

7. Use the SQL * Loader command in the DOS window to input data.

 

C:> sqlldr userid = system/manager control = input. ctl

 

The default log file name is input. log.

The default bad record file is input. bad.

 

If the database is remotely imported, the input string should be changed:

C:> sqlldr userid = system/manager@serviceName_192.168.1.248 control = input. ctl

 

8. Connect to SQL * plusand check whether all data is imported and whether the data is successfully imported by comparing input.logwith the original test.xls file.