如何替换CSV文件中的特定换行符？现有代码会替换所有换行符

需要替换CSV文件中字段内部的换行符，保留行与行之间的正常换行。示例CSV内容如下：

"1.","",""
"2.","Combi","SCK Test 1"
"3.","",""
"4.","",""
"5.","",""
"6.","Combi","SCK
Test 2"
"7.","",""
"8.","",""
"9.","",""
"10.","Combi","SCK Test 3"
"11.","",""
"12.","",""
"13.","",""
"14.","",""
"15.","",""
"16.","Combi","SCK Test 4"
"17.","Combi","SCK
Test 5"
"18.","Combi","SCK Test 6"
"19.","Combi","SCK Test 7"
"20.","Combi","SCK Test 8"

原VBA代码会替换所有换行符，无法区分字段内和行尾的换行，代码如下：

Dim FSO As Object
Dim Filename As String
 
Set FSO = CreateObject("Scripting.FileSystemObject")

Filename = "F:\Docs\data.csv"

text = FSO.OpenTextFile(Filename).ReadAll

FSO.OpenTextFile(Filename, 2).Write Replace(text, vbLf, ",")

Debug.Print FSO.OpenTextFile(Filename:=Filename, IOMode:=ForReading, Format:=TristateUseDefault).ReadAll

解决方案 #

核心思路：CSV中字段内的换行处于未闭合的双引号对中，而行尾换行在双引号之后。通过遍历文本、跟踪引号的闭合状态，只替换处于未闭合引号内的换行符。

修改后的VBA代码：

Dim FSO As Object
Dim Filename As String
Dim text As String
Dim char As String
Dim result As String
Dim inQuotes As Boolean
Dim i As Integer

Set FSO = CreateObject("Scripting.FileSystemObject")
Filename = "F:\Docs\data.csv"
text = FSO.OpenTextFile(Filename).ReadAll

inQuotes = False
result = ""

For i = 1 To Len(text)
    char = Mid(text, i, 1)
    ' 切换引号闭合状态
    If char = """" Then
        inQuotes = Not inQuotes
        result = result & char
    ' 仅替换引号内的换行符为逗号
    ElseIf inQuotes And (char = vbLf Or char = vbCr) Then
        result = result & ","
    ' 其他字符直接保留
    Else
        result = result & char
    End If
Next i

' 写入处理后的内容
FSO.OpenTextFile(Filename, 2).Write result
Debug.Print result

代码说明 #

• inQuotes变量实时跟踪当前是否处于双引号内部，遇到双引号时切换状态
• 遍历每个字符，仅当处于引号内且是换行符时，才将其替换为逗号
• 行尾的换行符因处于引号外，会被直接保留，不会被修改

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