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

Source: Internet
Author: User
Tags connect how to use sql sql loader oracle database

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.