OraFAQ Forum: Server Utilities » Multiple fields terminations

Home » RDBMS Server » Server Utilities » Multiple fields terminations

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Multiple fields terminations [message #260645]

Mon, 20 August 2007 10:15

mcvspw2@hotmail.com
Messages: 1
Registered: August 2007
Location: Liverpool

Junior Member

All,
Please can anyone tell me if there is a command for multiple field terminations?
Data Example
ZR01;TB1065C310O030014300F/ANPMUNIX0000000000/F;1/02

I want to load the data into the database fields where ; or / are the field terminators.

Field1 Field2 Field3 Field4 F5 F6
ZR01 TB1065C310O030014300F ANPMUNIX0000000000 F 1 02

Thanks in advance

Report message to a moderator

Re: Multiple fields terminations [message #260658 is a reply to message #260645]

Mon, 20 August 2007 11:19

Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Afaik, there is none in SQL*Loader.
You can do it with an external table and SQL.
Or you can use sed to replace one of the termination character by the other before calling SQL*Loader.

Regards
Michel

Report message to a moderator

Re: Multiple fields terminations [message #260688 is a reply to message #260645]

Mon, 20 August 2007 13:10

Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA

Senior Member

You can specify different terminators for different fields, but not different terminators for the same field.

Report message to a moderator

Re: Multiple fields terminations [message #260732 is a reply to message #260645]

Mon, 20 August 2007 15:52

Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA

Senior Member

-- test.dat:

ZR01;TB1065C310O030014300F/ANPMUNIX0000000000/F;1/02

-- test.ctl:

load data
infile test.dat
into table test_tab
fields
(field1 terminated by ';',
field2 terminated by '/',
field3 terminated by '/',
field4 terminated by ';',
f5 terminated by '/',
f6 terminated by whitespace)

-- table:

SCOTT@10gXE> create table test_tab
  2    (field1 varchar2 ( 6),
  3  	field2 varchar2 (21),
  4  	field3 varchar2 (19),
  5  	field4 varchar2 ( 6),
  6  	f5     varchar2 ( 6),
  7  	f6     varchar2 ( 6))
  8  /

Table created.

-- load:

SCOTT@10gXE> host sqlldr scott/tiger control=test.ctl log=test.log

-- result:

SCOTT@10gXE> select * from test_tab
  2  /

FIELD1 FIELD2                FIELD3              FIELD4 F5     F6
------ --------------------- ------------------- ------ ------ ------
ZR01   TB1065C310O030014300F ANPMUNIX0000000000  F      1      02

SCOTT@10gXE>

Report message to a moderator

Re: Multiple fields terminations [message #260813 is a reply to message #260732]

Tue, 21 August 2007 00:47

Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Great!

I hope that OP's fields always terminate with the same character and not it is either one or the other (kind of fields terminated by sometimes ';' or sometimes '/').

Regards
Michel

Report message to a moderator

Previous Topic:	import problems
Next Topic:	All data not imported when doing full db import

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jun 22 21:43:29 CDT 2024