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.

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.

By submitting your personal information, you agree to receive emails regarding relevant products and special offers from TechTarget and its partners. You also agree that your personal information may be transferred and processed in the United States, and that you have read and agree to the Terms of Use and the Privacy Policy.

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.

0 comments

E-Mail

Username / Password

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