SQL*Loader with carriage returns in file
Date: Fri, 17 Mar 2000 16:48:38 -0600
Message-ID: <38d2b646.0_at_csoux1102>
[Quoted] I am using Data Loader to input data into a table. I have a VARCHAR2(4000) [Quoted] field that contains text that includes carriage returns. When I try to run [Quoted] the load, it fails with the following message:
"Record 1: Rejected - Error on table BIG, column CATEGORY_ORDERING_RULES.
second enclosure string not present"
My control file and table defn is at the end of this message.
How can I use data loader to load data that has carriage returns in some of the fields?
[Quoted] I got a reply from the "ask an expert" section on oracle's site, but his/her [Quoted] reply (see bottom of this message) didn't work when I tried it, even with the exact code they used to test. I posted another request to ask an expert, [Quoted] but it usually takes 3 days for an answer and I need this Monday AM. I see [Quoted] what he is trying to do with the "str '|'" appended to the INFILE, but when [Quoted] I run the load, it just hangs, and the SQLLOAD80 process hangs my NT system [Quoted] at 100% utilization.
Please help.
Thanks.
Kyle Miller
Here is my control file:
LOAD DATA
INFILE 'c:\hargrovedata\realdataWithCR.csv'
INTO TABLE big
[Quoted] FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
trailing nullcols
(show_name, show_location_name, show_address1,
show_address2, show_city, show_state, show_zipcode, show_phone, show_fax, show_industry, show_start_date, show_end_date, registration_deadline_date, show_producer, vendor_name, vendor_small_image, vendor_long_description, vendor_address1, vendor_address2, vendor_city, vendor_state, vendor_zipcode, vendor_phone, vendor_phone2, vendor_fax, vendor_email_address, vendor_contact_name, vendor_acceptable_payment1, vendor_acceptable_payment2, vendor_acceptable_payment3, vendor_acceptable_payment4,vendor_acceptable_payment5, vendor_acceptable_payment6, vendor_discount_date, category_name,
category_ordering_rules CHAR(4000), ...many more fields)
The table defn is as follows:
CREATE TABLE big
(
show_name varchar2(60), show_location_name varchar2(255), show_address1 varchar2(30), show_address2 varchar2(30), show_city varchar2(30), show_state varchar2(10), show_zipcode varchar2(10), show_phone varchar2(16), show_fax varchar2(16), show_industry varchar2(35), show_start_date DATE, show_end_date DATE,
registration_deadline_date DATE,
show_producer varchar2(25),
vendor_name varchar2(50), vendor_small_image varchar2(25), vendor_long_description varchar2(255), vendor_address1 varchar2(40), vendor_address2 varchar2(40), vendor_city varchar2(40), vendor_state varchar2(10), vendor_zipcode varchar2(10), vendor_phone varchar2(16), vendor_phone2 varchar2(16), vendor_fax varchar2(16), vendor_email_address varchar2(50), vendor_contact_name varchar2(50), vendor_acceptable_payment1 varchar2(20), vendor_acceptable_payment2 varchar2(20), vendor_acceptable_payment3 varchar2(20), vendor_acceptable_payment4 varchar2(20), vendor_acceptable_payment5 varchar2(20), vendor_acceptable_payment6 varchar2(20), vendor_discount_date DATE,
category_name varchar2(255),
category_ordering_rules varchar2(4000), ...many more fields)
;
Here is one of the data records:
TS2000,Washington Convention Center,"900 9th Street, NW",B
Hall,Washington,DC,20001,000-000-0000,000-000-0000,Trade
Show,26-Jul-00,28-Jul-00,,NTP,"Hargrove, Inc",,,Number One
Hargrove
Drive,,Lanham,MD,20706,301-306-4627,,301-731-5438,customerse
rvice_at_hargroveinc.com,,visa,mc,amex,,,,10-Jul-00,Material
Handling,"Small package rates apply to shipments with a
total weight of 30 pounds or less.
Material Handling is billed by the hundredweight, or per
CWT, with a 2 CWT minimum.
When computing material handling costs, remember to round
up to the next hundred pounds. For example, a delivery tht
weighs 347 pounds will be billed at 4 CWT.
Each delivery to the dock constitutes a shipment, and is
billed accordingly.
Refer to the Freight Handling Hints provided in your manual
for more information about Material Handling.
Be sure to check the show information and show schedule
carefully to determine whether or not overtime surcharges
should be included in your material handling
estimate.",...many more fields
Reply (submitted on : 03/15/2000 11:39 PST )
Kyle,
What you can do is terminate each record with a special
character "|" for example, and then specify that character
in your control file. For example, I tested this with the
following control file...
load data
infile 'cr_test.dat' "str '|'"
into table dept
append
fields terminated by ',' optionally enclosed by '"'
( deptno, dname, loc )
Question Reopened (submitted on : 03/17/2000 13:19 PST )
when I add the "str'|'" to my control file, the data load
hangs, and I get no output. Can you let me know why it
just hangs now? Nothing changed except the addition of the
"str '|'" to the infile line of control file.
Received on Fri Mar 17 2000 - 23:48:38 CET