Re: How do I use a field delimiter of more than 1 char in the data file?

From: Hermen W. Huiskamp <ihwh_at_iname.com>
Date: 1997/07/12
Message-ID: <33C78080.31EA1DBB@iname.com>#1/1

This is a multi-part message in MIME format.

--------------E052DBFB76F4DD5244F1367E
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Ben wrote:

> I appreciate your input Leo, but I could have easily done string
> substitution obviously (perl is my language of choice) in about 2
> seconds. But, I am wondering if there is a way/option to set in order
>
> for the control file and sqlload to recognize a delimiter that is more
>
> than one character or is this simply impossible? It would just be
> nice to know how (just to make my sporatic users' data bullet-proof).
>
> Thanks!
> - Ben
>
> Leo Put <lp2014_at_vum.be> wrote:
>
> : You could run your file through sed and convert the 3-character
> : delimeter to a 1 character delimiter (eg. |).
> : Then you should be able to use sqlload.
> :
> : sed -e 's/:::/|/g' <fromfile >tofile
> :
> : Leo Put
> : Vlaamse UitgeversMaatschappij Belgium
> : lp2014_at_vum.be
> :
> : >
> : > I would like three delimiter characters because the data file I am
 

> : > using is a catalog generated by users' input through a cgi
 program.
> : > Thus if I choose a single character delimiter to load in the
 control,
> : > whether it is ";" or ":" or "," or "[" or whatever, it is
 possible
> : > that a user may have put in that character into a field in the
 data
> : > file, which would screw up the automated loading. Thus if I make
 a
> : > three character delimiter, it is much less likely that a user will
 

> : > happen to put in a sequence of ":::" in a data field.
> : >
> : > - Ben
> <snip>

 Dear Ben,

When working with SQL*Loader you must bear in mind that it will only recognise a delimiter being a single byte character. In my experience it may be any value (0-255).
Your message reads as if you get data from another system, so another option is to look if the records have a fixed format, or can be obtained in fixed format. In that way you don't have to worry about whatever characters have been put in by the end users. SQL*Loader will reject rows having an incorrect contents (data format) in fields, so that could end your worries.

F&C

Hermen W. Huiskamp

--------------E052DBFB76F4DD5244F1367E
Content-Type: text/x-vcard; charset=us-ascii; name="vcard.vcf"
Content-Transfer-Encoding: 7bit
Content-Description: Card for Huiskamp, Hermen W.
Content-Disposition: attachment; filename="vcard.vcf"

begin:          vcard
fn:             Huiskamp, Hermen W.
n:              Huiskamp;Hermen W.
org:            ORDINA HDT (IT-Consultancy)
adr:            Goeverneurkade 41;;;Voorburg;;2274 KK;Nederland
email;internet: ihwh_at_iname.com
tel;work:       06/54245479
tel;fax:        070/3000719
tel;home:       070/3000715

x-mozilla-cpt: ;0
x-mozilla-html: FALSE
end: vcard

--------------E052DBFB76F4DD5244F1367E-- Received on Sat Jul 12 1997 - 00:00:00 CDT