VBA-用CSV代替逗号而不是用引号引起来

拉克什马纳拉吉·纳拉扬萨米（Lakshmanaraj Narayanasamy）

  Filename = Dir(Filepath & "\" & "*.csv")
    While Filename <> ""
        SourceFile = Filepath & "\" & Filename
        TargetFile = SavePath & "\" & Replace(Filename, ".csv", ".txt")

        OpenAsUnicode = False

        Dim objFSO: Set objFSO = CreateObject("Scripting.FileSystemObject")
        'Detect Unicode Files
        Dim Stream: Set Stream = objFSO.OpenTextFile(SourceFile, 1, False)
        intChar1 = Asc(Stream.Read(1))
        intChar2 = Asc(Stream.Read(1))
        Stream.Close
        If intChar1 = 255 And intChar2 = 254 Then
            OpenAsUnicode = True
        End If

        'Get script content
        Set Stream = objFSO.OpenTextFile(SourceFile, 1, 0, OpenAsUnicode)
        arrData = Stream.ReadAll()
        Stream.Close

        'Create output file
        Dim objOut: Set objOut = objFSO.CreateTextFile(TargetFile)
        objOut.Write Replace(Replace(arrData,",", "#|#"), Chr(34), "")  '-- This line is working fine but it is replacing all the commas inside the text qualifier as well..
        objOut.Close

        Filename = Dir

    Wend

在上面的代码中，行objOut.Write Replace（Replace（arplaceData，“，”，“＃|＃”），Chr（34），“”）用＃|＃替换所有逗号，包括string.so中的逗号。我只想替换没有用双引号引起来的逗号。

包含字符串“ A”，“ B，C”，D的文件

结果我需要的是A＃|＃B，C＃| #D

感谢您的帮助。

拉尔夫

关于以下内容的情况如何：

objOut.Write Mid(Replace(Replace(arrData,""",""", "#|#"), Chr(34), ""), 2)

基本上，这种交流现在","的#|#。但这还不够，因为文件以开头"。因此，这一Mid()功能正在被淘汰。如果文件也以a结尾，"那么您也必须对其进行调整。

基于注释中提到的速度问题，这里是用于测试此解决方案的完整代码：

Option Explicit
Option Compare Text

Public Sub ConvertFile()
Dim lngRowNumber As Long
Dim strLineFromFile As String
Dim strSourceFile As String
Dim strDestinationFile As String

strSourceFile = "C:\tmp\Extract.txt"
strDestinationFile = "C:\tmp\Extract_b.txt"

Open strSourceFile For Input As #1
Open strDestinationFile For Output As #2

lngRowNumber = 0

Do Until EOF(1)
    Line Input #1, strLineFromFile
    strLineFromFile = Mid(Replace(strLineFromFile, """,""", "#|#"), 2)
    Write #2, strLineFromFile
    strLineFromFile = vbNullString
Loop

Close #1
Close #2

End Sub

测试的文件为350 MB，超过400万行。代码在不到一分钟的时间内完成。

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。