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

Our

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. – Baccarat

Answer 1

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

  }
}

Stony

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

Yes. #14068343 – Stony

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

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

Answer 2

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

Mayst

Answer 3

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

Scorcher

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

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

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 – Afterbody

Answer 4

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

Possum

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