RE: SQLLDR Question

Since those are the last two fields in the data file, I think you can use TERMINATED BY WHITESPACE on the field definition. Something like:

        trans_date date(14) "mm/dd/yy,hh:mi" terminated by whitespace

> -----Original Message-----
> From: srcdco [mailto:srcdco_at_ritvax.isc.rit.edu]
> Sent: Wednesday, May 23, 2001 4:41 PM
> To: ORACLE-L
> Cc: srcdco
> Subject: SQLLDR Question
>
>
> I am trying to load a file that has the fields comma-delimited,
> variable length. A sample line from the file looks like this:
>
> 503,414081660,Success,Auth,AH9042,z,4555,-,-,-,0,05/01/01,14:21
>
> The problem I am having is putting the date and time together. The
> control file looks like this:
>
> load data
> infile 'cyber_real.dat'
> append
> into table rit_cyber_hist
> when order_id <> 'Order ID' and order_id <> ''
> fields terminated by ','
> (order_id,
> trans_nbr,
> cyber_status,
> trans_type,
> auth_code,
> avs_code,
> trans_amt,
> cic_resp,
> po_nbr,
> ship_to_zip,
> tax_amt,
> trans_date date(14) "mm/dd/yy,hh:mi")
>
> It ignores the time portion of the file, I presume because it has a
> comma before it and it assumes that it is a different field. I can't
> figure out any way to get this loaded with the trans_date field
> containing both the date and time.
>
> This is on Oracle 8.1.6.0 on Sun Solaris.
>
> Any suggestions? Thank you.
>
> --
> Scott Canaan (srcdco_at_rit.edu)
> (716) 475-7886
> "Life is like a sewer, what you get out of it depends on what you put
> into it" - Tom Lehrer
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Scott Canaan
> INET: srcdco_at_ritvax.isc.rit.edu
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
Received on Thu May 24 2001 - 08:00:14 CDT