Teach you how to use SQL Loader to export Excel data to Oracle

This article describes how to use SQL loader to insert data from an Oracle database into an Excel file.

Implementation purpose: Insert data from an Excel file into an Oracle database

Implementation steps:

1. Open Microsoft Excel 2000

2, file (F) → new (N) → workbook →

3, after the input data, save for Test.xls,

4, document (F) → Save As (A) →

Save type is: Tab delimited, named Text.txt, save to C: \

5, must first create the table structure:

Connect to Sql*plus to System/manager user login,

Sql> Conn System/manager

Create a table structure

Sql> CREATE TABLE Test

(

ID number,--Ordinal

USERNAMEVARCHAR2 (10),--User name

PASSWORDVARCHAR2 (10),--Password

SJ VARCHAR2 (20)--date established

);

This column more highlights: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

6, to create sql*loader input data required by the file, are saved to C:\, edited with Notepad:

Control file: Input.ctl, the contents are as follows:

Load data--1, control file identification

InFile ' Test.txt '--2, the data file to enter is named Test.txt

Append into Table test--3, append records to table test

Fields terminated by X ' 09 '--4, field terminated by X ' 09 ', is a tab character (tab)

(ID,USERNAME,PASSWORD,SJ)-----Define column Correspondence order

A, insert, by default, requires the table to be empty at the beginning of the data load

b, append, append new records to the table

c, replace, delete old records, replace the new Loaded records

D, Truncate, ditto

7, in the DOS window using the Sql*loader command to achieve data input

C:\>sqlldr Userid=system/manager Control=input.ctl

The default log file name is: Input.log

Default bad record file is: Input.bad

If you are importing a database remotely, the input string should read:

C:\>sqlldr userid=system/manager@servicename_192.168.1.248 Control=input.ctl

8, connect to Sql*plus, to see whether the successful input, can compare Input.log with the original Test.xls file, to see whether the data are all imported, whether the import success.