CODEWITHSUNDEEP
javaexcelcsv
user2903064
user2903064

Reputation: 1

formatting csv columns from java

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();
            }
        }
    }

Upvotes: 0

Views: 3079

Answers (2)

tom
tom

Reputation: 2714

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.

Upvotes: 0

roxolid
roxolid

Reputation: 135

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.

Upvotes: 1

Related Questions