Ways to import text data in Oracle

oracle| data
Using the Sql*loader command to load in a Dos environment


Data transfer tools using other databases


Data loading capabilities in Oracle Enterprise Manager


Specific technology to achieve


First, the Dos environment loading


1. First, the server-side listening service must be turned on.


Test method: Dos input


C:\>sqlplus Username/password@servicename


2, then use Oracle's SQLLDR command for data import


Pre-conditions


1 The Oracle database end must have built the structure of the data table that needs to be imported


2 A data source file The following example is a tab-delimited text file Model.txt, which is exported in Excel tables


3 manual editing of a Xxx.ctl control file


4 Command line loading data


The following example:


The following files are placed by default to C:\ , if not, you need to indicate the full path


1. Command Control file Input.ctl content


Command
Description

Load data
1, Control file identification

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

Append into table system. Tower-level property sheet
3. Append Records to table test

Fields terminated by X ' ' 09 '
4, the specified separator, the field terminated by X ' ' 09 ', is a tab character (tab)

(number, name, size)
5, define the order of the column corresponding table




control specifies how the data is inserted in the file keyword


Insert, the default way to require a table to be empty at the start of a data load


Append, append new record in table


Replace, delete old records, replace new Loaded records


Truncate, ditto


Using the Sql*loader command in a Dos window to implement data import


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


The default log file name is: Input.log


Default bad record file is: Input.bad


Ii. use of other database transfer tools


The following is an example of the SQL Server Import Export Wizard


1. Set up the data source server in the Import and Export Wizard of data, select the data source in the instance select SQL Server


2, and then specify the Oracle data source to import


3. Oracle attribute information needs to be configured


It should be noted that the user information for the logon database is the scenario name after the data import, that is, the table in SQL Server after the import is named Username in Oracle. Table name


The following prompts allow you to completely import data tables and views from SQL Server, or you can use Query statements to return the columns or rows you want to select.

Data loading function in Oracle Enterprise Manager


Log on to Oracle's console interface and use the data loading tool for a separate datasheet


The middle need to specify the control file, and so on, with the DOS load consistent, no longer repeat

[Http://www.china-dev.com/school/database/wz/37WR43G3ZZZ91NJCHS.shtml]