How to import CSV text data to Oracle

 

1. Input. CTL

Load data
Infile '/u1/out/cxcq201/test.csv' -- specify the path here, if not running in the same directory
Replace into Table tc_ccj_file
Fields terminated by "," optionally enclosed '"'
Trailing nullcols
(
Ccj01,
Ccj02,
Ccj03,
Ccj04,
Ccj05,
Ccj06
)

 

2.test.csv File

 

3. sqlldr xxx/xxx @ host name control =/u1/out/cxcq201/input. CTL bad =/u1/out/cxcq201.txt

 

Explanation:

Load data
Infile't. dat // file to be imported
// Infile 'tt. date' // import multiple files
// Infile * // the content to be imported is after begindata in the control file.
　　
Into Table table_name // specify the mounted table
Badfile 'C:/bad.txt '// specify the bad file address
　　
* ************ The following are four ways to load tables.
Append // Add the data in the original table to the end
// Insert // load an empty table. If the original table has data, sqlloader stops the default value.
// Replace // all data in the original table will be deleted
// Truncate // if the specified content is the same as that of replace, the truncate statement is used to delete the existing data.
　　
* ************ The specified terminated can be either at the beginning of the table or in the internal fields of the table.
Fields terminated by ', 'optionally enclosed '"'
// Load this data: 10, LG, "" LG "," LG, LG"
// Result in the Table: 10 LG "LG" LG, LG
// Terminated by X '09' // in hexadecimal format '09'
// Terminated by writespace // load this data: 10 LG
　　
Trailing nullcols ************** when the table field does not have a corresponding value, it can be blank