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
– Beri, Commented May 6, 2015 at 10:26

Prashant G · Accepted Answer · 2023-03-25 04:26:42Z

115

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

  }
}

edited Mar 25, 2023 at 4:26

answered May 6, 2015 at 10:35

Prashant G

4,9303 gold badges38 silver badges47 bronze badges

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

4 Comments

Aqqqq Over a year ago

Would it be OK to use "pw.print" instead of "pw.write"?

Prashant G Over a year ago

Yes. stackoverflow.com/questions/14067843/…

ch271828n Over a year ago

Would it be better to use try...finally to ensure pw is closed?

Prashant G Over a year ago

it did throw an exception, but i have updated the code with "try with resources".

Clément · Accepted Answer · 2020-03-12 12:47:39Z

11

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!");
    }
}

edited Mar 12, 2020 at 12:47

Clément

34 bronze badges

answered May 25, 2016 at 15:21

Tamil veera Cholan

5121 gold badge8 silver badges21 bronze badges

Comments

ℛɑƒæĿᴿᴹᴿ · Accepted Answer · 2022-04-14 15:12:49Z

4

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.");
    }
}

edited Apr 14, 2022 at 15:12

ℛɑƒæĿᴿᴹᴿ

5,4765 gold badges42 silver badges61 bronze badges

answered Jul 15, 2019 at 8:55

Samim Aktar

491 silver badge2 bronze badges

3 Comments

Sumanth Varada Over a year ago

@suraj-rao can we get an inputstream from this ?

Samim Aktar Over a year ago

yes you can do this @Sumanth Varada but you should have to import some library

Deva Over a year ago

you must have to tell you used OpenCSV dependency or jar file. or at least show users to import classes. geeksforgeeks.org/writing-a-csv-file-in-java-using-opencsv

anil084 · Accepted Answer · 2019-12-26 06:16:36Z

0

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

answered Dec 26, 2019 at 6:16

anil084

91 bronze badge

1 Comment

David García Bodego Over a year ago

Welcome to SO! When you reply a question with just code, please explain it a little bit.

Collectives™ on Stack Overflow

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

4 Answers 4

4 Comments

Comments

3 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

3 Comments

1 Comment

Linked

Related