Re: Help - SQLLoader and Space-Delimited Files

From: Ludo Van Helleputte <Ludo.van.helleputte_at_be.pwcglobal.com>
Date: Sat, 5 May 2001 21:06:17 +0200
Message-ID: <3af451e8_3_at_news2.prserv.net>


Can't you use fixed length columns ?? Try another way ... or upload it in a single column of a temp table and use SQL functions (instr, ...) to split-up ...

"The Nomad" <nobody_at_nowhere.com> wrote in message news:8u_H6.145508$fs3.23201511_at_typhoon.tampabay.rr.com...
> Hey all,
>
> I have a space-delimited file (I cannot control the format - it is kicked
> out by another tool for which I have no source). Some of the columns will
 be
> null. The SQL Loader seems to treat multiple spaces as a single space.
 Here
> is the sample information:
>
> Loader Control File
>
> load data
> infile 'datatest.dat'
> append
> into table testdata
> fields terminated by ' ' optionally enclosed by '"'
> ( CHANNEL,
> PACKAGE,
> PRODUCT,
> MARKET,
> YEAR,
> SCENARIO,
> SALES,
> COGS,
> MARGIN,
> MARKETING,
> FREIGHT,
> DISCOUNTS,
> TOTALEXPENSES,
> PROFIT,
> OPENINGINVENTORY,
> ADDITIONS,
> ENDINGINVENTORY,
> PROFITPCT,
> PRODUCTSHARE)
>
> DataTest
>
> ...snip...
> "Retail" "2 Liter" "ProdA" "Manhattan" "Feb" "Budget" 0 0 0 0 0 0 0 0 0 0
 0
> 0
> "Retail" "2 Liter" "ProdA" "Manhattan" "Mar" "Budget" 0 0 0 0 0 0 0 0 0 0
 0
> 0
> "Retail" "2 Liter" "ProdA" "Manhattan" "Qtr1" "Budget" 10 3 7 2 0 1 3 4 -2
> 12 0 40 11.11111111111111
> "Retail" "2 Liter" "ProdA" "Manhattan" "Apr" "Budget" 0 0 0 0 0 0 0 0 0 26
> 26 0
> "Retail" "2 Liter" "ProdA" "Manhattan" "May" "Budget" 10 3 7 2 0 1 3 4 26
 36
> 52 40 12.5
> ...snip...
>
> It chokes on the first, second and 4th lines. On those lines, there is a
> null value for PROFITPCT. If I change the delimiter in this sample input
> file from space to a comma, then everything imports properly. I've tried
> using the TRAILING NULLCOLS and specifying a terminator/enclosed-by
> statements on each line, but then what happens is it imports the last 0
 into
> the PROFITPCT column and nulls out PRODUCTSHARE.
>
> I can't just replace all occurrences of space to comma because
> 1) There are spaces in the data columns inside double-quotes
> 2) There are hundreds of thousands of rows of data - too many to hand-job.
>
>
> What am I missing? I've been over and over the SQL Loader portion of the
> utilities guide with no success. Thanks for any help you can provide.
>
> Marc
>
>
>
>
>
Received on Sat May 05 2001 - 21:06:17 CEST

Original text of this message