使用LotusScript导入CSV至Notes遇引号内逗号拆分异常求解决方案

LotusScript的Input #语句会默认将逗号作为字段分隔符，无论逗号是否被双引号包裹，这就是导致字段拆分混乱的根源。要正确处理带引号的CSV字段，必须替换Input #为整行读取，并自定义CSV解析逻辑。

解决方案步骤 #

1. 替换整行读取方式 #

使用Line Input #读取CSV的每一行，避免系统自动拆分字段。

2. 自定义CSV字段解析函数 #

编写函数解析单行CSV文本，识别双引号包裹的字段，忽略字段内的逗号分隔符。

3. 修改导入循环逻辑 #

用解析后的字段数组为Notes文档赋值，并添加错误检查，避免数据异常。

完整代码示例 #

自定义CSV解析函数 #

Function ParseCSVLine(line As String) As Variant
    Dim fields() As String
    Dim field As String
    Dim inQuotes As Boolean
    Dim i As Integer
    Dim char As String
    
    inQuotes = False
    field = ""
    ReDim fields(0)
    
    For i = 1 To Len(line)
        char = Mid(line, i, 1)
        
        If char = Chr(34) Then ' 处理双引号
            inQuotes = Not inQuotes
            ' 处理转义引号（例如""表示单个"）
            If inQuotes And Mid(line, i+1, 1) = Chr(34) Then
                field = field & Chr(34)
                i = i + 1 ' 跳过下一个引号
            End If
        ElseIf char = "," And Not inQuotes Then
            ' 非引号内的逗号作为字段分隔符
            fields(UBound(fields)) = Trim(field)
            field = ""
            ReDim Preserve fields(UBound(fields)+1)
        Else
            field = field & char
        End If
    Next i
    
    ' 添加最后一个字段
    fields(UBound(fields)) = Trim(field)
    
    ' 移除字段首尾的双引号
    For i = 0 To UBound(fields)
        If Left(fields(i), 1) = Chr(34) And Right(fields(i), 1) = Chr(34) Then
            fields(i) = Mid(fields(i), 2, Len(fields(i))-2)
        End If
    Next i
    
    ParseCSVLine = fields
End Function

修改后的导入循环 #

Dim line As String
Dim csvFields As Variant
Dim row As Integer
Dim counter As Integer
Dim success As Boolean
Dim newDoc As NotesDocument
Dim dbCurr As NotesDatabase

' 假设dbCurr已初始化，filenum已通过Open语句打开

row = 0
counter = 0

Do While Not EOF(filenum)
    Line Input #filenum, line ' 读取整行CSV数据
    row = row + 1
    
    If row > 1 Then ' 跳过表头行，根据实际CSV结构调整
        csvFields = ParseCSVLine(line)
        
        ' 验证字段数量是否匹配预期（你的数据有14个字段，索引0-13）
        If UBound(csvFields) = 13 Then
            Set newDoc = dbCurr.CreateDocument
            With newDoc
                .Form = "jobcode"
                .jobcode = csvFields(0)
                .jobtitle = csvFields(1)
                .jobfunction = csvFields(2)
                .mgrlevel = csvFields(3)
                .eeo4cat = csvFields(4)
                .jobsubfunction = csvFields(5)
                .salaryplan = csvFields(6)
                .paygrade = csvFields(7)
                .jobfamily = csvFields(8)
                .minannual = CDbl(csvFields(9))
                .midannual = CDbl(csvFields(10))
                .maxannual = CDbl(csvFields(11))
                .jobsummary = csvFields(12)
                .jobquals = csvFields(13)
            End With
            
            success = newDoc.ComputeWithForm(False, False)
            If success Then
                Call newDoc.Save(True, False)
                counter = counter + 1
            Else
                Print "Row " & CStr(row) & " failed form validation"
            End If
        Else
            Print "Row " & CStr(row) & " has invalid field count: " & CStr(UBound(csvFields)+1)
        End If
    End If
Loop

Print "Import completed. " & CStr(counter) & " documents saved."

关键说明 #

• 引号状态跟踪：通过inQuotes变量判断是否处于双引号包裹状态，仅当不在引号内时才将逗号视为分隔符。
• 转义引号处理：支持CSV标准的转义规则（用""表示单个双引号），如果你的CSV没有这种场景，可以删除对应逻辑。
• 错误防护：添加字段数量验证，避免因数据格式错误导致的数组越界或赋值异常。
• 表头控制：通过row > 1跳过第一行表头，可根据实际CSV结构调整判断条件。

内容的提问来源于stack exchange，提问作者Matt Smith