Ask the Expert

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 ","
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

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

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: