I have a problem with how SQL loader manage the end of a column value. I was hoping to manage CR LF, the enclosement character and the separator character but it seems I can't find a solution!
The data I receive from the .csv file looks like this:
"C","I","FLAGS","LASTUPDATEDATE","BOEVERSION","C_OSUSER_UPDATEDBY","I_OSUSER_UPDATEDBY","C_OSUSER_PWF","DESCRIPTION","DURATION","ENDDATE","I_OSUSER_PWF","LASTSTATUSCHA","STARTDATE","DURATIONUNIT","TYPE","STATUS","C_BNFTRGHT_CONDITIONS","I_BNFTRGHT_CONDITIONS","C_CNTRCT1_CONDITION","I_CNTRCT1_CONDITION","EXTBLOCKTYPE","EXTBLOCKDURATIONUNIT","EXTBLOCKDURATION","EXTBLOCKDESCRIPTION","PARTITIONID"
"7680","423","PE","2015-07-06 11:42:10","0","1000","1506","","No benefits are payable for a Total Disability period during a Parental or Family-Related Leave, for a Total Disability occurring during this period.
","0","","","","","69280000","69312015","71328000","7285","402","","","","","","","1"
"7680","426","PE","2015-07-06 11:42:10","0","1000","1506","","""Means to be admitted to a Hospital as an in-patient for more than 18 consecutive hours.
""
","0","","","","","69280000","69312021","71328000","7285","402","","","","","","","1"My ctl file is as follows:
Load Data
infile 'C:\2020-07-29-03-04-48-TolCondition.csv'
CONTINUEIF LAST != '"'
into table TolCondition
REPLACE
FIELDS TERMINATED BY "," ENCLOSED by '"'
(
C,
I,
FLAGS,
LASTUPDATEDATE DATE "YYYY-MM-DD HH24:MI:SS",
BOEVERSION,
C_OSUSER_UPDATEDBY,
I_OSUSER_UPDATEDBY,
C_OSUSER_PWF,
DESCRIPTION CHAR(1000),
DURATION,
ENDDATE DATE "YYYY-MM-DD HH24:MI:SS",
I_OSUSER_PWF,
LASTSTATUSCHA DATE "YYYY-MM-DD HH24:MI:SS",
STARTDATE DATE "YYYY-MM-DD HH24:MI:SS",
DURATIONUNIT,
TYPE,
STATUS,
C_BNFTRGHT_CONDITIONS,
I_BNFTRGHT_CONDITIONS,
C_CNTRCT1_CONDITION,
I_CNTRCT1_CONDITION,
EXTBLOCKTYPE,
EXTBLOCKDURATIONUNIT,
EXTBLOCKDURATION,
EXTBLOCKDESCRIPTION,
PARTITIONID)
Here is what I tried in the control file:
CONTINUEIF LAST != '"'
CONTINUEIF THIS PRESERVE (1:2) != '",'
"str X'220D0A'"
Here is the result I currently have with "CONTINUEIF LAST != '"'
Record 2: Rejected - Error on table FNA_FNTFO2.TOLCONDITION, column DESCRIPTION.
second enclosure string not present
Record 3: Rejected - Error on table FNA_FNTFO2.TOLCONDITION, column C.
no terminator found after TERMINATED and ENCLOSED field
Table FNA_FNTFO2.TOLCONDITION:
1 Row successfully loaded.
2 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Is there any way to manage line break and enclosement character in SQL Loader? I dont understand why we can`t change how it sees rows. Instead of seeing a new row when there is a CR LF, can we tell it to concacenate values until the last enclosement character (chr34 in my case) + the separator character (y, in my case) has been seen.
I really hope to find a way to resolve this issue without having to change the .csv file.
Thank you
I think you're going to have to preprocess the CSV in some way sadly.
If I've understood your file correctly, you have two double quotes on their own line. These should be part of the description a couple of lines above.
So checking the last character is something other than quotes doesn't work, because this is the last non-blank character on the line with the two quotes.
THIS checks the current record to decide whether to add the next line to the record. There's nothing in the data to mark which is the beginning of a new record.
But even if there was, there are several blank lines in the description of the second record, so the THIS condition would be false at some point working through these so SQL*Loader considers it a new record.
Trying to continue when the first two characters of the NEXT line are something other than quote-comma also doesn't work. New records start with quote-notcomma, so this would try and concatenate all lines except the end of descriptions.
So I think your options are:
- Preprocess the CSV, so there are no lines with only double-quotes on them. This allows you to use CONTINUEIF LAST != '"'
- Read the file as an external table, loading each line as a separate row. Then using SQL or PL/SQL to glue the rows together and extract the columns as needed