Help - SQLLoader and Space-Delimited Files

[The Nomad]

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

[Ludo Van Helleputte]

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" <nob...@nowhere.com> wrote in message
news:8u_H6.145508$fs3.23...@typhoon.tampabay.rr.com...