Writing comma separated numbers into one cell

Question

I am generating CSV file using Java code with header fields say UserName, AccountNumbers. Now for AccountNumber header field, I need to show all account numbers as comma separated in onc cell only.

ex. abc, 10001,10002,10003

Now when I open the generated CSV file in excel, the account numbers are not displaying properly and that cell value is considered as Number, and by default Number format getting applied when opening in excel.

I applied below solutions :

1) wrapping the comma separated list in double quotes i.e. "10001,10002,10003" -- did not worked 2) wrapping the comma separated list in "=""list""" i.e. "=""10001,10002,10003""" -- problem with this wrapping is that, for some of the list it's working fine, but for some, I am getting "= appearing in start of the list. i.e. some list are already considered as text format (not sure why)

HttpServletResponse response = this.getPortalPageContext().getResponse();
response.setContentType("text/csv");
response.setHeader("Content-Disposition", "attachment;filename=user_search_export.csv");
response.setHeader("Pragma", "public");
response.setHeader("Cache-Control", "cache");
response.setHeader("Cache-Control", "must-revalidate");
Writer out = response.getWriter();
StringBuffer tmp = new StringBuffer();
tmp.append("UserName");
tmp.append("Accounts");
out.write(tmp.toString());
out.write("\r\n");

StringBuffer tmp = new StringBuffer();
String accountNo = "10001,10002,10003";
tmp.append("abc");
tmp.append("\"=\"\"" + accountNo + "\"\"\"");
out.write(tmp.toString());
out.write("\r\n");

Thanks

`Solution 1` should have worked perfectly! Can you edit the question and include the JAVA code that you have? — user2004685, Feb 26 '18 at 20:08
Try: `tmp.append("abc,"); tmp.append("\"" + accountNo + "\"");` — user2004685, Feb 26 '18 at 20:29
I had already tried this and output was --> abc 100,011,000,210,003 — Mr bond, Feb 26 '18 at 20:44

Koenig · Answer 1 · 2018-02-27T06:47:00.663

0

Why you don't use another delimiter as cell delimiter, like ';'. In the import wizard of excel you can choose this delimiter as cell delimiter only. When excel use the currency formatter for the last column, you can force text type for this column.

edited Feb 27 '18 at 06:47

answered Feb 26 '18 at 20:27

Koenig

54
4

How we can specify/use another delimiter apart from comma for CSV ? – Mr bond Feb 26 '18 at 20:34
Here are my settings for the import https://ibb.co/mE3NyH and my result on the screen https://ibb.co/e1B9dH. You said in another comment, that your end users doesn't want to import - so csv is the wrong format for your usecase. You have to write native calc-files instead. – Koenig Feb 27 '18 at 07:08
In a previous asked question https://stackoverflow.com/questions/3454975/writing-to-excel-in-java was http://poi.apache.org/spreadsheet/examples.html suggested. – Koenig Feb 27 '18 at 07:14

Ron Rosenfeld · Answer 2 · 2018-02-27T00:38:37.877

0

Placing the text qualifier around your comma-separated string, and then IMPORTing the file works as it should.

If the value is being split, be sure there is no space after that first comma (as there is in your example).

I will add some examples

Content of csv file

abc,"10001,10002,10003"

Import using Legacy Wizard

step 2

step 3

on the worksheet

edited Feb 27 '18 at 00:38

answered Feb 26 '18 at 20:52

Ron Rosenfeld

53,870
7
28
60

End users doesn't want to import.. they just want to get it opened when they double clicked. – Mr bond Feb 27 '18 at 05:45
@Mrbond That is how Excel is designed. I suggest you generate an Excel file, rather than a CSV file, if the end users desires are paramount. You could probably write a script which accomplishes the import, but they would have to click on that script, and then, from within the script, select the file. – Ron Rosenfeld Feb 27 '18 at 10:48

Writing comma separated numbers into one cell

2 Answers2