SQL loader-第二个附件字符串不存在 [英] Sql loader - second enclosure string not present

问题描述

我正在通过sql loader将.csv文件数据加载到oracle表中.其中一个字段的数据中有换行符(CRLF)，因此，出现以下错误:

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:

第二个附件字符串不存在

second enclosure string not present

这是我的控制文件

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

COMMENTS字段在其记录之一中具有换行符.有人可以提出解决方案吗?

The field COMMENTS has a new line character in one of its records. Can any one suggest a solution for this.

谢谢

推荐答案

我找到了使用包含换行符和逗号的字段加载.csv文件的最佳方法.请在.csv文件上运行宏，然后使用sqlloader

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

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

对我来说运行完美.

这篇关于SQL loader-第二个附件字符串不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！