Re: SQL*Loader Problem - NULLs and double quotes

"Daniel A. Morgan" wrote:

> As in the following example use OPTIONALLY ENCLOSED BY '"'
>
> LOAD DATA
> INFILE *
> INTO TABLE dept
> FIELDS TERMINATED BY ','
> OPTIONALLY ENCLOSED BY '"'
> (deptno, dname, loc)
> BEGINDATA
> 12,RESEARCH,"SARATOGA"
> 10,"ACCOUNTING",CLOVELAND
> 11,"ART",SALEM
> AA,FINANCE,"BOSTON"
> 21,"SALES",ROCHESTER
> 42,"INT'L","SAN FRANCISCO"
>
> Daniel A. Morgan

The problem is that the quotes are being *added* to the field by SQL*Loader. I start with data that looks like this...
SKU<TAB>ProductName<TAB>BulletDescription JERAUTANA-H<TAB>Anaheim Angels Authentic Home Jersey<TAB>8" x 11" photo

When the data is loaded, I get the BulletDescription with quotes around it like this:
"8"" x 11"" photo"

I don't want the quotes. Currently, I have to run the import and then run a script to search for and remove the bad double quotes.. How do I stop that from happening? The fields are not enclosed in quotes in the tab delimited file.

In addition, adding OPTIONALLY ENCLOSED BY '"' caused the second problem with NULL fields (below).

Please help!!!

> 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.
>
Received on Wed May 16 2001 - 12:40:18 CDT