VBA - Replacing commas in CSV not inside quotes

Question

  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

In the above code the line objOut.Write Replace(Replace(arrData,",", "#|#"), Chr(34), "") is replacing all the commas with #|# including the commas inside string.so I want to replace only commas which are not in double quotes.

File containing the string "A","B,C",D

Result I need is A#|#B,C#|#D

Thanks for your help in advance.

this is one of the first hits on google. please search first. http://stackoverflow.com/questions/6780765/parse-csv-ignoring-commas-inside-string-literals-in-vba — Jeremy, Feb 16 '16 at 14:10
Thanks But the link you provided will change the delimiter for single string How can I apply this to multiple files in the loop.. I am not really expert in VBA if you could change my code that would be great help... — Lakshmanaraj Narayanasamy, Feb 16 '16 at 14:17
change you replace to replace "," with #|# instead of just replacing the , — MatthewD, Feb 16 '16 at 14:27
what are you actually trying to do? what is the purpose of re-writing the file with the pound signs? — Jeremy, Feb 16 '16 at 14:46
Client has some SQL procedure to import the data and it will accept only #|# column delimiter... — Lakshmanaraj Narayanasamy, Feb 16 '16 at 16:37

Ralph · Accepted Answer · 2016-02-17T08:28:51.263

1

How about something along the line of:

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

Basically, this exchanges now "," for #|#. But that's not enough as the file begins with a ". So, this one is being eliminated using the Mid() function. If the file also ends with a " then you would have to adjust that as well.

Based on the speed concerns noted in the comments here is the complete code which I used to test this solution:

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

The tested file was 350 MB with a bit over 4 million rows. The code completed in less than a minute.

edited Feb 17 '16 at 08:28

answered Feb 16 '16 at 18:23

Ralph

9,284
4
32
42

This won't work. I have file with 10000 lines out of which 3000 lines are having commas inside the double quotes. – Lakshmanaraj Narayanasamy Feb 17 '16 at 07:08
I am not sure what kind of performance you are looking for. But with the above code I was able to convert a 350 MB file with 4.2 million rows in less than a minute. – Ralph Feb 17 '16 at 08:27
See below Line COLUMN_TYPE,12,04-Feb-08,121,08-Dec-99,1,121,V,"A,B" I need results as COLUMN_TYPE#|#12#|#04-Feb-08#|#121#|#08-Dec-99#|#1#|#121#|#V#|#A,B – Lakshmanaraj Narayanasamy Feb 17 '16 at 09:33
It seems that you are changing your question. Yet, if you have several different formats in one file (the originally asked parsing situation and this new one) then it will get tricky and you'll have to analyze each row in order to find out which part of the line needs to be replaced and in which format. Anyway, the above solution answers your original question. So, please mark the question as closed. – Ralph Feb 17 '16 at 09:40

VBA - Replacing commas in CSV not inside quotes

1 Answers1

Linked