I need to import text files into Oracle, I need to create a table and use Loader import, I used to write to SQL, I think it is really trouble. .
Oracle SQL * Loader Guide (Reprinted)
SQL * Loader is a tool for an Oracle database to import external data. It is similar to DB2's LOAD tool, but more choices, it supports changing load modes, optional loading and multi-table loading.
How to use SQL * Loader tools we can use Oracle's SQLLDR tool to import data. For example: SqlLDR scott / tiger control = loader.ctl control file (loader.ctl) will load an external data file (including separator). Loader.ctl is as follows: Load Data Infile 'c: /data/mydata.csv' Into Table EMP Fields Terminated By, "Optionally Enclosed By '" (Empno, Empname, Sal, Deptno)
MyData.csv is as follows: 10001, "Scott Tiger", 1000, 40 10002, "FRANK NAUDE", 500, 20 below are example control files for specifying the length of the recording length. "*" Represents the data file with this file with this file, ie, use the Begindata section to identify the data. load data infile * replace into table departments (dept position (02:05) char (4), deptname position (08:27) char (20)) begindata COSC COMPUTER SCIENCE ENGL ENGLISH LITERATURE MATH MATHEMATICS POLY POLITICAL SCIENCE Unloader this tool Oracle There is no tool to export data to a file. However, we can use SQL * Plus SELECT and FORMAT data to output to a file: set echo off newpage 0 Space 0 PageSize 0 Feed Off Head Off Trimspool On Spool ORADATA.TXT SELECT COL1 || ',' || COL2 || ',' || col3 from tab1 where col2 = 'xyz'; spool off
Alternatively, it is also possible to use UTL_FILE PL / SQL Package: Rem Remember to update INITSID.ORA, UTL_FILE_DIR = 'C: / ORADATA' Parameter Declare FP UTL_FILE.FILE_TYPE; begin fp: = UTL_FILE.FOPEN ('C: / ORADATA' , 'Tab1.txt', 'W'); UTL_FILE.PUTF (FP, '% S,% S / N', 'TextField', 55); UTL_FILE.FCLOSE (FP); END; /
Of course, you can also use third-party tools, such as SqlWays, Toad for Quest, etc.
Loading specified length or variable length records, such as: LOAD DATA INFILE * INTO TABLE load_delimited_data FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY ' "' TRAILING NULLCOLS (data1, data2) BEGINDATA 11111, AAAAAAAAAA 22222," A, B, C, D, "Below is imported fixed position (fixed length) data example: Load Data Infile * Into Table Load_positional_data (Data1 Position (1: 5), Data2 Position (6:15)) Begindata 1111AAAAAAAAAA 25222bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb
Skipping the data line: You can use the "Skip N" keyword to specify how many line data can be skipped when importing. Such as: load data infile * inTo Table Load_positional_data Skip 5 (Data1 Position), Data2 Position (6:15)) Begindata 11111AAAAAAAAAAAAAAAAAAAAAAAAAAAAAA A
Modify data when importing data: You can modify the data when importing data to the database. Note that this is only suitable for conventional imports, not suitable for Direct import mode. Such as: Load Data Infile * Into Table Modified_data (R_NO "MY_DB_SEQUENCE.NEXTVAL", Region Constant '31', Time_Loaded "To_Char (sysdate, 'hh24: mi' ) ", Data1 Position (1: 5)": DATA1 / 100 ", DATA2 POSITION (6:15)" Upper (: DATA2) ", DATA3 POSITION (16:22)" To_date (: Data3, 'YYMMDD') " Begindata 11111aaaaaaaaa991201 22222bbbbbbbbb990112
LOAD DATA INFILE 'mail_orders.txt' BADFILE 'bad_orders.txt' APPEND INTO TABLE mailing_list FIELDS TERMINATED BY "," (addr, city, state, zipcode, mailing_addr "decode (: mailing_addr, null,: addr,: mailing_addr)", Mailing_city "decode (: mailing_city, null,: city ,: mailing_city)", mailing_state)
Import data into multiple tables: Load Data Infile * Replace Into Table Emp When Empno! = '(Empno Position (1: 4) Integer External, Ename Position (6:15) Char, Deptno Position (17:18) Char, Mgr Position (20:23) Integer External INTO TABLE PROJ WHEN Projno! = '(Projno Position (25:27) Integer External, Empno Position (1: 4) Integer External) Import Selected Record: Such an example : (01) Represents the first character, (30:37) representing the character between 30 to 37: load data infile 'mydata.d' discardfile 'mydata.bad' discardfile 'mydata.dis' append Into Table my_selective_table when 01) <> 'h' and (01) <> 't' and (30:37) = '19991217' (Region constant '31', service_key position (01:11) integer external, call_b_no position (12:29) Char)
Skip Some fields while importing: You can separate the data with post (x: y). In Oracle8i, you can implement it by specifying the FILLER field. The Filler field is used to skip, ignore the fields in the imported data file. Such as: Load Data Truncate Into Table T1 Fields Terminated By ',' (Field1, Field2 Filler, Field3)
Import Multi-Bank Record: You can use one of the following two options to implement multiplexed data into a record:
Concatenate: - Use When Sql * Loader Should Combine The Same Number of Physical Records Together To Form One Logical Record.
CONTINUEIF - USE IF A CONDIS SHOULD BE TREATED AS One. Eg. By Having A '#' CHARACTER IN COLUMN 1.
Submission of SQL * Loader data: Under normal circumstances, it is submitted after importing data file data. You can also specify the number of records per submission by specifying the ROWS = parameter.
Increasing SQL * Loader: 1) A simple and easily ignored problem is that there is no index and / or constraint (primary key) for the imported table. If this is done, it will significantly reduce database import performance even when using ROWS = parameters. 2) You can add Direct = True to improve the performance of imported data. Of course, this parameter cannot be used in many cases. 3) You can turn off the log of the database by specifying the Unrecoverable option. This option can only be used with Direct. 4) You can run multiple import tasks simultaneously.
The difference between conventional importing and Direct import mode: General import can import data by using an INSERT statement. Direct imports can skip the relevant logic of the database (direct = true) and import data directly into the data file.
