0

I use opencsv (2.3 version) CSVWriter to create csv file in java.

However while opening csv file I created, it is opened in such a way all the data are stored in single column.

CSVWriter I use to create CSV files:

   File file = new File(fileName);
   CSVWriter writer = new CSVWriter(new FileWriter(fileName, true), ';');
   String[] col= new String[3];

   for(Customer c : CustomerList) {
         col[0] = c.getCustomerName();
         col[1] = c.getCustomerId();
         col[2] = c.getCustomerBirthDate();
         writer.writeNext(col);
   }

   writer.close();

CSV File - all data in Single column:

"Micky";"1";"19901220"

"Grace";"2";"19901231"

all data in Single column

Expected data aligned in cells peroperly:

data aligned in cells peroperly

Questions:

  1. Is it possible to make the data to place in its separate column using CSVWriter while writing only?
  2. If it is not supported by opencsv should I use other csv?
Alagammal P
  • 829
  • 5
  • 19
  • 43
  • Can you share your code, some sample output and your expected output please? The more information we have, the better we can help you. – Matt Oct 10 '17 at 10:47
  • @Matt updated with required details – Alagammal P Oct 10 '17 at 11:26
  • 1
    Your CSV file is correct, the viewer you're using must not be recognizing your separator char. You could try to change the default separator in CSVWriter or just configure your viewer to consider ' ; ' as separator. – fn. Oct 10 '17 at 13:48
  • It is possible to have the values with comma. So if I use the default separtor the values with commas will be splilted right? – Alagammal P Oct 10 '17 at 14:33
  • 1
    @alagammal-p Yes, just remember to set the custom separator on CSVWriter constructor and configure your viewer to understand that char as separator(for example [Excel](https://superuser.com/a/606274)) – fn. Oct 10 '17 at 18:41
  • @fn, I dont want to do configure the viewer as I cannot expect the end user doing this. My concern is can it be controlled while creating the CSV?(like jasper report pdf file creation allows us to configure the file opening procedure with password, etc) or if opencsv doesnt support this, then I was looking forvany other csv(like supercsv) which supports – Alagammal P Oct 10 '17 at 20:22

2 Answers2

1

fn Has the answer to the question. You need to use the comma as a separator (which is the default) for the an external reader to pick it up automatically. The comma is the default for openCSV.

OR you can go into excel and tell it to use the semi colon as the separator. using the "Text to Columns" option.

Scott Conway
  • 975
  • 7
  • 13
  • If I use comma as delimiter, is it possible to allow some of the column values with comma? – Alagammal P Oct 11 '17 at 13:40
  • 1
    yes - if you put quotes around your data then the comma inside the quote would be taken as data not a field separator. – Scott Conway Oct 12 '17 at 23:42
  • Thank you. Im going to try this. I also face another issue in displaying special charecters like "accented e" in csv, I opened a new question https://stackoverflow.com/questions/46691554/utf-8-encoding-in-opencsv, can you please help me out? – Alagammal P Oct 13 '17 at 05:10
  • Oh and you do not need to add quotes yourself. The CSVWriter should do it for you. – Scott Conway Oct 13 '17 at 21:15
0

Try using univocity-parsers to write this in format that excel expects:

    new CsvRoutines(Csv.writeExcel())
       .writeAll(customerList, Customer.class, new File(fileName), Charset.forName("windows-1252"));

Just annotated the fields of your Customer class with @Parsed and it will work just fine.

Disclaimer: I'm the author of this library. It's open-source and free (apache 2.0 license)

Jeronimo Backes
  • 6,141
  • 2
  • 25
  • 29
  • Can I please have any reference link just for the idea related to Column separtor and other things? – Alagammal P Oct 11 '17 at 08:18
  • `Csv.writeExcel()` returns a `CsvWriterSettings` object with lots of options that you can change at will. To change the delimiter use `csvWriterSettings.getFormat().setDelimiter(DELIMITER);` – Jeronimo Backes Oct 11 '17 at 09:00
  • 1
    univocity-parsers is nice but if he changes the separator to semicolon like he did with openCSV then he would have the same issue :) – Scott Conway Oct 11 '17 at 13:12