sqlldr question
From: Veeru71 <m_adavi_at_hotmail.com>
Date: Wed, 2 Jul 2008 04:12:05 -0700 (PDT)
Message-ID: <4b015425-45d9-4e14-b40e-2c4e42234b4d@g16g2000pri.googlegroups.com>
load data
infile 'test.unl'
preserve blanks
into table TEST
fields terminated by '|'
trailing nullcols
(
ID,
NAME CHAR(30) "DECODE(:NAME, ' ', ' ', '\\\ ', ' ', RTRIM(:NAME))" )
Date: Wed, 2 Jul 2008 04:12:05 -0700 (PDT)
Message-ID: <4b015425-45d9-4e14-b40e-2c4e42234b4d@g16g2000pri.googlegroups.com>
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,
- if the data contains 1 or more spaces (eg. 1st & 2nd lines), it should be loaded as a single space.
- If the data contains '\ ' (i.e, backslash followed by a single space, eg. 3rd line) it should be loaded as a single space
- 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
Received on Wed Jul 02 2008 - 06:12:05 CDT