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...
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.