Quick Method for Bulk Importing Text Files into Oracle (sqlldr Implementation)

Preface

When I was doing a project recently, I needed to import a batch of3More than 0 million POI data into the Oracle database, the simple insertion import speed is too slow, use sqlldr for batch import3More than 0 million data took2About 0 minutes, the speed is still good, now I share it with everyone, the specific method is as follows:

1.Create a new import control file input.ctl, the content of the file is as follows:

Load data
Characterset UTF8
Infile 'H:\POI\baidu.txt'
Append into table tbl_poi_baidu
fields terminated by ","
Optionally enclosed by '""'
Trailing nullcols
(POI_NAME,CLASS_ALL,ADDR,PHONE,TEL,LONGITUDE,LATITUDE,CITY,CITYCODE,PROVINCE,DISTRICT,STREET,STREENUMBER,CLASS_1,CLASS_2,CLASS_3,CLASS_4)

2Command line window input:

sqlldr userid=sde/sde control=input.ctl log=log.out discard=discard.txt errors=1000000 

Note:The file character set must be consistent with the character set of Oracle services, it is recommended to save the file as UTF-8No BOM, add Characterset UTF to the import file8 Solve the problem of Chinese garbled characters in Oracle data import: export NLS_LANG='SIMPLIFIED CHINESE_CHINA.AL'32UTF8

Quickly merge files in DOS environment: copy *.csv newfile.txt

Summary

That's all for this article. I hope it can help everyone learn or use Oracle Database. If you have any questions, you can leave a message for communication.

Statement: The content of this article is from the Internet, the copyright belongs to the original author. The content is contributed and uploaded by Internet users spontaneously. This website does not own the copyright, has not been edited by humans, and does not assume relevant legal liabilities. If you find any content suspected of copyright infringement, please send an email to: notice#oldtoolbag.com (When sending an email, please replace # with @ to report, and provide relevant evidence. Once verified, this site will immediately delete the content suspected of infringement.)