Re: SQLLDR Question

From: Jared Still <jkstill_at_cybcon.com>
Date: Wed, 23 May 2001 19:27:24 -0700
Message-ID: <F001.0030CD3B.20010523191600@fatcity.com>

Instead of trying to do this in SQL Loader, why not just try to clean up the data?

This can be done at least 2 methods:

1. Get the sender to send it to you properly
2. clean it up yourself.

The following Perl script will do it

Jared

---

#!/usr/bin/perl  

my $file="data.txt";
open(DATA,$file) || die "cannot open $file - $!\n";  

while(<DATA>){

        chomp;
        my @data = split(/,/);
        # append last field to penultimate field, remove last field
        $data[ $#data -1] .= "-" . $data[ $#data ];
        undef $data[ $#data ];
 
        #print it all out
        my $newData =  join(',',@data);
        # remove trailing comma
        chop $newData;
        print "$newData\n";

}

---

On Wednesday 23 May 2001 14:41, Scott Canaan wrote:
> 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: Jared Still
  INET: jkstill_at_cybcon.com

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 Wed May 23 2001 - 21:27:24 CDT