SQLLDR-在终止和封闭的字段后未找到终止符 [英] Sqlldr- No terminator found after terminated and enclosed field

问题描述

我使用Oracle 11g.
我的数据文件如下所示:

I use Oracle 11g.
My data file looks like below:

1 |"\ a \ ab \" |不要"清洁针" |"@"

1|"\a\ab\"|"do not "clean" needles"|"@"

2 |"\ b \ bg \" |墙" 69端对端" |"@"

2|"\b\bg\"|"wall "69" side to end"|"@"

我的控制文件是:

load data
infile 'short.txt'
CONTINUEIF LAST <> '"'
into table "PORTAL"."US_FULL" 
fields terminated by "|" OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
  u_hlevel,
  u_fullname NULLIF u_fullname=BLANKS,
  u_name char(2000)  NULLIF c_name=BLANKS ,
  u_no NULLIF u_no=BLANKS
)

通过sqlldr加载数据时，将创建一个.bad文件，并且.log文件包含错误消息，指出在终止并包含字段后未找到终止符"

While loading data through sqlldr, a .bad file is created and .log file contains error message stating "No terminator found after terminated and enclosed field"

我的数据中没有双引号的开头和结尾，但是像上面的示例中的clean和69一样，我需要在数据中加上双引号.例如:加载后我的数据文件应如下所示:

Double quotes starting and ending are not in my data, however I would need double quotes withing the data like in above example surrounding clean and 69. Ex: My data file after loading should look like:

1，\ a \ ab \，请勿清洁"针头，@

1, \a\ab\, do not "clean" needles, @

2，\ b \ bg \，将"69"端对端壁，@

2, \b\bg\ , wall "69" side to end , @

如何做到这一点?

推荐答案

由于字段被双引号引起来，所以您要保留的双引号需要通过在前面加上另一个双引号来转义，这很担心像这样:

I'm afraid since the fields are surrounded by double-quotes the double-quotes you want to preserve need to be escaped by adding another double-quote in front like this:

1|"\a\ab\"|"do not ""clean"" needles"|"@"

或者，如果您可以在字段不被双引号引起来的情况下获取数据，这也将起作用:

Alternately if you can get the data without the fields being surrounded by double-quotes, this would work too:

1|\a\ab\|do not "clean" needles|@

如果无法让数据提供者根据需要格式化数据(即，在提取到文件之前搜索双引号并替换为2个双引号)，则必须对文件进行预处理以进行设置用双引号将这些方法之一加起来，以便数据将按预期加载.

If you can't get the data provider to format the data as needed (i.e. search for double-quotes and replace with 2 double-quotes before extracting to the file), you will have to pre-process the file to set up double quotes one of these ways so the data will load as you expect.

这篇关于SQLLDR-在终止和封闭的字段后未找到终止符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！