Problem loading comma-separated values

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
)

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.