aspose file tools*
The moose likes Java in General and the fly likes how to write comma in CSV file Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of The Java EE 7 Tutorial Volume 1 or Volume 2 this week in the Java EE forum
or jQuery UI in Action in the JavaScript forum!
JavaRanch » Java Forums » Java » Java in General
Bookmark "how to write comma in CSV file" Watch "how to write comma in CSV file" New topic
Author

how to write comma in CSV file

Raza Mohd
Ranch Hand

Joined: Jan 20, 2010
Posts: 247

hi all,

I need to write the header for csv where i want to add a field that contain comma.

for example.


cvv number ('Q','C') , card number , Id



But when i write CSV file::::
cvv number ('Q'
get writes in one cell.
and
'C')
in other.

how can i write
cvv number ('Q','C')
as a single field..???

please get me clear of it.



thanks in advance.


Good luck!!
A small leak can sink a Gigantic ship.>
Joanne Neal
Rancher

Joined: Aug 05, 2005
Posts: 3455
    
  12
Put double quotes around the fields that conmtain commas.

"cvv number ('Q','C')" , card number , Id

Joanne
Raza Mohd
Ranch Hand

Joined: Jan 20, 2010
Posts: 247

thanks for response joanne!

I did but still having the same problem.

Can not able to write as a single field.


Is there any solution?



Thanks
raza!
Joanne Neal
Rancher

Joined: Aug 05, 2005
Posts: 3455
    
  12
How are you parsing the line ? A proper csv parser will ignore comma contained inside double quotes.

Edit

Okay. Just re-read your original post. You say you are writing the csv file, not reading it. What are you writing it to ? What are the 'cells' you refer to ?
Raza Mohd
Ranch Hand

Joined: Jan 20, 2010
Posts: 247

I am just passing it into String[] and writing with writeNext()


String[] header = new String[]{"cvv number ('Q','C')" , "card number" , "Id" };

reconWriter.writeNext(header);
Joanne Neal
Rancher

Joined: Aug 05, 2005
Posts: 3455
    
  12
But you must be writing it to something - a text file, an excel file, a socket, an instance of a class, etc, etc
Raza Mohd
Ranch Hand

Joined: Jan 20, 2010
Posts: 247

when i open the written file in Excel it splits the field in the seperate cells where it finds comma in between.

Joanne Neal
Rancher

Joined: Aug 05, 2005
Posts: 3455
    
  12
You haven't put any quotes around what you are writing out.

The quotes in this line just tell java that it is a String. The quotes aren't actually part of the String.
String[] header = new String[]{"cvv number ('Q','C')" , "card number" , "Id" };

Try this
String[] header = new String[]{"\"cvv number ('Q','C')\"" , "card number" , "Id" };
Raza Mohd
Ranch Hand

Joined: Jan 20, 2010
Posts: 247

I am writing a file with .csv extension and file is Excel Comma Saperated Values File.
Joanne Neal
Rancher

Joined: Aug 05, 2005
Posts: 3455
    
  12
Raza Mohd wrote:I am writing a file with .csv extension and file is Excel Comma Saperated Values File.


I assume you wrote this before you read my last post ?
Raza Mohd
Ranch Hand

Joined: Jan 20, 2010
Posts: 247

hi i am having same problem yet.

i am using this constructor of CSVWriter.

reconWriter = new CSVWriter(new FileWriter(filepath), ',', CSVWriter.NO_QUOTE_CHARACTER);
Joanne Neal
Rancher

Joined: Aug 05, 2005
Posts: 3455
    
  12
I don't know where you got the CSVWriter class from but that last parameter looks very suspicious. I would try and find out what that actually does. If it stops the CSVWriter from outputting quote characters, then that is probably your problem.

You could try opening your output file in a text editor like Notepad and see if the quotes are actualy being written to the file.
Raza Mohd
Ranch Hand

Joined: Jan 20, 2010
Posts: 247

opening a file inn a editor does not produce any quote because of that NO_QUOTE_CHARACTER

