You can combine two data fields into one column with sqlldr, but I think both
fields have to be in the table.
Example:
SQL> desc x
Name Type
--------------------- ------------------------------------
TRANS_DATE DATE
F1 CHAR(8)
LOAD DATA
INFILE *
APPEND
INTO TABLE x
FIELDS TERMINATED BY ','
(f1,
trans_date "to_date(:f1||' '||:trans_date,'mm/dd/yy hh24:mi')"
)
BEGINDATA
05/01/01,14:21
05/24/01,9:57
(After the load, you can ALTER TABLE table DROP COLUMN column to get rid of the
f1 column,)
Jared Still <[EMAIL PROTECTED]> on 05/23/2001 11:16:00 PM
Please respond to [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc: (bcc: Chaim Katz/Completions/Bombardier)
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 ([EMAIL PROTECTED])
> (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
