How to export Excel data to an Oracle database

The following article describes how to use SQL * Loader to export Excel-related data to the Oracle database. The main purpose is to insert the actual application data in excel-related files into the Oracle database, the following describes the specific content of the article.

Steps:

1. Open MicroSoft Excel 2000

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

3. Input SQL * loaderand Save the Excel Data to 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,

The following is a code snippet:
 

SQL> conn system/manager

 
 

Create Table Structure

The following is a code snippet:
 

SQL> create table test

(

Id number, -- serial number

Usernamevarchar2 (10), -- User Name

Passwordvarchar2 (10), -- Password

Sj varchar2 (20) -- Build date

);

 
 

6. create SQL * The files required by the Oracle database for Loader input data are saved to C: and edited in Notepad:

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

Load data -- 1. The control file ID is infile test.txt --2respectively. The data file name to be input is test.txt append into table test -- 3. append records to table test.

Fields terminated by X09--4, field terminated on X09, is a TAB)

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

A. insert, which is the default mode. When SQL * Loader starts to load Excel data, the table must be empty.

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.

The following is a code snippet:
 

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 SQL * Loader is remotely imported to the Oracle database for the Excel database, the input string should be changed:

The following is a code snippet:
 

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

 
 

8. After connecting to SQL * plus, you can check whether all the Oracle databases are imported and whether they are successfully imported by comparing input.logwith the original test.xls file.