Re: control file .ctl and control characters

spudlover2001_at_yahoo.com wrote:
> Hi all,
>
> I'm loading a data file into a temp table using SQL Loader. One of the
> columns is a date type field. However, in the data file there is
> sometimes a series of control characters like
> '' in the date fields. This is causing problems as all those
> records are getting bumped to the bad.file. I tried to use a nullif
> but that is not working either.
>
> Its okay if the field is blank because when i update the data from the
> temp table to the target update table i'll use the TRIM function.
>
> Here is the control file
>
> LOAD DATA
> INFILE 'C:\testfile.dat
> REPLACE
> INTO TABLE testtable
> (
> varcol1 POSITION(01:01),
> varcol2 POSITION(02:16),
> charcol1 POSITION(17:27),
> charcol2 POSITION(28:107),
> charcol3 POSITION(108:109),
> datecol1 POSITION(110:119) date nullif (datecol1 =
> '')
> )
>
>
> Any help would be greatly appreciated.
>
> AA

nullif (datecol1 = '')

Is never going to work as what you have posted are visual placeholders.

Use the ASCII function and SUBSTR to determine what they are and then use the CHR function in your SQL*Loader script to replace them with NULL.

Though my preferred solution would be, upstream, to insist on clean loadable files or use an operating system tool to replace them.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)