SQL*LOADER CSV file problem

From: Jason Selby <jselby_at_cams.co.uk>
Date: Thu, 23 Apr 1998 16:46:44 +0100
Message-ID: <353F6264.642B202A@cams.co.uk>

Hi

I've got a problem, the following record is not loaded into the table when using SQL*LOADER :
"1813.","""Bumping"" of
heart","","","","00","","00","","00","**********","0","EN"

The control file is as follows

LOAD DATA

INFILE 'c:\readcode\corev2.1'
INFILE 'c:\readcode\corev2.2'
INFILE 'c:\readcode\corev2.3'
INFILE 'c:\readcode\corev2.4'
INFILE 'c:\readcode\corev2.5'
INFILE 'c:\readcode\corev2.6'
INFILE 'c:\readcode\corev2.7'
INFILE 'c:\readcode\std9v2.rc'

INSERT
INTO TABLE read_cross_ref_new
(READ_CODE TERMINATED BY ',' ENCLOSED BY '"', TERM TERMINATED BY ',' ENCLOSED BY '"',

TERM_60 TERMINATED BY ',' ENCLOSED BY '"',
TERM_198 TERMINATED BY ',' ENCLOSED BY '"',
ICD9_CODE TERMINATED BY ',' ENCLOSED BY '"',
ICD9_CODE_DEF TERMINATED BY ',' ENCLOSED BY '"',
ICD9_CM_CODE TERMINATED BY ',' ENCLOSED BY '"',
ICD9_CM_CODE_DEF TERMINATED BY ',' ENCLOSED BY '"',
OPCS_CODE TERMINATED BY ',' ENCLOSED BY '"',
OPCS_CODE_DEF TERMINATED BY ',' ENCLOSED BY '"',

SPECIALTY_FLAGS TERMINATED BY ',' ENCLOSED BY '"', STATUS_FLAG TERMINATED BY ',' ENCLOSED BY '"', LANGUAGE_CODE TERMINATED BY WHITESPACE ENCLOSED BY '"') The problem seems to occur when the field TERM begins with 2 double quotes although this is allowed in the normal CSV file spec. Is there a workaround or should I convert it to tab delimited or something other than CSV.

TIA Jason Received on Thu Apr 23 1998 - 10:46:44 CDT