SQL*Loader Problem - NULLs and double quotes

I'm having two problems with SQL*Loader.

1. Quotes The data is in Tab delimited format and sometimes contains double quotes. When I load the data, I get extra quotes in data. For example:

Input data -
8" x 11" photo

Data in Oracle table -
"8"" x 11"" photo"

I loaded the data using a control file like this one: LOAD DATA
INFILE bullet.dat
INTO TABLE HOSWEB.STAGEBULLET
FIELDS TERMINATED BY X'09' TRAILING NULLCOLS
(SKU,

ProductName,
BulletDescription)

I tried changing the control file to this, but got unexpected results: LOAD DATA
INFILE bullet.dat
INTO TABLE HOSWEB.STAGEBULLET
FIELDS TERMINATED BY X'09' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(SKU,

ProductName,
BulletDescription)

That corrected the quote problem but caused problem number 2 (below).

2. NULLs
The data in the file contains some NULL values. For example (<TAB> added for clarity to represent tabs in data):
SKU<TAB>ProductName<TAB>BulletDescription JERAUTANA-H<TAB>Anaheim Angels Authentic Home Jersey 100%<TAB>heavyweight polyester double-knit jersey JERAUTANA-H<TAB><TAB>Official team logos and markings

When I load this data "Official team logos and markings" unexpectedly ends up in the ProductName field.

Any idea what gives? I'm having one heck of a time trying to trouble shoot this, since each change seems to break something somewhere else.

TIA! Russ
rconway_at_NOSPAMhallofsports.com (remove NOSPAM to reply) Received on Tue May 15 2001 - 18:32:31 CDT