SQL*Loader and fields with <cr>

From: Rick Osterberg <osterber_at_fas.harvard.edu>
Date: 12 Aug 1998 03:48:10 GMT
Message-ID: <6qr39q$vj3$1_at_news.fas.harvard.edu>

Strange problem here. I'm porting a database from mSQL to Oracle on Unix. I exported the mSQL tables to ASCII files that are delimited and quoted. But some fields have embedded <cr>s within them. So my ASCII output file looks like:

"1","this is one record","more data"
"2","multi

line record here","even more data"
"3","some of them

may span three
lines","still more data"

etc... Where the line breaks within a field, there's a Unix '\r\n', and there's just a regular '\n' along at the end of each field. I'm trying to load these up into an Oracle table with SQL*Loader. But I can't get Loader to recognize the multi-lines as a single record. I tried

CONTINUEIF LAST = X'0A' (Hex OA is the <cr> character.)

But Loader said that I couldn't use a whitespace character. I also tried

CONTINUEIF NEXT (1) != '"' (I.e., if the next line line starts with something besides ", then it's part of the same record.) But that doesn't work either. That somehow corrupts many of the fields.

Any ideas? Much thanks!

-Rick

--
+--------------------------------------------------------------------------+
|                 Rick Osterberg   osterber_at_fas.harvard.edu                |
+--------------------------------------------------------------------------+

Received on Wed Aug 12 1998 - 05:48:10 CEST