Import Excel Data in Oracle

The following articles mainly show you how to use SQL * Loader to export Excel Data to Oracle, and then insert the relevant data in the excel file into the Oracle database, if you are interested in the actual operations, the following articles will provide you with relevant knowledge.

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. When SQL * Loader is implemented to export Excel Data to the Oracle database, all the files required for SQL * 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. 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 SQL * Loader in the DOS window to export Excel Data to the Oracle database. Use the SQL * Loader command 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.