Q

Changing the column values with hyphens to commas

How can I decode while importing all the data to a table? For example, I have a .CSV file with two columns, such as:
Name     Address
selva    addr1-addr2-addr3
joy      asd1-asd2-asd3 
When I use the SQL loader to load this data to a table, I need a CTL file. How can I decode this date to change the column values with hyphens to (addr1-addr2-addr3) to comma (addr1,addr2,addr3)?
INSERT, APPEND, REPLACE, TRUNCATE in the control file refers to instruction to the database to:

1. Insert rows in an already empty table; this instruction will fail if the table is not empty.
2. Insert rows in a non-empty table.
3. Replace or truncate rows in a table and insert data in the data file or in the control file. Data can be part of the control file preceded by the instruction BEGINDATA.

In order for you to replace the hyphens with comma in the rows in the table, you need to create a before insert trigger on the table as follows.

Say the table is called TEST with the following description:

NAME VARCHAR2(100)
ADDRESS VARCHAR2(200)

The trigger text to achieve your objective is as follows:

CREATE OR REPLACE TRIGGER REPLACE_DASHES_WITH_COLUMN
BEFORE INSERT ON TEST
FOR EACH ROW
BEGIN
:NEW.ADDRESS := REPLACE(:NEW.ADDRESS,'-', ',');

END;
/

The control file (test.ctl) listing will be as follows:

LOAD DATA
INFILE *
INTO TABLE TEST
REPLACE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(NAME, ADDRESS)
BEGINDATA
Sonali Bendre, c/o Azim Fahmi-Somewhere outthere-Bollywood
Preity Zinta, "c/o Azim Fahmi-In your dreams-None of your business"
"George Bush", 1600 Pennsylvania Avenue-Whitehouse-Washington D.C.
Tony Blair, 10 Downing Street-London-UK-POSTALCODE HERE
Telaram Thakur, 10 Shabzi Mandi-Jackson Heights-New York

From command line if you type the following command:

sqlldr USERID= / {@db_name} control=test.ctl log=test.log bad=test.bad

where @db_name is optional

The dashes will be replaced with comma because of the compiled before user trigger above.

This was first published in January 2005

Dig deeper on Using Oracle PL-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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close