Re: Newbie control file question

From: ddf <oratune_at_msn.com>
Date: Tue, 1 Sep 2009 09:29:31 -0700 (PDT)
Message-ID: <a4357466-2f8f-417d-a9c5-1e0e2ee303e0_at_d21g2000vbm.googlegroups.com>

On Sep 1, 10:46 am, Sashi <small..._at_gmail.com> wrote:
> All, I'm trying to load data using a control file.
> Here's the sqlldr version:
> SQL*Loader: Release 10.2.0.3.0 - Production on Tue Sep 1 11:43:14 2009
>
> Copyright (c) 1982, 2005, Oracle.  All rights reserved.
>
> Here's a sample data line:
> "941 -
> Warren","06/08/09","00:08","9085635152","9733928015","4","54","002","NEWARK    ,
> NJ","NJ","Toll",".18","0000019619"
>
> Here's my control file:
>
> LOAD DATA
> INFILE '/my/source/file.csv'
> INTO TABLE dest_table
> FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
> (
> SWITCH_NAME,
> CONNECT_DATE "MM/DD/YY",
> CONNECT_TIME,
> SRC_NUMBER,
> DEST_NUMBER,
> DUR_MM,
> DUR_SS,
> TRUNK,
> LOCATION,
> STATE,
> CALL_TYPE,
> COST,
> EXP_CODE
> )
>
> It's being rejected thus:
> Record 1: Rejected - Error on table CDR_TEMP_FEED, column
> CONNECT_DATE.
>
> How do I fix this?
>
> TIA,
> Sashi

By changing your .ctl file:

LOAD DATA
INFILE '/my/source/file.csv'
INTO TABLE dest_table
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS (
SWITCH_NAME,
CONNECT_DATE "to_date(:connect_date, 'MM/DD/YY')", CONNECT_TIME,
SRC_NUMBER,
DEST_NUMBER,
DUR_MM,
DUR_SS,
TRUNK,
LOCATION,
STATE,
CALL_TYPE,
COST,
EXP_CODE
)

SQL*Loader: Release 10.2.0.3.0 - Production on Tue Sep 1 11:26:37 2009

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 1

SQL>
SQL> select *
  2 from dest_table;

SWITCH_NAME

---

CONNECT_DATE CONNECT_TI SRC_NUMBER DEST_NUMBE DUR_MM DUR_SS

-------------------- ---------- ---------- ---------- ----------
----------
TRUNK  LOCATION                                 ST CALL_TYPE

COST

------ ---------------------------------------- -- ----------

---

EXP_CODE

---

941 - Warren

08-JUN-2009 00:00:00 00:08      9085635152 9733928015
4         54
002    NEWARK    , NJ                           NJ Toll              .

18
0000019619

SQL> David Fitzjarrell Received on Tue Sep 01 2009 - 11:29:31 CDT