Problem loading comma-separated values

I have a problem using comma-separated values to load into an Oracle table using SQL Loader. 
data file---data.dat
name  addr   city
king  , 11,newyork , us
peter , 22,southlane , us

control file
load data
infile 'data.dat'
append into emp
fields terminated by ","
(
ename,
eaddr,
ecity
)
Due to the presence of a comma in the address provided in the datafile, I am unable to load it successfully. Please tell me a solution.

    Requires Free Membership to View

    Login

The FIELDS TERMINATED clause defines the delimiter as the comma. So every time SQL*Loader hits a comma, it knows the subsequent data is the start of a new field. If you want to have the field contain the delimiter you have two options: either change the delimiter or optionally enclose the field within a secondary character.

For instance, you can change the text file to something like the following: 

king  | 11,newyork | us
peter | 22,southlane | us
Now that I have changed the delimiter to the vertical bar, change your control file to contain FIELDS TERMINATED BY "|".

The other option is to optionally enclose your data in quotes similar to the following: 

king  , "11,newyork" , us
peter , "22,southlane" , us
Now that the second field is enclosed in quotes, you can use the FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'. That last part is a double quote enclosed in two single quotes.

This was first published in June 2006

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top