oracle Sql 加载器 - 第二个附件字符串不存在
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21332278/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Sql loader - second enclosure string not present
提问by Vinod Chelladurai
I am loading a .csv file data into oracle table through sql loader. One of the fields has a new line character (CRLF) in its data and so, am getting the below error:
我正在通过 sql loader 将 .csv 文件数据加载到 oracle 表中。其中一个字段的数据中有一个换行符 (CRLF)，因此出现以下错误：
second enclosure string not present
第二个外壳字符串不存在
This is my control file
这是我的控制文件
load data
characterset UTF8
infile 'C:\Users\lab.csv'
truncate
into table test_labinal
fields terminated by ";" optionally enclosed by '"'
TRAILING NULLCOLS
(
STATEMENT_STATUS ,
MANDATORY_TASK ,
COMMENTS CHAR(9999) "SubStr(:Comments, 0, 1000)"
)
The field COMMENTS has a new line character in one of its records. Can any one suggest a solution for this.
字段 COMMENTS 在其记录之一中有一个换行符。任何人都可以为此提出解决方案。
Thanks
谢谢
采纳答案by Vinod Chelladurai
I found the best way to load the .csv files with fields containing newline and comma.Please run the macro over the .csv file and then load using sqlloader
我找到了加载包含换行符和逗号的字段的 .csv 文件的最佳方法。请在 .csv 文件上运行宏，然后使用 sqlloader 加载
Sub remove()
Dim row As Integer
Dim oxcel As Excel.Application
Dim wbk As Excel.Workbook
Set oxcel = New Excel.Application
Set wbk = oxcel.Workbooks.Open("filename.csv", 0, True)
row = 0
With oxcel
.ActiveSheet.Select
Do
row = row + 1
'Assume first column is PK and so checking for empty pk to find the number of rows
Loop Until IsEmpty(Cells(row, 1)) Or IsNull(Cells(row, 1))
Range(Cells(1, 24), Cells(row - 1, 24)).Select
For Each oneCell In Selection
oneCell.Value = Application.Substitute(Application.Substitute
(Application.Substitute (CStr(oneCell.Value), vbLf, vbCr), vbCr, "-"),",","-")
Next oneCell
End With
End Sub
It's running perfect for me.
它对我来说运行完美。
回答by Alex Poole
If your last field is always present (though trailing nullcolssuggests it isn't) and you have some control over the formatting, you can use the CONTINUEIFdirective to treat the second line as part of the same logical record.
如果您的最后一个字段始终存在（尽管trailing nullcols暗示它不是）并且您对格式有一些控制，则可以使用该CONTINUEIF指令将第二行视为同一逻辑记录的一部分。
If the commentsfield is always present and enclosed in double-quotes then you can do:
如果该comments字段始终存在并用双引号括起来，那么您可以执行以下操作：
...
truncate
continueif last != x'22'
into table ...
Which would handle data records like:
这将处理数据记录，如：
S;Y;"Test 1"
F;N;"Test 2"
P;Y;"Test with
new line"
P;N;""
Or if you always have a delimiter after the comments field, whether it is populated or not:
或者，如果您总是在评论字段后面有一个分隔符，无论是否填充：
...
truncate
continueif last != ';'
into table ...
Which would handle:
哪个将处理：
S;Y;Test 1;
F;N;"Test 2";
P;Y;Test with
new line;
P;N;;
Both ways will load the data as:
两种方式都会将数据加载为：
S M COMMENTS
- - ------------------------------
S Y Test 1
F N Test 2
P Y Test withnew line
P N
But this loses the new line from the data. To keep that you need the terminating field delimiter to be present, and instead of CONTINUEIFyou can change the record separator using the stream record format:
但这会丢失数据中的新行。为了保持这一点，您需要存在终止字段分隔符，而不是CONTINUEIF您可以使用流记录格式更改记录分隔符：
...
infile 'C:\Users\lab.csv' "str ';\n'"
truncate
into table ...
The "str ';\n'"defines the terminator as the combination of the field terminator and a new line character. Your split comment only has that combination on the final line. With the same data file as the previous version, this gives:
将"str ';\n'"终止符定义为字段终止符和换行符的组合。您的拆分评论仅在最后一行具有该组合。使用与以前版本相同的数据文件，这给出：
S M COMMENTS
- - ------------------------------
S Y Test 1
F N Test 2
P Y Test with
new line
P N
4 rows selected.
Since you're on Windows you might have to include \rin the format as well, e.g. "str ';\r\n'", but I'm not able to check that.
由于您使用的是 Windows，您可能还必须包含\r在格式中，例如"str ';\r\n'"，但我无法检查。
回答by LaShawnda Walker
load data
characterset UTF8
infile 'C:\Users\lab.csv'
truncate
into table test_labinal
fields terminated by ";" optionally enclosed by '"'
TRAILING NULLCOLS
(
STATEMENT_STATUS ,
MANDATORY_TASK ,
COMMENTS CHAR(9999) "SubStr(REPLACE(REPLACE(:Comments,CHR(13)),CHR(10)), 0, 1000)"
)
Note: The CHR(13)is the ASCII character for "carriage return" and the CHR(10)is the ASCII character for "new line". Using the Oracle PL/SQL REPLACEcommand without a replacement value will remove any "carriage return" and/or "new line" character that is embedded in your data. Which is probably the case because the comment field is the last field in your CSV file.
注意：CHR(13)是“回车”CHR(10)的ASCII字符， 是“换行”的ASCII字符。使用REPLACE不带替换值的 Oracle PL/SQL命令将删除数据中嵌入的任何“回车”和/或“换行”字符。这可能是因为注释字段是 CSV 文件中的最后一个字段。
回答by San
You can use replace(replace(column_name, chr(10)), chr(13))to remove newline charactors or regexp_replace(column_name, '\s+')to remove non printable charactors during loading
您可以在加载过程中replace(replace(column_name, chr(10)), chr(13))删除换行符或regexp_replace(column_name, '\s+')删除不可打印的字符
