• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

how to write comma in CSV file

 
Ranch Hand
Posts: 247
MyEclipse IDE MySQL Database Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Rancher
Posts: 3742
16
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Put double quotes around the fields that conmtain commas.

"cvv number ('Q','C')" , card number , Id
 
Raza Mohd
Ranch Hand
Posts: 247
MyEclipse IDE MySQL Database Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 3742
16
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 247
MyEclipse IDE MySQL Database Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 3742
16
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 247
MyEclipse IDE MySQL Database Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 3742
16
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 247
MyEclipse IDE MySQL Database Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I am writing a file with .csv extension and file is Excel Comma Saperated Values File.
 
Joanne Neal
Rancher
Posts: 3742
16
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 247
MyEclipse IDE MySQL Database Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 3742
16
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 247
MyEclipse IDE MySQL Database Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 3742
16
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 247
MyEclipse IDE MySQL Database Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 247
MyEclipse IDE MySQL Database Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
even when i use Quote around the field.
It gets splitted and appends starting " at the last .
 
Raza Mohd
Ranch Hand
Posts: 247
MyEclipse IDE MySQL Database Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I used Quotes as you said.


but it gives two fields.

 
Joanne Neal
Rancher
Posts: 3742
16
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 247
MyEclipse IDE MySQL Database Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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


 
Rancher
Posts: 1337
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 247
MyEclipse IDE MySQL Database Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 1337
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 247
MyEclipse IDE MySQL Database Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 1337
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 247
MyEclipse IDE MySQL Database Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 1337
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 247
MyEclipse IDE MySQL Database Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 3742
16
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 247
MyEclipse IDE MySQL Database Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
so how can it be done?
even i put Quotes still not able to write.
 
Lester Burnham
Rancher
Posts: 1337
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 247
MyEclipse IDE MySQL Database Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
That is the code i am using




and the file it generate is Excel File
 
Lester Burnham
Rancher
Posts: 1337
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 247
MyEclipse IDE MySQL Database Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
not adding quotes does not solve my problem....
 
Lester Burnham
Rancher
Posts: 1337
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I give up.
 
Raza Mohd
Ranch Hand
Posts: 247
MyEclipse IDE MySQL Database Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
i am exhausted
 
Joanne Neal
Rancher
Posts: 3742
16
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 247
MyEclipse IDE MySQL Database Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 3742
16
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 247
MyEclipse IDE MySQL Database Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
value inside quotes contain Double comma , which is the problem .

because i need to create file with one comma..
 
Joanne Neal
Rancher
Posts: 3742
16
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
reply
    Bookmark Topic Watch Topic
  • New Topic