Re: sqlldr question

From: Ed Prochak <edprochak_at_gmail.com>
Date: Wed, 2 Jul 2008 05:40:59 -0700 (PDT)
Message-ID: <96d100ae-4fc8-4fd5-be4e-5e00ee05230e@27g2000hsf.googlegroups.com>


On Jul 2, 7:12 am, Veeru71 <m_ad..._at_hotmail.com> wrote:
> I have a table with 2 columns.....
>
> CREATE TABLE TEST ( ID NUMBER(5), NAME VARCHAR2(30))
>
> My data file looks like the following and '|' is the col delimiter
>
> 10| | -- single space
> 20| | -- 5 spaces
> 30|\ | -- back slash followed by a single space
> 40|| -- 0 spaces
> 50|JOHN | -- valid data with possible trailing spaces
>
> My requirement is...
> In the 'NAME' column,
>
> 1) if the data contains 1 or more spaces (eg. 1st & 2nd lines), it
> should be loaded as a single space.
> 2) If the data contains '\ ' (i.e, backslash followed by a single
> space, eg. 3rd line) it should be loaded as a single space
> 3) If there is no data (eg. 4th line), it should be loaded as NULL
> 4)Any other data should be right-trimmed (eg. 5 th line should be
> loaded as 'JOHN' without any trailing spaces).
>
> The following control file is working to some extent but it is unable
> to load the 2nd line (more than 1 space) properly. Multiple spaces
> are getting RTRIMmed to empty string and hence, a NULL is getting
> loaded.
>
> ctl file
> ======
> load data
> infile 'test.unl'
> preserve blanks
> into table TEST
> fields terminated by '|'
> trailing nullcols
> (
> ID,
> NAME CHAR(30) "DECODE(:NAME, ' ', ' ', '\\\ ', ' ', RTRIM(:NAME))"
> )
>
> Any thoughts ?
> Thanks in advance
> - Murty

consider trimming the name before the decode? Or would substring work? DECODE(SUBSTR(:NAME,1,2), ' ', ' ', '\\\ ', ' ', ' ', ' ', RTRIM(:NAME))
this assumes no left padding (i.e., no input has two leading spaces followed by nonspace characters.

Actually, I find it a LOT easier to load into a staging table then parse the data in PL/SQL. I have also done preprocessing (in PERL or other language with regular expressions) before loading. Now with external tables, you may not need to create a regular table for staging.

HTH,
  Ed Received on Wed Jul 02 2008 - 07:40:59 CDT

Original text of this message