Database Reference
In-Depth Information
How Do I Load Delimited Data?
Delimited data
, or data that is separated by some special character and perhaps enclosed in quotes, is the most
popular data format for flat files today. On a mainframe, a fixed-length, fixed-format file would probably be the
most recognized file format, but on UNIX/Linux and Windows, delimited files are the norm. In this section, we will
investigate the popular options used to load delimited data.
The most popular format for delimited data is the
comma-separated values
(
CSV
) format. In this file format, each
field of data is separated from the next by a comma. Text strings can be enclosed within quotes, thus allowing for the
string itself to contain commas. If the string must contain a quotation mark as well, the convention is to double up the
quotation mark (in the following code we use“” in place of just
"
). A typical control file to load delimited data will look
much like our first example earlier, but the
FIELDS TERMINATED BY
clause would generally be specified like this:
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
It specifies that a comma separates the data fields, and that each field
might
be enclosed in double quotes. Let's
say that we were to modify the bottom of this control file to be as follows:
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(DEPTNO, DNAME, LOC)
BEGINDATA
10,Sales,
"Virginia,USA"
20,Accounting,
"Va, ""USA"""
30,Consulting,Virginia
40,Finance,Virginia
When we run SQLLDR using this control file, the results will be as follows:
EODA@ORA12CR1> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 Sales Virginia,USA
20 Accounting Va, "USA"
30 Consulting Virginia
40 Finance Virginia
Notice the following in particular:
Virginia,USA
in department
10
: This results from input data that was
"Virginia,USA"
.
This input data field had to be enclosed in quotes to retain the comma as part of the data.
Otherwise, the comma would have been treated as the end-of-field marker, and
Virginia
would have been loaded without the
USA
text.
•
Va, "USA"
: This resulted from input data that was
"Va, ""USA"""
. SQLLDR counted the
double occurrence of
"
as a single occurrence within the enclosed string. To load a string
that contains the optional enclosure character, you must ensure the enclosure character is
doubled up.
•
