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 Usernamevarchar2 (10), -- User Name Passwordvarchar2 (10), -- Password Sj varchar2 (20) -- Build date );

6. Create the files required for SQL * Loader input data and save them to C: \. Edit them in Notepad:

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', which 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.

(