Re: SQL*Loader

From: Jared Still <jkstill_at_bcbso.com>
Date: Fri, 30 Jun 2000 12:16:44 -0700 (PDT)
Message-Id: <10544.110994@fatcity.com>

Dick,

Here's a perl script I wrote several years ago for this very purpose.

I mention it's age because it is rather ugly and Perl was rather new to me at that time. :)

But it does work.

Jared

#!/usr/local/bin/perl

# rmcol
# removes columns from comma delimited file

$[=1;

sub usage {

	print "$0: remove columns from comma delimited file\n";
	print "$0: [filename] [startpoint] [# elements to delete]\n\n";
	print "    ( startpoint is 1 based )\n";
	print "    example:\n";
	print "    if file x contains 'A','B','C','D':\n";
	print "    rmcol x 2 2 will change x to 'A','D'\n";
	exit 1;

}

$ColFile = shift || &usage;
-f $ColFile || &usage;

$StartPoint = shift || &usage;
$Length = shift || &usage;

open(COL,"<$ColFile") || die "cannot open $ColFile - $! \n"; open(COLNEW,">${ColFile}.tmp") || "cannot create ${ColFile}.tmp - $! \n";

while(<COL>){

	chop;
	split(/,/,$_);
	splice(@_,$StartPoint,$Length);
	print COLNEW join(',',@_),"\n";

}

close(COL);
close(COLNEW);

rename($ColFile,"${ColFile}.bak") || die "cannot rename output ${ColFile} - $! \n"; rename("${ColFile}.tmp",${ColFile}) || die "cannot rename output ${ColFile}.tmp - $! \n";

##############################3

On Fri, 30 Jun 2000 dgoulet_at_vicr.com wrote:

> I hope somebody may have an idea on this. I've already done the RTFM thing

> and experimenting with no luck.
> 


>     My problem is that we have an automatic tester that generates ASCII comma

> delimited text files of it's results to be loaded to the database.  One of these
> files has a column in it that is no longer wanted in the database.  Regrettably
> it's going to be some time before the test engineering folks get around to
> removing it from the file, but the engineers want it out like yesterday and
> wouldn't you know it's right in the middle of each line.  The headache is that
> nothing in this results file is fixed in size.  The records are from 60 to 80
> characters long, with trailing nullcols, and the fields therein vary from test
> to test.  Even this particular column varies from 1 to 5 characters.
> 


>     Anyone out there got any idea on how to tell SQL*Loader to ignore this

> field???  My other alternative if Pro*C, but that may take a while too.
> 
> Thanks & Happy 4th for those of you in the States.
> 
> Dick Goulet
> -- 
> Author: 
>   INET: dgoulet_at_vicr.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).
> 

Jared Still
Certified Oracle DBA and Part Time Perl Evangelist ;-) Regence BlueCross BlueShield of Oregon Received on Fri Jun 30 2000 - 14:16:44 CDT