when i turned it to DEFAULT_QUOTE_CHARACTER it solved my problem .

But getting quote when i open it anywhere that i do not need to have.

How could it be handle?



Joanne Neal
Rancher

Joined: Aug 05, 2005
Posts: 3455
    
  12
Raza Mohd wrote:But getting quote when i open it anywhere that i do not need to have.

How could it be handle?


If you want to have the data in CSV format, then you need to have quotes around any values that contain a comma.
If you don't want quotes around the values, then you need to either store the data in a different format or remove the quotes as you are reading the file into the programs that don't need them.
Raza Mohd
Ranch Hand

Joined: Jan 20, 2010
Posts: 247

If i use DEFAULT_QUOTE_CHARACTER
then no need to add Quotes to write the field having commas as you said.
Raza Mohd
Ranch Hand

Joined: Jan 20, 2010
Posts: 247

even when i use Quote around the field.
It gets splitted and appends starting " at the last .
Raza Mohd
Ranch Hand

Joined: Jan 20, 2010
Posts: 247

I used Quotes as you said.


but it gives two fields.

Joanne Neal
Rancher

Joined: Aug 05, 2005
Posts: 3455
    
  12
Can you post the contents of the file you've written the data to. Use a simple text editor like Notepad to open it, not Excel.

Can you also post your current code.
Raza Mohd
Ranch Hand

Joined: Jan 20, 2010
Posts: 247


This is the code and data i am writing to csv.


Lester Burnham
Rancher

