How to import Oracle database to Excel Data

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,

 
 

  
  
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 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. 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. 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.

 
 

  
  
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:

 
 

  
  
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.

Article by: http://database.csdn.net/c_oracle/tag/2