To direct SQL*Loader to access the data files as comma-separated-values format files, use the CSV clause. This assumes that the file is a stream record format file with the normal carriage return string (for example, \n on UNIX or Linux operating systems and either \n or \r\n on Windows operating systems). Record terminators can be included (embedded) in data values. The syntax for the CSV clause is as follows:
FIELDS CSV [WITH EMBEDDED|WITHOUT EMBEDDED] [FIELDS TERMINATED BY ','] [OPTIONALLY ENCLOSED BY '"']
The following are key points regarding the FIELDS CSV clause:
The SQL*Loader default is to not use the FIELDS CSV clause.
The WITH EMBEDDED and WITHOUT EMBEDDED options specify whether record terminators are included (embedded) within any fields in the data.
If WITH EMBEDDED is used, then embedded record terminators must be enclosed, and intra-datafile parallelism is disabled for external table loads.
The TERMINATED BY ',' and OPTIONALLY ENCLOSED BY '"' options are the defaults and do not have to be specified. You can override them with different termination and enclosure characters.
When the CSV clause is used, only delimitable data types are allowed as control file fields. Delimitable data types include CHAR, datetime, interval, and numeric EXTERNAL.
The TERMINATED BY and ENCLOSED BY clauses cannot be used at the field level when the CSV clause is specified.
When the CSV clause is specified, normal SQL*Loader blank trimming is done by default. You can specify PRESERVE BLANKS to avoid trimming of spaces. Or, you can use the SQL functions LTRIM and RTRIM in the field specification to remove left and/or right spaces.
When the CSV clause is specified, the INFILE * clause in not allowed. This means that there cannot be any data included in the SQL*Loader control file.
The following sample SQL*Loader control file uses the FIELDS CSV clause with the default delimiters:
LOAD DATA INFILE "mydata.dat" TRUNCATE INTO TABLE mytable FIELDS CSV WITH EMBEDDED TRAILING NULLCOLS ( c0 char, c1 char, c2 char, )