Re: SQL*Loader with carriage returns in file

We had exactly the same problem, you are not goingto like it. I had to write a C program which would remove the extra carriage returns, I could not find any other way.
Tom
Kyle G. Miller <kmiller_at_spencergrace.com> wrote in message news:38d2b646.0_at_csoux1102...
> I am using Data Loader to input data into a table. I have a
VARCHAR2(4000)
> field that contains text that includes carriage returns. When I try to
run
> 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?
>
> I got a reply from the "ask an expert" section on oracle's site, but
his/her
> 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,
> but it usually takes 3 days for an answer and I need this Monday AM. I
see
> what he is trying to do with the "str '|'" appended to the INFILE, but
when
> I run the load, it just hangs, and the SQLLOAD80 process hangs my NT
system
> at 100% utilization.
>
> Please help.
>
> Thanks.
>
> Kyle Miller
>
> Here is my control file:
> LOAD DATA
> INFILE 'c:\hargrovedata\realdataWithCR.csv'
> INTO TABLE big
> 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 Mon Mar 20 2000 - 07:44:56 CET