Including comma in CSV file

Question

I am currently working on importing data in CSV file. I am stuck over deciding a variable which needs to be added to a field which is comma separated. Below is the data -

 "acNumber","Date","Code"
  12332,123  09/12/2012 1231

This is the desired format. How it actually shows

  "acNumber","Date","Code"
  12332        123   09/12/2012 ...

Actual data being sent is a string & in the format below -

"12332,13321","08/08/2016","1234"

I have tried below the following possible solutions -

1) surrounding my code with quotes as suggested here

""12332,13321""

I have added two double quotes as the value was already covered in quotes.I have tried with single double quotes as well.

2) Use of escape ("\") before comma in final output string

 "12332\,13321"

I have googled over it and still didn't found any solution.Kindly help.

Naren are you opening this CSV in a spreadsheet. If you do that , the " surrounding your data will simply go away. can you open the CSV on a text processor such as notepad++ or so and see if you have the quotes there ? — Ramachandran.A.G, Aug 08 '16 at 09:44
`"12332,13321","08/08/2016","1234"` is the proper format - https://en.wikipedia.org/wiki/Comma-separated_values — IVO GELOV, Aug 08 '16 at 09:46
Quotes around the value should suffice. (Of course in a string literal the quotes must be escaped.) You might try a tab `\t` as separator char instead. Maybe show code. — Joop Eggen, Aug 08 '16 at 09:46
@RamachandranGA I opened it in notepad++. It had quotes over it — Narendra Pandey, Aug 08 '16 at 10:00

score 3 · Answer 1 · answered Aug 08 '16 at 09:52

3

If you want to escape your separator which is a comma here you are supposed to put the content between double quotes so if you have 12332,123 you should put "12332,123" in your CSV file.

If you want to escape a content between double quotes you have put it between two new double quotes which gives three double quotes in total so if you have "12332,13321" you should put """12332,13321"""

answered Aug 08 '16 at 09:52

Nicolas Filotto

43,537
11
94
122

hi nicolas, when i use """12332,13321""" as my input, i get ="""12332 & 13321""" in two different cells. – Narendra Pandey Aug 08 '16 at 12:39
where do you get it? in excel? – Nicolas Filotto Aug 08 '16 at 12:41
I have could import it successfully ("12332,13321" in one cell only) on Excel and Libre Office. Which software do you use to import your CSV file? – Nicolas Filotto Aug 08 '16 at 13:03
Thanks, i got solution of it. I was ignoring '='. :( – Narendra Pandey Aug 09 '16 at 08:11

score 1 · Answer 2 · answered Aug 08 '16 at 10:33

1

Just tried this

a) Enclosing in """ seems to work. You can append with """ in your code and it should be ready to go

Edit : My bad , just saw Nicolos post the same answer. He is right

answered Aug 08 '16 at 10:33

Ramachandran.A.G

4,788
1
12
24

after using """12332""","""13321""", i get data in two different cells as "12332" and "13321" – Narendra Pandey Aug 08 '16 at 12:31
Thanks, i got solution of it. – Narendra Pandey Aug 09 '16 at 08:10

score 0 · Answer 3 · answered Aug 08 '16 at 10:58

Probably this isn't the answer you're looking for. However, I suggest to use a library to write csv, e.g. the one from apache commons. It has default CSVFormat-s, e.g. one for Excel. This lib is very configurable so it's relatively easy to customize it.

Generally, I suggest to use 3rd party libraries for common tasks.

This is a code snippet on how to use apache commons csv lib:

    List<String> record = Arrays.asList("acNumber","Date","Code");
    StringBuilder builder = new StringBuilder();
    try (CSVPrinter csvPrinter = new CSVPrinter(builder, CSVFormat.EXCEL)) {
        csvPrinter.printRecord(record);
    }

Thanks, i got solution of it. I was ignoring '='. :( – Narendra Pandey Aug 09 '16 at 08:12 — Narendra Pandey, Aug 09 '16 at 08:12

score 0 · Answer 4 · answered Aug 09 '16 at 08:09

I got it fixed.

IT WAS FOOLISH OF ME to ignore "=" in front of my field. I was trying to find solution without considering it.Later i consider it is as part of my value and came to know why its is being used.Found solution here. I reformat the value as below -

"=""432234,23423"""

Including comma in CSV file

4 Answers4