CSV file with "ID" as first item is corrupt in Excel

Question

I am trying to write data to a csv file with java, however when I try opening the produced file with excel I am getting an error saying the file is corrupt. Upon opening the file in notepad it looks to be formatted correctly so I'm not sure what the issue is. I am using the FileWriter class to output the data to the file.

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 to use some library in java in order to print to a csv file? I presumed you could just do this natively in java as long as you used the correct formatting.

Appreciate the help,

Shaw

For starters extract a method for writing a single line, and replace writer.append('\n') with writer.newLine(). Because newLine is dependable on operating system. — Beri, May 06 '15 at 10:26

Prashant Ghimire · Accepted Answer · 2023-03-25T04:26:42.410

It's because MS Excel can't decide how to open the file with such content.

To solve the issue, replace "ID" with "id".

When you have ID as the first word in a SpreadSheet type file, it matches the specification of a SYLK file and MS Excel (and potentially other SpreadSheet Apps) attempts to open as a SYLK file. However, it does not meet the complete specification of a SYLK file since rest of the values in the file are comma separated. Hence, the error is shown.

id	Name
1	Jon Doe
2	Jane Doe

As a bonus, trying to minimize file access by using file object less.

I tested and the code below should work.

import java.io.File;
import java.io.FileNotFoundException;
import java.io.PrintWriter;

public class CsvWriter {
  public static void main(String[] args) {

    try (PrintWriter writer = new PrintWriter("test.csv")) {

      StringBuilder sb = new StringBuilder();
      sb.append("id");
      sb.append(',');
      sb.append("Name");
      sb.append('\n');

      sb.append("1");
      sb.append(',');
      sb.append("Jon Doe");
      sb.append('\n');

      sb.append("2");
      sb.append(',');
      sb.append("Jane Doe");
      sb.append('\n');

      writer.write(sb.toString());

      System.out.println("write success.");

    } catch (FileNotFoundException e) {
      System.out.println(e.getMessage());
    }

  }
}

Yes. http://stackoverflow.com/questions/14067843/difference-between-printwriter-and-filewriter-class — Prashant Ghimire, Apr 01 '17 at 17:27
Would it be better to use `try...finally` to ensure pw is closed? — ch271828n, Feb 03 '19 at 06:21
it did throw an exception, but i have updated the code with "try with resources". — Prashant Ghimire, Feb 04 '19 at 00:43

score 11 · Answer 2 · edited Mar 12 '20 at 12:47

import java.io.File;
import java.io.FileNotFoundException;
import java.io.PrintWriter;

public class CsvFile {

    public static void main(String[]args){
        PrintWriter pw = null;
        try {
            pw = new PrintWriter(new File("NewData.csv"));
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        StringBuilder builder = new StringBuilder();
        String columnNamesList = "Id,Name";
        // No need give the headers Like: id, Name on builder.append
        builder.append(columnNamesList +"\n");
        builder.append("1"+",");
        builder.append("Chola");
        builder.append('\n');
        pw.write(builder.toString());
        pw.close();
        System.out.println("done!");
    }
}

score 4 · Answer 3 · edited Apr 14 '22 at 15:12

I think this is a simple code in java which will show the string value in CSV after compile this code:

public class CsvWriter {
    public static void main(String args[]) {
        // File input path
        System.out.println("Starting....");
        File file = new File("/home/Desktop/test/output.csv");
        try {
            FileWriter output = new FileWriter(file);
            CSVWriter write = new CSVWriter(output);
            // Header column value
            String[] header = { "ID", "Name", "Address", "Phone Number" };
            write.writeNext(header);
            // Value
            String[] data1 = { "1", "First Name", "Address1", "12345" };
            write.writeNext(data1);
            String[] data2 = { "2", "Second Name", "Address2", "123456" };
            write.writeNext(data2);
            String[] data3 = { "3", "Third Name", "Address3", "1234567" };
            write.writeNext(data3);
            write.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        System.out.println("End.");
    }
}

yes you can do this @Sumanth Varada but you should have to import some library — Samim Aktar, Jul 22 '19 at 12:22
you must have to tell you used OpenCSV dependency or jar file. or at least show users to import classes. https://www.geeksforgeeks.org/writing-a-csv-file-in-java-using-opencsv/ — Deva, Jul 29 '20 at 13:00

score 0 · Answer 4 · answered Dec 26 '19 at 06:16

 private static final String FILE_HEADER ="meter_Number,latestDate";
    private static final String COMMA_DELIMITER = ",";
    private static final String NEW_LINE_SEPARATOR = "\n";
    static SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:m m:ss");

    private void writeToCsv(Map<String, Date> meterMap) {
        try {
            Iterator<Map.Entry<String, Date>> iter = meterMap.entrySet().iterator();
            FileWriter fw = new FileWriter("smaple.csv");
            fw.append(FILE_HEADER.toString());
            fw.append(NEW_LINE_SEPARATOR);
            while (iter.hasNext()) {
                Map.Entry<String, Date> entry = iter.next();
                try {
                    fw.append(entry.getKey());
                    fw.append(COMMA_DELIMITER);
                    fw.append(formatter.format(entry.getValue()));
                    fw.append(NEW_LINE_SEPARATOR);
                } catch (Exception e) {
                    e.printStackTrace();
                } finally {
                    iter.remove();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

Welcome to SO! When you reply a question with just code, please explain it a little bit. — David García Bodego, Dec 26 '19 at 06:23

CSV file with "ID" as first item is corrupt in Excel

4 Answers4

Linked

Related