Re: SQL*Loader can a comma delimited file be imported and skip selected fields?

A copy of this was sent to "Jeff Boehlert" <boehlert_at_peak.org> (if that email address didn't require changing) On Fri, 28 May 1999 01:56:44 -0700, you wrote:

>Thank you Tom. I was sure someone had come up with some clever way to do
>this. I am not working in a Unix environmnet so I have opted to try your
>PL/SQL approach. As mentioned my file is comma delimited, optionally
>enclosed with quotes. When I try my file with this approach the parsing
>appears to have a few problems. See details here:
>
>LOAD DATA
>INFILE *
>REPLACE
>INTO TABLE DELIMITED_TEST
>(
> FIELD1 position(1:4096) "delimited.word(:field1,1,chr(34),chr(44))",
> FIELD2 position(1:1) "delimited.word(:field1,4,chr(34),chr(44))"
>)
>BEGINDATA
>John Jones,"Elm St",Junk,123 Main Street
>Jeff Boehlert,abcdef," 5555","po box, 1848, abc"
>

[snip]

>Fields having leading/trailing characters stripped??
>
>Thanx for your help, once I have this tuned it will be a great help. It sure would be nice if loader allowed us to map an inbound column to the bit bucket!!
>

It does in release 8.1... Keyword FILLER maps input data fields to the bit bucket.

sorry, i must have posted an older version of my package. Here is the correct one (that works correctly :)

create or replace package delimited
as

	function word( p_str 			in varchar2, 
				   p_n   			in varchar2, 
				   p_enclosed_by 	in varchar2 default '''', 
				   p_separated_by   in varchar2 default ',' )
	return varchar2;

	pragma restrict_references( word, WNDS, RNDS );

create or replace package body delimited as

        type vcArray is table of varchar2(2000) index by binary_integer;

	g_words			vcArray;
	g_empty			vcArray;
	g_last_string	varchar2(4096);

	function de_quote( p_str in varchar2, p_enc_by in varchar2 ) return varchar2
	is
	begin
		return replace( ltrim( rtrim( p_str, p_enc_by ), p_enc_by ), 
							p_enc_by||p_enc_by, p_enc_by );
	end de_quote;


	procedure parse( p_str 	 in varchar2, 
					 p_delim in varchar2, 
					 p_sep   in varchar2 )
	is
		l_n			number  default 1;
		l_in_quote	boolean default FALSE;
		l_ch		char(1);
		l_len		number default nvl(length( p_str ),0);
	begin
		if ( l_len = 0 ) then
			return;
		end if;

		g_words := g_empty;
		g_words(1) := NULL;

		for i in 1 .. l_len loop
			l_ch := substr( p_str, i, 1 );
			if ( l_ch = p_delim ) then
				l_in_quote := NOT l_in_quote;
			end if;
			if ( l_ch = p_sep AND NOT l_in_quote ) then
				l_n := l_n + 1;
				g_words(l_n) := NULL;
			else
				g_words(l_n) := g_words(l_n)||l_ch;
			end if;
		end loop;

		for i in 1 .. l_n loop
			g_words(i) := de_quote( g_words(i), p_delim );
		end loop;
	end parse;


	function word( p_str 			in varchar2, 
				   p_n   			in varchar2, 
				   p_enclosed_by 	in varchar2 default '''', 
				   p_separated_by   in varchar2 default ',' ) return varchar2
	is
	begin
		if ( g_last_string is NULL or p_str <> g_last_string ) then
			g_last_string := p_str;
			parse( p_str, p_enclosed_by, p_separated_by );
		end if;
		return g_words( p_n );
	exception
		when no_data_found then return NULL;
	end;

end delimited;
/

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

-- 
Opinions are mine and do not necessarily reflect those of Oracle Corporation