• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Writing CSV File with Commas as Possible Values

 
Scott Florez
Ranch Hand
Posts: 58
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi guys and gals. I'm using the following code to write a CSV (comma-separated value) file containing the values of my resultset from a database query:



However, when an entry has a comma in it, it tricks Excel into thinking it should move to the next cell prematurely. Does anyone know how I can fix this?
 
Ulf Dittmer
Rancher
Posts: 42968
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The delimiter (i.e., the comma in this case, but it can be the semicolon in some regions, despite the name CSV) needs to be escaped by enclosing that cell in double apostrophes:

..., "yadda,yadda", ...

Double apostrophes themselves are escaped by doubling them:

..., "yadda ""yadda"" yadds", ...

CSV has more pitfalls. Why not use a ready-made library that does all this for you?
 
Carol Enderlin
drifter
Ranch Hand
Posts: 1364
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Put the content in double quotes ("<content>").

But then you can have a problem if your content has double quotes. I replaced double quote with two double ("") quotes. I don't think that created any further problems.

Hope that helps.

[dang it, Ulf beat me. He's right, there are other gotchas, like character fields that are turned into numbers if they look like scientific notation to excel]
[ February 02, 2007: Message edited by: Carol Enderlin ]
 
Scott Florez
Ranch Hand
Posts: 58
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks for the ideas guys. I actually came up with a very easy solution. Those seem to be the ones I often overlook. I just decided to go with a tab-delimited text file, which Excel reads perfectly. I just append a "\t" after each entry in a line. Thanks so much!
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic