Sample instructions for importing data to an Oracle database table using the SQLLDR command

Source: Internet
Author: User

csv:comma-separated values (comma-separated values) are abbreviations for multiple-line text files with comma-delimited fields Sqlldr are sql*loader abbreviations, After installing the full version of the Oracle client, this tool uses SQLLLDR to import regular text data (such as CSV) into the datasheet Import Example: Import a CSV text file andon.txt into a Testandon table in manpowertest Testandon table structure CREATE TABLE Testandon
(
Build_no VARCHAR2 (5),
Device_no VARCHAR2 (5),
Btn_no VARCHAR2 (5),
Flag VARCHAR2 (1),
Data_time Date--This is the time format for special handling when importing
)1.Create a. CTL import control file with Notepad or other text software, where C:\andon.ctl is the example ofOPTIONS (skip=1)-Skips over 1 lines, that is, skipping the first row of the CSV file header does not import
Load data
InFile ' C:\andon.txt '--the path of the CSV text to import
Append into table Manpowertest.testandon--which table to import into the database
Field terminated by ","--the text to be imported is delimited by what symbol, here is the comma
(Build_no,--which fields to import into the table
Device_no,
Btn_no,
Flag
Data_time timestamp "Yyyy-mm-dd hh24:mi:ss"--format conversion with timestamp "Yyyy-mm-dd hh24:mi:ss" because the field type in the datasheet is a date type, not a literal type )
2.Execute the SQLLDR command in cmd as follows Sqlldr Userid=manpowertest/[email protected] Control=c:\andon.ctl Log=c:\andon.log

Archive 1:andon.txt

Building number, device number, button code, switch status, trigger time

a2-1,4,2,1,2014-10-29 15:14:13
a2-1,30,2,0,2014-10-29 14:51:36
a2-2,10,2,0,2014-10-29 14:18:53
a2-2,11,2,0,2014-10-29 15:11:07
a2-2,12,2,0,2014-10-29 14:40:13

Archive 2:andon.ctl

OPTIONS (Skip=1)
Load data
InFile ' C:\andon.txt '
Append into table Manpowertest.testandon
Fields terminated by ","
(Build_no,
Device_no,
Btn_no,
Flag
Data_time timestamp "Yyyy-mm-dd hh24:mi:ss")

Sample instructions for importing data to an Oracle database table using the SQLLDR command

Related Article

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.