Re: SQL-Loader: Record Separaters...

A few years ago we did something similar to this. How we got around the CR LF in the column was to translate the field when we spooled it to a flat file. And then we re-translated it after we did the load.

In SQL*Plus, Something like

spool data.out
select col1 || ',' || col2 || ',' || translate (col3, chr(10), '~') || ',' || col4 ...
from table1;
spool off

Then in SQL*loader, load comma delimited file

Then later
update imported_data_table
set col3 = translate (col3, '~', chr(10));

Babette Turner-Underwood, TMI Communications babettet_at_tmi.ca.NOSPAM (remove appropriate part to e-mail me)

Lonn Tite wrote in message <366FF85A.61992168_at_serviceware.ca>...
>I am looking for information on how I can set an end of record indicator
>
>in the control file when loading data into a 7.x database.
>
>This very large data file (340MB) is tab delimited.
>
>There are no quotes around string values and some values contain comma's
>
>within them.
>
>My dilemma is that a field within each record contains multi-line text
>using carriage returns. I would like to set an end of record indicator
>(~) in the control file if possible.
>
>By default SQL-Loader uses carriage returns as record separaters. Seeing
>that carriage returns are being used within a field's value is there a
>way to override the default by using a special character and defining
>this character in the control file as the record separater?
>
>I have tried the following and am receiving a syntax error.
>
>ex.
>Load data
>Infile *
>Into Table Emp
>Insert
>Fields Terminated by X'09' (Tabs)
>(
> empno,
> empname,
> empnotes
> record terminated by X'7E' or ... by '~' ??????
>)
>
>Any suggestions would be greatly appreciated.
>
>Lonn Tite
>ltite_at_serviceware.ca
>
>
Received on Thu Dec 10 1998 - 15:58:50 CST