File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes I/O and Streams and the fly likes Writing CSV File with Commas as Possible Values Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » I/O and Streams
Bookmark "Writing CSV File with Commas as Possible Values" Watch "Writing CSV File with Commas as Possible Values" New topic
Author

Writing CSV File with Commas as Possible Values

Scott Florez
Ranch Hand

Joined: Dec 05, 2006
Posts: 58
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
Marshal

Joined: Mar 22, 2005
Posts: 39548
    
  27
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?


Ping & DNS - updated with new look and Ping home screen widget
Carol Enderlin
drifter
Ranch Hand

Joined: Oct 10, 2000
Posts: 1364
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

Joined: Dec 05, 2006
Posts: 58
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!
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Writing CSV File with Commas as Possible Values
 
Similar Threads
center a string in padding using String.format
How to set width of cells in excel on browser ?
Writing CSV File from Database Generates Null Pointer
Faster Sum of Prime Numbers
How to change the values in the file (output)