Problem loading comma-separated values
I have a problem using comma-separated values to load into an Oracle table using SQL Loader. Due to the presence of a comma in the address provided in the datafile, I am unable to load it successfully.
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.
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 | usNow 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" , usNow 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.