Oracle sqlldr Import Export txt data file detailed

First, SQLLDR import txt

1. Preparation

a). txt file

This is to be saved as unsigned UTF-8.

b). Oracle Build Table

2. Write the control file Input_test.ctl

LOAD DATA

CHARACTERSET ' UTF8 '--Character Set settings
INFILE ' D:\input_test.txt '--The text data path to be imported, can write multiple
REPLACE into table Input_test--Empty the original data and Import mode append import with append into table T_name
Fields terminated by X ' 09 '--delimited by tabs
Trailing Nullcols--Allow empty column import

(col1,col2)

Note:

InFile ' D:\input_test.txt ' indicates the path of the data file that needs to be mounted

Append into table test data loaded tables:
(1) Append indicates that there is data in the table, plus the following
(2) INSERT indicates that empty tables are loaded and data is stopped. Default value
(3) REPLACE If there is data in the original table, it will be deleted
(4) TRUNCATE if the data to be loaded is the same as the current data, the loaded data replaces the existing data.
Fields terminated by ', '

The data used to represent tab tabs, which are delimited by ', ', with by X ' 09 ', that is, 16 binary "09", are used for the import of the data of the Tab tab file that Excel converts. Common delimiters and ' | '

Multilingual programmable Character Set encoding: CHARACTERSET ' UTF8 '

Execute under 3.DOS

Sqlldr System/[email protected], Control=c:\input\input_test.ctl log=c:\input\input_test.log bad=c:\input\input_ Test.bad

Valid keywords:

UserID-ORACLE Username/password

control– Control files

Log files for log– records

bad– Bad Data file

data– Data files

discard– Discarded data file

discardmax– maximum value allowed to discard data (all default)

Skip--Number of logical records to skip (default 0)

Load--Number of logical records to load (all default)

errors– number of error records allowed (default 50)

Rows-number of rows in conventional path bind array or between direct path data saves (record per commit, default: Regular path 64, all direct paths)

Bindsize--Size of conventional path bind array in bytes (default 256000)

The size of the buffer for each commit record (in bytes, default 256000)

Silent--Suppress output information (header,feedback,errors,discards,partitions)

direct– Import using Passthrough path (default false)

Parfile--Parameter file:name of file that contains parameter specifications

Parallel--Parallel import (default false)

FILE--file to allocate extents from

Skip_unusable_indexes--Disallow/allow unusable indexes or index partitions (default false)

Skip_index_maintenance--Do not maintain indexes, mark affected indexes as unusable (default false)

ReadSize--Size of Read buffer (default 1048576)

Paired with Bindsize, the smaller ones will automatically adjust to the larger ones. Sqlldr calculates a single record length, multiplied by rows, such as less than bindsize, does not attempt to expand rows to fill the bindsize, or bindsize if exceeded.

External_table--Use external table for load; not_used, Generate_only, EXECUTE (default not_used)

Columnarrayrows--Number of rows for direct path column array (Default 5000)

Streamsize--Size of direct path stream buffer in bytes (default 256000)

Multithreading--use multithreading in direct path

Resumable-Enable or disable resumable for current session (default false)

Resumable_name--text string to help identify resumable statement

Resumable_timeout--wait time (in seconds) for resumable (default 7200)

Date_cache--size (in entries) of date conversion cache (default 1000)

4. write. bat Batch

The above 3 steps have completed the TXT import, under Windows can also write the Sqlldr command as a batch file, double-click execution.

@echo off

Echo input_test

Pause-paused, suggested to join, so as not to error double-click to execute
@rem
Sqlldr System/[email protected], Control=c:\input\input_test.ctl log=c:\input\input_test.log bad=c:\input\input_ Test.bad
@rem
@rem Sqlldr System/[email protected], Control=c:\input\input_test.ctl rows=100000
Pause

Second, SQLLDR export txt

Export txt with spool

1. Write Output.sql

Sqlplus System/[email protected] as SYSDBA--Connect Oracle
CHARACTERSET Al32utf8--Set the encoding set
Set Trimspool on--open pool
Spool C:\output\output.txt--Pool output path
Set pagesize 0--page Setup
Set heading off--Close the table header
Set Linesize 32767--Maximum row display
Select ' # ' | | col1| | ' #,# ' | | col2| | ' #,# ' | | col3| | ' # '--Set the required column format. In this example, columns are separated by commas, and the column contents are caused by #.

From Test_data;
Exit --Exit Sqlplus
Spool off--close the pool
Pause

Note: The above command can be executed directly under DOS.

2. write. bat Batch

Then write the bat call above the Outputsql file, as follows:

cd/
Set nls_lang=. Al32utf8--setting character set UTF8
CHCP 65001--Conversion code page UTF8
@echo off
echo Data output
Pause
@rem
Sqlplus System/[email protected] as SYSDBA @c:\dmp_sql\output.sql
@rem
Pause

Oracle sqlldr Import Export txt data file detailed