Sql Loader issue - loading 2 values into a column

[swapna_dba]

i have a .csv file as below:

john,australian
smith,australian
mary,"australian,irish"

this needs to be loaded into the table with 2 columns

firstname nationality
--------- ----------
john australian
smith australian
mary australian
mary irish

my control file is as below:

load data
infile '/home/oracle/sample.csv'
insert into abc
(firstname,nationality)

which gives me the result:

firstname nationality
--------- ----------
john australian
smith australian
mary "australian

Is it possible to insert 2 records for mary, 1 for australian and 1 for irish using sql *loader.

please advise....Thanks!

[skhanal]

I don't think you can do it easily. Does the record with dual nationality appear in badfile?. If yes then you can try running loader against badfile and skipping second column this time.

[jnjulian]

Another option is to write a short PERL script to pre-process the data load file and look for the dual nationality condition in each row. When found, add a duplicate row to the load file and split the two nationality values into the separate rows. Then process the loadfile as normal.

PERL is a great language for pre-processing and validation checking your data before loading it.

Regards,

~JJ

[Muppatz]

You can use "optionally enclosed by" in your control file, something like this:

load data
infile '/home/oracle/sample.csv'
insert into abc
fields terminated by ',' optionally enclosed by '"'
(firstname,nationality)

This should treat the "australian,irish" bit as a single column.

[skhanal]

Optionally enclosed by will remove the extra " in first pass but will not load two records.