Q

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.

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 ","
(
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 | 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 last published in June 2006

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close