This Excel tutorial explains how to quote all cells of a CSV file. CSV stands for “Comma Separated Values”. If a CSV file is represented by a data table then each row of the table is a one record set. And columns of the table equivalent to the data fields. So inside one record set, each field is separated by commas. Then each record set has the same amount of fields. Hence the same number of commas. But the problem is sometimes field data also contains embedded line breaks or commas.
For an example let’s consider the below CSV file.
If you look at the data in the second field, you will see commas also included as the field data. Lot’s of applications use CSV as a data exchanging format. So you can understand what will happen if you exchange the above file between two different applications. To overcome this issue, quotes are used to separate the field data.
In this lesson I will teach you how to quote all the cell values of a CSV file. We are going to do this using a macro. Don’t know how to add macro to a workbook? Follow these easy steps. How to add a macro to Excel file
Now let’s look at how we can create this macro. Let’s name this subroutine as QuoteAllCellValues.
End Sub
First we need to declare a few variables.
Dim WS As Worksheet
Dim WS_LastRowNumber As Long
Dim WS_LastColumnNumber As Long
Dim i As Long
Dim j As Long
Next we can open the CSV file and assign it to the WB variable.
Here we open the file using the full path. But this method may not be suitable for all the circumstances. Because if you use this method users need to access the code and change the path accordingly. I used this method to make this macro simple. But If you need a more user friendly application then use the file browsing method.
Next we can assign the activesheet to the WS variable.
Then we need to find the boundaries of the CSV file. In other words finding the last row and the last column of the data set.
WS_LastColumnNumber = WS.Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
So now we know how many rows and columns we have in our CSV file. Therefore we can use nested For Next loop statements to loop through each cell of the CSV file.
For j = 1 To WS_LastColumnNumber
Next j
Next i
Outer For Next is used to loop through the rows and inner statement for the columns. Then inside the For Next loop, we can use below code to quote the values inside the cell.
So the entire nested For Next loop section should look like this.
For j = 1 To WS_LastColumnNumber
WS.Cells(i, j).Value = Chr(34) & WS.Cells(i, j).Value & Chr(34)
Next j
Next i
And below is the full code of the subroutine.
Dim WB As Workbook
Dim WS As Worksheet
Dim WS_LastRowNumber As Long
Dim WS_LastColumnNumber As Long
Dim i As Long
Dim j As Long
Set WB = Workbooks.Open("D:\Work\New Post\Data.csv")
Set WS = ActiveSheet
WS_LastRowNumber = WS.Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
WS_LastColumnNumber = WS.Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
For i = 1 To WS_LastRowNumber
For j = 1 To WS_LastColumnNumber
WS.Cells(i, j).Value = Chr(34) & WS.Cells(i, j).Value & Chr(34)
Next j
Next i
MsgBox "Completed!", vbInformation, ""
End Sub
Here we used ASCII code for the character ". However we can use " directly in the code instead of Chr(34). To do that you can use below line instead of “WS.Cells(i, j).Value = Chr(34) & WS.Cells(i, j).Value & Chr(34)”
Note that you need to use an additional " character than normal string concatenation. I.e. """" instead of """
