| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: SQLLDR Question
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 <jkstill_at_cybcon.com> on 05/23/2001 11:16:00 PM
Please respond to ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> 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:
The following Perl script will do it
Jared
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-LReceived on Thu May 24 2001 - 09:49:21 CDT
(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: Chaim.Katz_at_Completions.Bombardier.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).
 |
 |