Is there a way to include commas in CSV columns without breaking the formatting

Question

I ve got a two column CSV with a name and a number  Some people s name use commas  for example Joe Blow  CFA  This comma breaks the CSV format  since it s interpreted as a new column   I ve read up and the most common prescription seems to be replacing that character  or replacing the delimiter  with a new value  e g  this that the  other    I d really like to keep the comma separator  I know excel supports other delimiters but other interpreters may not   I d also like to keep the comma in the name  as Joe Blow  CFA looks pretty silly   Is there a way to include commas in CSV columns without breaking the formatting  for example by escaping them

User · Answer

Depending on your language  there may be a to json method available  That will escape many things that break CSVs

User · Answer

Enclose the field in quotes  e g   field1 value field2 value  field 3 value  field4  etc      See wikipedia   Updated   To encode a quote  use    one double quote symbol in a field will be encoded as     and the whole field will become       So if you see the following in e g  Excel   ---------------------------------------   regular value                         ---------------------------------------   the CSV file will contain   regular value                                  A comma is simply encapsulated using quotes  so   becomes       A comma and quote needs to be encapsulated and quoted  so     becomes

User · Answer

May not be what is needed here but it s a very old question and the answer may help others   A tip I find useful with importing into Excel with a different separator is to open the file in a text editor and add a first line like    sep    where   is the separator you wish Excel to use  Alternatively you can change the default separator in Windows but a bit long-winded    Control Panel Clock  amp  region Region Formats Additional Numbers List separator  change from comma to your preferred alternative    That means Excel will also default to exporting CSVs using the chosen separator

User · Answer

I faced the same problem and quoting the   did not help  Eventually  I replaced the   with    finished the processing  saved the output into an outfile and replaced the   with    This may seem ugly but it worked for me

User · Answer

First  if item value has double quote character      replace with 2 double quote character       item   item ToString   Replace                 Finally  wrap item value   ON LEFT  With double quote character      ON RIGHT  With double quote character     and comma character      csv          amp  item ToString    amp

User · Answer

You could encode your values  for example in PHP base64 encode  str    base64 decode  str   IMO this is simpler than doubling up quotes  etc   https   www php net manual en function base64-encode php  The encoded values will never contain a comma so every comma in your CSV will be a separator

User · Answer

If you want to make that you said  you can use quotes  Something like this   name    Joe Blow  CFA     arr           name         so now  you can use comma in your name variable

User · Answer

Double quotes not worked for me  it worked for me     If you want to place a double quotes as example you can set        You can build formulas  as example   fprintf strout      if C3 1          B3    n      will write in csv       IF C3 1    B3

User · Answer

In addition to the points in other answers  one thing to note if you are using quotes in Excel is the placement of your spaces  If you have a line of code like this   print   s    s     s     s      value 1  value 2  value 3  value 4    Excel will treat the initial quote as a literal quote instead of using it to escape commas  Your code will need to change to  print   s   s    s    s      value 1  value 2  value 3  value 4    It was this subtlety that brought me here

User · Answer

You can use the Text Qualifier field in your Flat file connection manager to as    This should wrap your data in quotes and only separate by commas which are outside the quotes

User · Answer

You need to quote that values  Here is a more detailed spec

User · Answer

The problem with the CSV format  is there s not one spec  there are several accepted methods  with no way of distinguishing which should be used  for generate interpret    I discussed all the methods to escape characters  newlines in that case  but same basic premise  in another post   Basically it comes down to using a CSV generation escaping process for the intended users  and hoping the rest don t mind   Reference spec document

User · Answer

I found that some applications like Numbers in Mac ignore the double quote if there is space before it   a   b c  doesn t work while a  b c  works

User · Answer

You can use Template literals  Template strings   e g -      item

[csv] Is there a way to include commas in CSV columns without breaking the formatting?

Examples related to csv