40

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?

Aleksei Matiushkin
  • 119,336
  • 10
  • 100
  • 160
user338034
  • 433
  • 1
  • 4
  • 8

5 Answers5

46

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/)

Anthony Wang
  • 1,285
  • 1
  • 13
  • 14
  • 8
    This did not work for me. My file has long text fields with embedded commas. Using that format the long text no longer wraps within the cell width and consequently is displayed as a row of ####. When I followed the steps above, the resulting csv also, surprisingly, contains #### rather than the original text. – MattClarke Oct 13 '16 at 01:38
  • I used to use the concatenate solution. But this is much neater. – Omtara Jan 24 '17 at 10:10
  • 4
    Also use \"#\" for numeric columns – DropHit Oct 03 '17 at 20:57
  • This does not work as expected for Date or Numeric columns. – QuietSeditionist Mar 01 '19 at 17:01
  • For quoted numerical values use `\"#\";\"#\";\"#\";\"@\"` (these are the formats for respectively positive, negative, zero and text). And for dates you just enter the wanted date format enclosed with escaped quotes: `\"d-m-yyyy\"` – Piemol Nov 12 '19 at 19:43
  • 3
    For me this then saves as `""""Data"""",""""data2""""` which is what I see when opened in text editor. – wcndave May 18 '20 at 14:32
  • This is not the solution, this is just a over-engineered work-around. @Manav provided one of the correct solution. – Seymour Jul 28 '20 at 11:07
  • 1
    Not acceptable on Office 365, as it surrounds with triple-double quotes. 44000 ends up as `"""44000"""`. This is plainly ridiculous. Why on Earth would you want to triple your double quotes? The way I fixed this was then to use a text editor to replace `"""` with `"` – Fabien Haddadi Sep 22 '21 at 19:45
  • This solution is not the best. If you have empty string fields it will ignore the double quotes for those. – yaKay Feb 03 '22 at 06:21
37

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
Ravi Ram
  • 24,078
  • 21
  • 82
  • 113
Manav Patadia
  • 848
  • 7
  • 12
  • 4
    Excellent, i don't know why no upvotes for this, i literally wasted time righting code for doing this using nodejs – PDHide Apr 23 '20 at 18:48
  • 2
    Who thanks mate this works perfect, saves me huge amount of time! – Iason Jun 12 '20 at 13:17
  • 1
    UP vote this answer because it is the correct solution. – Seymour Jul 28 '20 at 11:07
  • 1
    This only adds double quotes at the start of initial cells and at the end of last cells. I have semicolon separated csv. Is it due to that? Is there anyone else experiencing the same problem? – Mutlu Simsek Jan 16 '21 at 13:25
  • Thank you so much, I've spent way too much time trying to solve this before seeing this answer. This was so easy and fixed my issue. :) – Adam DS Jul 26 '21 at 17:06
  • 1
    Well this is acceptable if you want to surround ALL fields with quotes, not specific ones... – Fabien Haddadi Sep 22 '21 at 19:41
  • This solution worked! Thank you! Note. Add a single quote (') before and after the path. Otherwise, you will get the following error: Import-Csv : Cannot bind parameter 'Delimiter'. – yaKay Feb 03 '22 at 06:21
  • import-csv assumed that the first row to be headers which have to be unique values. If there are repeated values on the first row, import-csv will return an error: 'The member "" is already present'. – yhd.leung May 03 '22 at 08:32
  • Had an issue with using the path separator \. I just used the filename without the path on the same folder. I suspect using \\ or / might work? No it didn't, the error is export-csv : Could not find a part of the path 'C:\Users\Documents\test2.csv'. – gfmoore Jan 05 '23 at 17:38
  • Some command to use in UNIX systems? – Ruben Dario Guarnizo Martinez Mar 29 '23 at 22:31
21

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.

joemienko
  • 2,220
  • 18
  • 27
  • 1
    This is not the solution, this is just a over-engineered work-around. @Manav provided one of the correct solution. – Seymour Jul 28 '20 at 11:07
  • 1
    @Seymour Sweet, let me just open PowerShell on my Mac where I'm using Excel... Using a second program such as terminal/shell/etc to manipulate spreadsheet data after saving in Excel is not an ideal or efficient solution. The above approach within Excel is exactly how I addressed. Agree to disagree. – sparecycle Jul 30 '20 at 15:05
  • 1
    Then what? If you export as a CSV you end up with 3 double quotes at beginning and end. – Garr Godfrey Oct 06 '20 at 21:54
  • @GarrGodfrey - The following discussion (I think) addresses your question (https://superuser.com/questions/349882/how-to-avoid-double-quotes-when-saving-excel-file-as-unicode). It looks like you would need to escape the double quotes with another set of double-quotes. – joemienko Oct 10 '20 at 14:16
  • @joemienko that doesn't work, that's how you end up with 3 sets of quotes in the CSV. Using Powershell proved the useful solution. One way to use you solution is to copy/paste from Excel into a text file, but Excel apparently will never save the CSV with a single set of double quotes. – Garr Godfrey Oct 13 '20 at 20:44
  • Not a solution for me either... as you would end up exporting your CSV with unwanted columns... So back to square one with hacking your export result with a text editor or other solutions... – Fabien Haddadi Sep 22 '21 at 19:48
  • This isn't vaguely a correct answer and doesn't solve the problem asked in the question – FreelanceConsultant Feb 16 '22 at 15:44
4

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.

Rob P
  • 99
  • 1
  • 4
  • 1
    saving as "CSV for MS DOS" does not work on windows (i don't know why they have this option in windows save as dialog in office 365) – vibs2006 Jan 06 '20 at 09:07
  • 1
    It... "sortof" works. In that it puts quotes around any text block segment that contains a comma. Sadly, that's not as useful as you might like. – Airk Jan 08 '20 at 17:52
  • 1
    Any field *which needs them* - this is therefore not a solution when you want to export your CSV **with** double quotes around every field. – MrUpsidown Jan 08 '21 at 15:33
1

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..."

bmgh1985
  • 779
  • 1
  • 14
  • 38
  • The second one can be condensed down as well, to `"...text...""...text..."` (note, no `&` needed in that one, although not as easy to debug) – bmgh1985 Aug 11 '14 at 07:40
  • Many thanks for response bmgh1985, I don't know how to use VBA, but the first solution offered does it for me. Thanks anyway! :-) – user338034 Aug 24 '14 at 13:50