Re: Need to use a rare field separator in *.dat files

On Mar 10, 2:01 pm, "Ramon F Herrera" <r..._at_conexus.net> wrote:
> All my *.dat files are created programmatically and therefore I can
> insert any ASCII character as a field separator. I have used
> characters such as ':' or '|' to mark the border between the fields,
> but needless to say, the data may contain those characters.
>
> What I would like to do is to use some rare (but visible) high ASCII
> value which will be essentially impossible to be present in the raw
> datafiles.
>
> Is that the right approach (in some cases I cannot switch to fixed
> length records)?
>
> How do I define such character in the *.ctl files?
>
> This is what I have now:
>
> FIELDS TERMINATED BY "|"
>
> TIA,
>
> -Ramon F Herrera

Using the extended ASCII table found here:

http://www.cdrummond.qc.ca/cegep/informat/Professeurs/Alain/files/ascii.htm

you might consider using a hex value of A9 or greater as your separator (presuming, of course, you can generate this character in your .dat file) as characters at that end of the extended ASCII range are not likely to be present in any text you might be processing. A simple

FIELDS TERMINATED BY X'A9' in your .ctl file (as an example) should solve your problem, again provided you can generate that character with your .dat file generator.

Years ago I wrote a routine in a Pro*C program to scan the input file and 'register' all characters in the file, after which I would select the first ASCII character in my array having no occurrences as my field separator. It worked well for years and took practically no time at all (given the routine was written in C) however choosing some uncommon character which isn't present on most computer keyboards is probably the direction to take.

David Fitzjarrell Received on Sat Mar 10 2007 - 15:02:30 CST