Joined: Oct 14, 2008
Posts: 1337
You should leave the handling of whether or not to enclose cell contents in double quotes to the CSV library. The way it's now, the double quotes you're adding will be considered part of the cell contents, and accordingly be escaped by the library (at least that's what should happen if the library works correctly).

That would be visible in the file contents Joanne asked you post.
Raza Mohd
Ranch Hand

Joined: Jan 20, 2010
Posts: 247

then what is the solution for that.
Joanne told me to enclose it in Quotes so that it did not get splitted.
Lester Burnham
Rancher

Joined: Oct 14, 2008
Posts: 1337
The first step is to examine the actual file that gets created, and post the contents here, like Joanne asked you to do.

The second step is to read the documentation of the CSV library and to experiment with it until you know how it handles this. If the library is even half decent, then you shouldn't have to think about this issue at all - it should do the right thing on its own. For example, why are you passing any parameter at all to the CSVWriter constructor (besides the FileWriter, obviously)?
Raza Mohd
Ranch Hand

Joined: Jan 20, 2010
Posts: 247

I am using opencsv api.

and i read it.

If i simply use Constructor with FileWriter object.

Quotes appear around field when i open it in notepad.

I am having serious problem..

Lester Burnham
Rancher

Joined: Oct 14, 2008
Posts: 1337
Quotes appear around field when i open it in notepad.

Of course there are quotes - that's how CSV works (which Notepad knows nothing about). The important question is what Excel does with the file. If it still doesn't work, post the code you're now using and the file it creates.
Raza Mohd
Ranch Hand

Joined: Jan 20, 2010
Posts: 247

it worked .

I userd the String as parameter in Constructor itself.

But when i was using String Array i dont know why it was not working

Lester Burnham
Rancher

Joined: Oct 14, 2008
Posts: 1337
What kind of code is that - you're trying to pass the actual data in a parameter called "lineEnd"?

The point of using a library is to make your life easier. so that you don't have to think about any of this. If you end up coding it all yourself, then there's no point in using a library.
Raza Mohd
Ranch Hand

Joined: Jan 20, 2010
Posts: 247

yeah you are right it was only hit and trial.

and it wrote only single line.

and write whole data in a single line.

You are right it was not at all correct what i was doing.library is made to done things in a easy way.

Actually i need to do this by today . it was urgent so trying to use hit and trial.

but caught again in problem.


Joanne Neal
Rancher

Joined: Aug 05, 2005
Posts: 3455
    
  12
Note that I only suggested putting quotes into your string before I found out you were using a specialist library to create your CSV files. As Lester said, if the library can do all that for you, then you should let it.
Raza Mohd
Ranch Hand

Joined: Jan 20, 2010
Posts: 247

so how can it be done?
even i put Quotes still not able to write.
Lester Burnham
Rancher

Joined: Oct 14, 2008
Posts: 1337
I said:
Lester Burnham wrote:If it still doesn't work, post the code you're now using and the file it creates.

to which you replied:
Raza Mohd wrote:it worked .

Are you now saying that you were mistaken, and it doesn't actually work? If so, my reply is still the same.
Raza Mohd
Ranch Hand

Joined: Jan 20, 2010
Posts: 247

That is the code i am using




and the file it generate is Excel File
Lester Burnham
Rancher

Joined: Oct 14, 2008
Posts: 1337
Why are you -again- adding double quotes in your own code? Why are you -again- passing additional parameters to the CSVWriter constructor? Why are you -again- not posting the file that gets created? It seems we're (and especially you) are running around in circles
Raza Mohd
Ranch Hand

Joined: Jan 20, 2010
Posts: 247

not adding quotes does not solve my problem....
Lester Burnham
Rancher

Joined: Oct 14, 2008
Posts: 1337
I give up.
Raza Mohd
Ranch Hand

Joined: Jan 20, 2010
Posts: 247

i am exhausted
Joanne Neal
Rancher

Joined: Aug 05, 2005
Posts: 3455
    
  12
Raza Mohd wrote:and the file it generate is Excel File


Open the file using Notepad and copy and paste the contents into here.
Raza Mohd
Ranch Hand

Joined: Jan 20, 2010
Posts: 247

This is the content of file when i open it in notepad....

~,C2A050810CL
Agr no,Receipt No.,Channel,AgencyID,Cheque Number,Dealing Bank Id (=1 if null),Drawn On,Towards,Receipt Amount,Received Date (Cheque sent Date in database),Cheque Date (Receipt Date in database),"Receipt Type ('Q',,'C',,'D') ('Q' if null)",CR. Note number (CRNNUM),"Cheque Status ('R',,'D',,'C') ('C' if null)","Auto Alloc ('Y',,'N') ('N' if null)",EMI Amount,LPP Amount,BCC Amount,Excess Amount,OTHCHARGE1,OTHAMT1,OTHCHARGE2,OTHAMT2,OTHCHARGE3,OTHAMT3,OTHCHARGE4,OTHAMT4,Remarks,Payable At,IT DOC,Paid by,PAN NO,Purpose
011025288000,CP0000009021,,,CP0000009021,13392-PDK,ICICI BANK LTD,Call to Pay payment received,900.0,05/08/2010,05/08/2010,Q,,C,Y,900.0,,,,,,,,,,,,,MUMBAI,,,,NORMAL
011025288055,CP0000073428,,,CP0000073428,13392-PDK,ICICI BANK LTD,Call to Pay payment received,6017.0,05/08/2010,05/08/2010,Q,,C,Y,6017.0,,,,,,,,,,,,,MUMBAI,,,,NORMAL
Joanne Neal
Rancher

Joined: Aug 05, 2005
Posts: 3455
    
  12
From a quick scan that looks to be a valid CSV file - all the fields that contain commas are surrounded by quotes.

It's difficult to tell from the rest of the thread just what problem you are currently having, so maybe you need to summarise your results so far.
Raza Mohd
Ranch Hand

Joined: Jan 20, 2010
Posts: 247

value inside quotes contain Double comma , which is the problem .

because i need to create file with one comma..
Joanne Neal
Rancher

Joined: Aug 05, 2005
Posts: 3455
    
  12
Assuming you are still using the code you posted at 05 August 2010 10:56:33, there is nothing there that would create double commas, so it must be something the CSVWriter class is doing. You may want to check the documenttaion for that to see if there is any explanation.

Of course, if you've changed your code then you need to post the latest version.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: how to write comma in CSV file