Import large text (TXT) files to Oracle using SQLLDR

Source: Internet
Author: User

There are several ways to import text files into an Oracle database, but if you import a text file that is too large, such as a 5g,10g text file, there are some ways to do it, such as the import text feature in Plsql developer, and if the text file is too large, not only is the import slow, The middle is also prone to error. At this time Sqlldr will be able to work, SQLLDR can insert 5W per second to Oracle data, you can customize the control file, import control, and the import process is not easy to error, the following easy to record the import process.

1.SQLLDR Import Control File description

1 Load Data2InFile'/home/oracle/42.txt'                    //absolute location of text files3Append into table Doupoidx//the name of the table to import4Fields terminated by','               //delimiter definition, here is the comma5Optionally enclosed by'"'            //Filter "No.6TRAILING Nullcols//the order of the corresponding fields. 7 (8Time TimeStamp'YYYY-MM-DD Hh24:mi:ss',9 Price ,Ten volume, One Interest A)

2. Execute the import command under Oracle User

Sqlldr  userid=scott/[email protected] control =/home/oracle/input.ctl       // user name/ Password @ Instance name control= The location of the control file

Small test on optionally enclosed by

left without quotation marks to the right: successful, as-is import
111 "
222 ""
333 "" "

left with quotation marks to the right: failed, second enclosure string not present
"The
"" 555
"" " 666

left and right have equal odd number of quotes, success, quote number =2n+1 when importing data with N quotes
"777"
"" " 888 " ""

left and right have equal even number of quotes, failed, no Terminator found after TERMINATED and enclosed field
"" 999 ""
"" "" " AAA" "" "

An even number of odd sides on one side: failure, error message for both of the above
" BBB" "
" CCC "
"" DDD ""
" EEE" ""

even numbers with unequal sides: failed, no Terminator found after TERMINATED and enclosed field
"" FFF "" ""
"" "" GGG ""

odd numbers on both sides: success, quotes =2n+1 when importing data with N quotes
" HHH " ""
"" " III"

Summary: Either the left side cannot be quoted, or if there are quotes around it must be an odd number

 

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.