What could be causing a CSV file created in Java to be corrupt in Excel?

General Discussions
1

I’m trying to create a CSV file in Java, and while the file appears to be formatted correctly when opened in Notepad, Excel throws an error saying the file is corrupt. I’m using the FileWriter class to write the data like this:

FileWriter writer = new FileWriter("test.csv");

writer.append("ID");
writer.append(',');
writer.append("name");
writer.append(',');
...
writer.append('\n');

writer.flush();
writer.close();

Do I need a special library in Java to create a CSV file, or am I missing something in the formatting? I assumed that as long as I used the correct structure, Java would handle it natively. Any advice on resolving this issue would be appreciated!

2

Excel can sometimes have trouble opening UTF-8-encoded CSV files without a BOM. You can add a BOM at the beginning of the file to ensure proper encoding. Try this:

FileOutputStream fos = new FileOutputStream("test.csv");
fos.write(0xEF);
fos.write(0xBB);
fos.write(0xBF);

Writer writer = new OutputStreamWriter(fos, StandardCharsets.UTF_8);
writer.append("ID,name\n");  // Writing header
writer.append("1,John Doe\n");  // Writing data
writer.flush();
writer.close();

:small_blue_diamond: Why this helps?

Excel sometimes misinterprets the encoding, but adding a BOM ensures it recognizes UTF-8 correctly.

3

If any of your data contains commas, Excel might misinterpret the columns. The best practice when you create a CSV file in Java is to wrap text fields in double quotes:

FileWriter writer = new FileWriter("test.csv");

writer.append("\"ID\",\"name\"\n");  // Enclosing headers in quotes
writer.append("\"1\",\"John Doe\"\n");
writer.append("\"2\",\"Jane, Smith\"\n");  // Name contains a comma

writer.flush();
writer.close();

:small_blue_diamond: Why this helps?

This ensures that Excel correctly reads each field without breaking it at unexpected commas.

4

Instead of handling CSV formatting manually, a great way to create a CSV file in Java is by using OpenCSV:

:one: First, add the dependency (if using Maven):

<dependency>
    <groupId>com.opencsv</groupId>
    <artifactId>opencsv</artifactId>
    <version>5.5.2</version>
</dependency>

:two: Then, write your CSV file like this:

import com.opencsv.CSVWriter;
import java.io.FileWriter;
import java.io.IOException;

public class CSVExample {
    public static void main(String[] args) throws IOException {
        CSVWriter writer = new CSVWriter(new FileWriter("test.csv"));
        
        String[] header = {"ID", "Name"};
        String[] row1 = {"1", "John Doe"};
        String[] row2 = {"2", "Jane Smith"};

        writer.writeNext(header);
        writer.writeNext(row1);
        writer.writeNext(row2);

        writer.close();
    }
}

Libraries like OpenCSV handle formatting, encoding, and escaping special characters automatically, making your life easier.