Adding double quote delimiters into csv file

31
votes

I have a number of text files which contain radio programme titles where each item is on a separate line, e.g.:

15 by 15
15 Minute Drama
Adrian Mole
Afternoon Drama
Afternoon Reading
etc

I would like to add double quotes as delimiters to each line, e.g.:

"15 by 15"
"15 Minute Drama"
"Adrian Mole"
"Afternoon Drama"
"Afternoon Reading"
etc

I thought I might be able to do this in Excel but not been able to find a way. I really don't mind whether the solution is Excel based or something else, as long as I don't have to do it manually.

Can anyone help please?

5
excelcsvdouble-quotes
If one of these solutions meets your needs, please kindly select one of the answers. – joemienko

5 Answers

40
votes

Here's a way to do it without formulas or macros:

  1. Save your CSV as Excel
  2. Select any cells that might have commas
  3. Open to the Format menu and click on Cells
  4. Pick the Custom format
  5. Enter this => \"@\"
  6. Click OK
  7. Save the file as CSV

(from http://www.lenashore.com/2012/04/how-to-add-quotes-to-your-cells-in-excel-automatically/)

22
votes

open powershell and run below command:

import-csv C:\Users\Documents\Weekly_Status.csv | export-csv C:\Users\Documents\Weekly_Status2.csv  -NoTypeInformation -Encoding UTF8
21
votes

This is actually pretty easy in Excel (or any spreadsheet application).

You'll want to use the =CONCATENATE() function as shown in the formula bar in the following screenshot:

Step 1 involves adding quotes in column B,

Step 2 involves specifying the function and then copying it down column C (by now your spreadsheet should look like the screenshot),

enter image description here

Step 3 (if you need the text outside of the formula) involves copying column C, right-clicking on column D, choosing Paste Special >> Paste Values. Column D should then contain the text that was calculated in column C.

4
votes

In Excel for Mac at least, you can do this by saving as "CSV for MS DOS" which adds double quotes for any field which needs them.

1
votes

Double quotes can be achieved using VBA in one of two ways

First one is often the best

"...text..." & Chr(34) & "...text..."

Or the second one, which is more literal

"...text..." & """" & "...text..."