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.