How to import data in Excel to orcale database table

Recently, we have to perform a product test. Because there is too little data in it, I need to add 10000 records to the database table in the first place. I don't think it will take a few months for you to add them one by one. People will also be exhausted. So I quickly thought of using tools.

For online search, we mostly use sqlldr to import data from an Excel file to an oracle table. But it is not clearly written. If you follow the above instructions, it will fail.

I will combine my methods with the information I found on the Internet into a complete process, hoping to help you:

 

Method:

1. Install Oracle10g or 11G on the local machine, and then install PLSQL.

2. Configure the net manager in Oracle 10g/11g. Configure the database to be connected

3. Run PLSQL to query the table records that you want to import data.

Iv. Use sqlloader

1. Microsoft Excel file (which is the content you want to import, consistent with the record format in the table) (f) → save as (a) →
The storage type is: delimiter, the name is text.txt, and saved to C :/

2. Connect to SQL * Plus
Log On As A system/Manager User,
SQL> conn system/Manager
Create Table Structure
SQL> Create Table Test (ID number, -- No. Username varchar2 (10), -- Username Password varchar2 (10), -- password SJ varchar2 (20) -- Build date ); if you have a ready-made table, you can skip this step to create the table structure.

3. 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' and is a tab)

(ID, username, password, SJ) -- defines the sequence of Columns

 

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

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

5. Connect to SQL * Plus to check whether the input is successful.

 

Let me give you a practical example:

Connect to SQL * Plus

Log On As A system/Manager User,

SQL> conn system/Manager

 

Control File: Input. CTL. The content is as follows:

Load data

Infile 'C:/loop.txt'

Append into Table Name

Fields terminated by X '09'

Trailing nullcols

(Field name 1,

Field name 2,

Field name 3 date "YYYY-MM-DD
Hh24: MI: SS ", note: this is a date field

Field name 4,

...

Field name n-1,

Field name N)

 

Use the SQL * loader command in the DOS window of the Local Machine to input data.

 

C:/> sqlldr userid = system/manager @ database instance name control = input. CTL