Re: SQL Loader Control file

Originally posted by starigopula

> We have about 200 files that we plan load on a nightly basis. Multiple
> clients send us data files nightly. While we can enforce field
> names/definitions, some clients are willing to send only pipe-
> delimited while others willing to send only "comma-delimited" files.

>

> Our choices are to have control files based on clients ( maintenance
> nightmare) (or) have a process that cleans the data (replace the
> delimeter with a standard delimeter) like you suggested.

>

> I was wondering if there would be something like

> FIELDS TERMINATED BY ('|',','.............)

>

> Any advise...Thanks for your help..........

>

> Best regards,

> Sumanth

After a quick test, both these syntaxes are incorrect resulting in error which identifies Pipe, Comma and/Or carret as one of the dlimiters.

1. FIELDS TERMINATED BY ('|',',','^')
2. FIELDS TERMINATED BY '|',',','^'

FIELDS TERMINATED BY '|,^' results in no delimiter identified thus the entire line is read as one token.

In absence of some undocumented feature that I am not aware of, you may either have to create a controlfile per delimiter or write some batch/Perl routine to clean up the incoming files.

I know sqlldr is a pretty good tool but I don't think it's that smart to dynamically use a correct delimiter according to the flat file.

Regards

/Rauf

--
Posted via http://dbforums.com