时间戳

首页 > 解决方案 > 由于换行符和包围字符，sql loader 没有加载所有需要的行

sql-loader - 由于换行符和包围字符，sql loader 没有加载所有需要的行

问题描述

我对 SQL 加载器如何管理列值的结尾有疑问。我希望管理 CR LF、包围字符和分隔符，但似乎我找不到解决方案！

我从 .csv 文件收到的数据如下所示：

"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"

我的ctl文件如下：

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)

这是我在控制文件中尝试的内容：

CONTINUEIF LAST != '"'
CONTINUEIF THIS PRESERVE (1:2) != '",'
"str X'220D0A'"

这是我目前使用 "CONTINUEIF LAST != '"' 得到的结果

Record 2: Rejected - Error on table TOLCONDITION, column DESCRIPTION.
second enclosure string not present
Record 3: Rejected - Error on table TOLCONDITION, column C.
no terminator found after TERMINATED and ENCLOSED field

Table 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.

有什么方法可以管理 SQL Loader 中的换行符和包围字符？我不明白为什么我们不能改变它看到行的方式。当存在 CR LF 时，我们可以告诉它连接值，而不是看到新行，直到看到最后一个包围字符（在我的情况下为 chr34）+ 分隔符（在我的情况下）。

我真的希望找到一种无需更改 .csv 文件即可解决此问题的方法。

谢谢

标签： sql-loader

解决方案

如果您使用的是 12c，则可以使用“FIELDS CSV WITH EMBEDDED”子句来告诉sqlldr某些列嵌入了数据文件的行尾字符。这将导致在数据文件中插入列。更多信息在这里

Load Data
infile 'C:\2020-07-29-03-04-48-TolCondition.csv'
into table TolCondition
REPLACE
FIELDS CSV WITH EMBEDDED TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

推荐阅读