Re: SQL*Loader Problem - NULLs and double quotes

Russ Conway wrote:

> 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)

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