Re: Trim Leading Blanks With Sql Loader

From: Daniel Morgan <damorgan_at_exxesolutions.com>
Date: Tue, 08 Jul 2003 10:28:43 -0700
Message-ID: <3F0AFF4B.C18DD533@exxesolutions.com>

Val wrote:

> I am having a problem loading in records from a flat file where there
> are leading blanks. In our table those columns are defined as
> char(1). In the flat file the fields are coming in with 4 blank spaces
> and 1 char OR all blank spaces. I have tried the trim funtion and it
> doesnt seem work. Please help! Below is my control file with the
> input records...
>
> LOAD DATA
>
> INTO TABLE "test"
> TRUNCATE
> FIELDS TERMINATED BY '|'
> TRAILING NULLCOLS
> (A DATE 'DD-MM-YYYY' NULLIF CRT_DT=BLANKS,
> B,
> C,
> D NULLIF D=BLANKS,
> E,
> F,
> G,
> H,
> I,
> J,
> K,
> L,
> M,
> N,
> O,
> P,
> Q,
> R char(1) "decode(LTRIM(R),NULL,NULL,LTRIM(r))",
> S char(1) "decode(LTRIM(S) ,NULL,NULL)",
> T char(1) "decode(LTRIM(T),NULL,NULL)",
> U char(1) "decode(LTRIM(U),NULL,NULL)")
>
> 06-11-2003| 5| AT_BASE_EE| | | licdfe_at_choree.net| F| sam|
> Licye| 1234 Avvvehjem ST| | McVarlane| CA| 94111| | Y| | Y|
> Y| | Y
> 06-11-2003| 5| AT_BASE_EE| | | ALE00_at_yahoo.com| F| BOB Ling|
> Fong| 15 Nohrow Street| Apt. 123| New York| MI| 12345| | Y| |
> Y| | |

Two thoughts.

1. Columns should not be defined as CHAR in a table.
2. You can use functions in SQL Loader control files ... I'd suggest using TRIM().

Here's a simple example:

LOAD DATA
INFILE *
INSERT
INTO TABLE funcdemo
(
 LAST_NAME position(1:7) CHAR "UPPER(:LAST_NAME)",  FIRST_NAME position(8:15) CHAR "LOWER(:FIRST_NAME)" )
BEGINDATA
Locke Phil
Cline Jack

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Tue Jul 08 2003 - 12:28:43 CDT