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 first published in June 2006

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close