Help - SQLLoader and Space-Delimited Files

[Quoted] 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 [Quoted] null. The SQL Loader seems to treat multiple spaces as a single space. Here [Quoted] 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
[Quoted] "Retail" "2 Liter" "ProdA" "Manhattan" "Mar" "Budget" 0 0 0 0 0 0 0 0 0 0 0
0
[Quoted] "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...

[Quoted] 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 [Quoted] 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 Wed May 02 2001 - 22:54:28 CEST