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
\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
The SQL*Loader default is to not use the
EMBEDDED options specify whether record terminators are included (embedded) within any fields in the data.
EMBEDDED is used, then embedded record terminators must be enclosed, and intra-datafile parallelism is disabled for external table loads.
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.
CSV clause is used, only delimitable data types are allowed as control file fields. Delimitable data types include
CHAR, datetime, interval, and numeric
BY clauses cannot be used at the field level when the CSV clause is specified.
CSV clause is specified, normal SQL*Loader blank trimming is done by default. You can specify
BLANKS to avoid trimming of spaces. Or, you can use the SQL functions
RTRIM in the field specification to remove left and/or right spaces.
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
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, )