formatting csv columns from java

Question

I'm trying to write a csv-file from java. Works perfectly, but when I write e.g. 19,4 to csv-file, in excel it is shown as 19. April. So I put a \' in front of 19,4. Value is shown as number now, but I have the ' in front of it. When I click into the column in excel and enter => the ' vanishes. What do I have to do, that the ' is not shown from beginning?

Here is some example source (from a servlet):

public void doWork(HttpServletRequest request, HttpServletResponse response) {

        byte[] fileData = null;
        String buffer = "";

        buffer += "First";
        buffer += ";";
        buffer += "\'";
        buffer += "19,4";
        buffer += ";";
        buffer += "\n";
        buffer += "Second";
        buffer += ";";
        buffer += "\'";
        buffer += "15,1";
        buffer += ";";
        buffer += "\n";
        buffer += "Third";
        buffer += ";";
        buffer += "\'";
        buffer += "1,7";
        buffer += ";";
        buffer += "\n";

        fileData = buffer.getBytes("UTF-8");

        byte[] fileDataWithBOM = new byte[fileData.length+10];
        fileDataWithBOM[0] = (byte)0xef;
        fileDataWithBOM[1] = (byte)0xbb;
        fileDataWithBOM[2] = (byte)0xbf;
        System.arraycopy(fileData, 0, fileDataWithBOM, 3, fileData.length);

        ServletOutputStream out = null;
        String fileName = "Accounting.csv";


        try {
            response.setContentType("text/csv");
            String disposition = "attachment; fileName="+fileName;
            response.setHeader("Content-Disposition", disposition);
            response.setCharacterEncoding("UTF-8");

            out = response.getOutputStream();

            int length = fileData.length;
            response.setContentLength(length);

            out.write(fileData);
            out.flush();
            out.close();

        } catch (Exception e) {
            throw e;
        } finally {
            try {
                if (out != null)
                    out.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

roxolid · Accepted Answer · 2013-10-21 13:14:15Z

1

It's not an CSV problem, it's Excel problem. I recommend you to use Excel's Text Import Wizard which will give you options how to format imported data.

CSV is just data representation format with no information about the formatting. The consuming application has to format it. If you need to store data + metadata together you have to choose some other format or try to 'encode' metadata somewhere in CSV file (for example, first row of CSV file can be used for this; however, I don't know any way how to instruct Excel to use it - I process all CSV with scripts and programs).

Note: I have Excel 2007.

answered Oct 21, 2013 at 13:14

roxolid

1351 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

tom · Accepted Answer · 2013-10-21 13:16:30Z

0

Is this your output?

First;\'19,4;
Second;\'15,1;
Third;\'1,7;

That doesn't look like you're generating CSV and your escaping of values is really weird. Also you're using the delimiter excel expects "," inside your numbers and a weird one to actually delimitate ";". To make it work "out of the box" it should be:

First,Second,Third
19.4,15.1,1.7

My advice is to use a CSV library such as the Jackson CSV mapper or OpenCSV. It will automatically escape your values. Also, you'd be better of creating a StringBuilder and append("vals") to it if you plan on doing large files. It's much quicker.

answered Oct 21, 2013 at 13:16

tom

2,71218 silver badges28 bronze badges

Collectives™ on Stack Overflow

formatting csv columns from java

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related