How to add quotes for every string value in a csv file via Excel VBA?

Question

I have a csv file, and I need a VBA function that adds quotes to every string value in the file, so that something like

vertical,device_name,value
mobile,Apple iPad,0
mobile,HTC,3

looks like

"vertical","device_name","value"
"mobile","Apple iPad",0
"mobile","HTC",3

What I have found until now is a macro,

Sub QuotesAroundText()
    Dim c As Range
    For Each c In Selection
        If Not IsNumeric(c.Value) Then
            c.Value = """" & c.Value & """"
        End If
    Next c
End Sub

that does almost exactly what I need - it add the quotes, but not to string, but to excel cells. That means, that this macro does work correctly in a xlsx file, but not in a csv file.

So I need a vba code that adds quotes not to cells, but to string, that are between commas.

Community · Accepted Answer · 2017-05-23 10:32:30Z

By emulating a string builder I was able to process a CSV file with 59,507 Rows x 9 Columns in just over 3 seconds. This process is much faster that standard concatenation.

This function was modified from my answer to Turn Excel range into VBA string

Test

Sub TestProcessCSVFile()
    Dim s As String
    Dim Start: Start = Timer

    ProcessCSVFile "C:\Users\Owner\Downloads\SampleCSVFile_53000kb.csv", "C:\Users\Owner\Downloads\_temp.csv"

    Debug.Print "Execution Time: "; Timer - Start; "Second(s)"

End Sub

Code

Sub ProcessCSVFile(OldFile As String, NewFile As String)
    Dim Data As String, text As String
    Dim vCell
    Dim length As Long

    Open OldFile For Binary As #1
    Data = Space$(LOF(1))
    Get #1, , Data
    Close #1

    text = Space(Len(Data) * 1.5)

    For Each vCell In Split(Replace(Data, vbCrLf, "," & vbCrLf & ","), ",")
        If Len(vCell) + length + 5 > Len(text) Then text = text & Space(Len(Data) * 0.1)

        If vCell = vbCrLf Then
            Mid(text, length, 1) = vbCrLf
        ElseIf IsNumeric(vCell) Then
            Mid(text, length + 1, Len(vCell) + 1) = vCell & ","
            length = length + Len(vCell) + 1
        Else
            Mid(text, length + 1, Len(vCell) + 3) = Chr(34) & vCell & Chr(34) & ","
            length = length + Len(vCell) + 3
        End If

    Next

    Open NewFile For Output As #1
    Print #1, Left(text, length - 1)
    Close #1

End Sub

Results

Preston · Accepted Answer · 2016-10-05 12:08:23Z

1

Read the text file in using line input, then taylor the following process to your needs:

Sub y()
a = "a,b,c,d"

'Split into 1d Array
b = Split(a, ",", , vbTextCompare)

'for each element in array add the quotes
For c = 0 To UBound(b)
    b(c) = """" & b(c) & """"
Next c

'join the product up
d = Join(b, ",")

'Print her out
Debug.Print d
End Sub

answered Oct 5, 2016 at 12:08

Preston

8,26511 gold badges64 silver badges96 bronze badges

Comments

Agung Hutomo · Accepted Answer · 2016-10-05 12:16:32Z

0

use workbooks.opentext filename:="your csv file with path", It will open the csv and separate them into cells, then apply your macro and save again as csv

answered Oct 5, 2016 at 12:16

Agung Hutomo

211 bronze badge

Collectives™ on Stack Overflow

How to add quotes for every string value in a csv file via Excel VBA?

3 Answers 3

Test

Code

Results

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Test

Code

Results

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related