Home » RDBMS Server » Server Utilities » Multiple fields terminations
Multiple fields terminations [message #260645] Mon, 20 August 2007 10:15 Go to next message
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



Re: Multiple fields terminations [message #260658 is a reply to message #260645] Mon, 20 August 2007 11:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
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


Re: Multiple fields terminations [message #260688 is a reply to message #260645] Mon, 20 August 2007 13:10 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
You can specify different terminators for different fields, but not different terminators for the same field.
Re: Multiple fields terminations [message #260732 is a reply to message #260645] Mon, 20 August 2007 15:52 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
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> 



Re: Multiple fields terminations [message #260813 is a reply to message #260732] Tue, 21 August 2007 00:47 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Great! ./fa/2115/0/

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
Previous Topic: import problems
Next Topic: All data not imported when doing full db import
Goto Forum:
  


Current Time: Sun Dec 04 18:57:08 CST 2016

Total time taken to generate the page: 0.18059 seconds