This tutorial explains how to add double quotes to all values in comma separated string. Here I will talk about a couple of methods that you can use to enclose CSV values in double quotes. Here I will use Excel macros and tweaks that you can use to add double quotes to all values. If you are familiar with Excel and its macros then it will be pretty easy to understand that. I will mention a VBA script that you can add in Excel to save current CSV with double quoted values. There aren’t easy ways to do it so I will show you how it done on Windows.
When you convert a CSV to SQL then often some fields do not convert properly. This is because if there is an additional comma in the string then parser will fail. To prevent this, you can enclose the CSV values in double quotes and then save data to file. And not just double quotes but you can also use single quotes if you can tweak the VBA script a little. In Excel, there is a features to format a specific cell that most people are unaware of and I will also include that in this post.
How to add Double Quotes to all Values in Comma Separated String?
Method 1: Add Double Quotes to CSV Values using VBA Script
The very first method to add double quotes to CSV values in Excel is using a VBA script. I think that very few people know that if there isn’t any formula in Excel that you want then you can create that. However, that requires some programming skills. As for adding double quotes to a CSV, there is no built-in method for it so you will have to create it. There is a special programming language called VBA that you can use to write some external functions in Excel. And that is what I will do here.
The first step to use the VBA script in Excel to add double quotes is to open the target CSV data in Excel. Next, select the cells you want to enclose in double quotes. After that, open VBA editor using Alt+F11 key.
Now, insert a module there and then paste the following code in it.
Sub QuoteCommaExport()
' Dimension all variables.
Dim DestFile As String
Dim FileNum As Integer
Dim ColumnCount As Long
Dim RowCount As Long
Dim MaxRow As Long
Dim MaxCol As Long
' Prompt user for destination file name.
DestFile = InputBox("Enter the destination filename" _
& Chr(10) & "(with complete path):", "Quote-Comma Exporter")
' Obtain next free file handle number.
FileNum = FreeFile()
' Turn error checking off.
On Error Resume Next
' Attempt to open destination file for output.
Open DestFile For Output As #FileNum
' If an error occurs report it and end.
If Err <> 0 Then
MsgBox "Cannot open filename " & DestFile
End
End If
' Turn error checking on.
On Error GoTo 0
MaxRow = ActiveSheet.UsedRange.Rows.Count
MaxCol = Selection.Columns.Count
MsgBox "Processing this many rows: " & MaxRow
MsgBox "Processing this many columns: " & MaxCol
' Loop for each row in selection.
For RowCount = 1 To MaxRow
' Loop for each column in selection.
For ColumnCount = 1 To MaxCol
' Write current cell's text to file with quotation marks.
Print #FileNum, """" & Selection.Cells(RowCount, _
ColumnCount).Text & """";
' Check if cell is in last column.
If ColumnCount = MaxCol Then
' If so, then write a blank line.
Print #FileNum,
Else
' Otherwise, write a comma.
Print #FileNum, ",";
End If
' Start next iteration of ColumnCount loop.
Next ColumnCount
' Start next iteration of RowCount loop.
Next RowCount
' Close destination file.
Close #FileNum
End Sub
Source
You are now ready to execute the script. Hit the play button from the interface of the VBA editor and then a message box will appear where you have to specify location of the CSV file that you want to save. Specify the full path and then hit OK button. It will save the file by enclosing all command separated values in double quotes.
This way, you can easily add double quotes to CSV values in Excel. And if you have deep knowledge of macros than you can make this one permanent. You can use this in any version of Excel and it will work like charm.
Method 2: Add Double Quotes to Comma Separated Values using Cell Formatting.
Apart from using a VBA code, you can use a Cell Formatting feature of Excel to add double quotes to comma separated values. However, this method doesn’t work on numeric data. This means that if there are some columns in CSV which have numeric values then they will remain unaffected after you apply cell formatting. Which is why, this method is suitable if your CSV have text data mostly.
To do this, use Format > Format Cells… option from the Home ribbon as you can see in the screenshot below. And before doing this, select the range in the sheet that you want to affect.
Now, in the Format Cells window, choose Custom and add /”@/” in the field and hit OK. After that, you will see that the values are now in double quotes. You can see the following screenshot to see how this works.
So, this is the second way of adding double quotes to command separated values in Excel. You can go with this method, in case you have mostly text data. And not only double quotes but you can opt for enclosing the cell data in single quotes as well. For that, you’ll just have to replace double quotes from the custom formatting formula that I mentioned above.
Final thoughts
These are best ways that you can use to easily add double quotes to comma separated strings. The best usage of these tweaks would be in creating the SQL queries from CSV data easily. Also, there can be other uses of the same. So, whatever use you have for to get double quoted CSV values, this post will be helpful. Also, you are free to share any other handy trick or method you know to do the same.
