Complex Java object to CSV

Question

I'm trying to generate a CSV file from a rather complex Java object. The object is a Session with some attributes and a list of Strings and Messages who in turn have some attributes and a list of Comments that have some attributes.

The session class is as follows;

public class Session {

    private Long id;

    private Date startDate;

    private Date endDate;

    private List<Message> messages;

    private List<String> participants;

    public TweetSession() {
    }  

    public TweetSession(Date startDate, List<Message> messages, List<String>     participants) {
        this.startDate = startDate;
        this.messages = messages;
        this.participants = participants;
    }

    public Long getId() {
        return id;
    }

    public Date getStartDate() {
        return startDate;
    }

    public void setStartDate(Date startDate) {
        this.startDate = startDate;
    }

    public Date getEndDate() {
        return endDate;
    }

    public void setEndDate(Date endDate) {
        this.endDate = endDate;
    }

    public List<Message> getMessages() {
        return messages;
    }

    public void setMessage(List<Message> messages) {
        this.message = message;
    }

    public List<String> getParticipants() {
        return participants;
    }

    public void setParticipants(List<String> participants) {
        this.participants = participants;
    }
}

The message class is as follows;

public class Message {

    private Long id;

    private Session session;

    private Date date;

    private String participant;

    private String content;

    private List<Comment> comments;

    public Message() {
    }

    public Message(String participant, Session session, Date date, String content) {
        this.participant = participant;
        this.session = session;
        this.content = content;
        this.date = date;
        this.comments = new ArrayList<>();
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getParticipant() {
        return participant;
    }

    public void setParticipant(String participant) {
        this.participant = participant;
    }

    public String getContent() {
        return content;
    }

    public void setContent(String content) {
        this.content = content;
    }

    public List<Comment> getComments() {
        return comments;
    }

    public void setComments(List<Comment> comments) {
        this.comments = comments;
    }

    public void addComment(Comment comment) {
        this.comments.add(comment);
    }

    public Date getDate() {
        return date;
    }

    public void setDate(Date date) {
        this.date = date;
    }

    public TweetSession getSession() {
        return session;
    }

    public void setSession(TweetSession session) {
        this.session = session;
    }
}

And the Comment class;

public class Comment {

    private Long id;

    private Message message;

    private String participant;

    private String message;

    private Date date;

    public Comment() {
    }

    public Comment(String participant, Message message, String content, Date date) {
        this.participant = participant;
        this.content = content;
        this.message = message;
        this.date = date;
    }

    public String getParticipant() {
        return participant;
    }

    public void setParticipant(String participant) {
        this.participant = participant;
    }

    public Message getMessage() {
        return message;
    }

    public void setMessage(Message message) {
        this.message = message;
    }

    public Date getDate() {
        return date;
    }

    public void setDate(Date date) {
        this.date = date;
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getContent() {
        return content;
    }

    public void setContent(String content) {
        this.content = content;
    }
}   

I'm wondering if it is possible to map this to a CSV file. When I convert the session object to JSON format and convert that JSON to CSV in an online generator I get the proper output so I think it must be possible. I just don't really know how. I've tried using the net.sf.supercsv library like this;

public void generateCSV(Session session, HttpServletResponse response) throws IOException {
    String csvFileName = "session.csv";
    response.setContentType("text/csv");

    String headerKey = "Content-Disposition";
    String headerValue = String.format("attachment; filename=\"%s\"",
            csvFileName);
    response.setHeader(headerKey, headerValue);

    ICsvBeanWriter csvWriter = new CsvBeanWriter(response.getWriter(),
            CsvPreference.STANDARD_PREFERENCE);

    // Generate header for the CSV
    Field fields[] = session.getClass().getDeclaredFields();
    String[] header = new String[fields.length];
    for (int i = 0; i < fields.length; i++) {
        header[i] = fields[i].getName();
    }

    csvWriter.writeHeader(header);

    // Generate CSV content from data
    csvWriter.write(session, header);

    csvWriter.close();
}

But this will of course not give the desired result.

Can anyone point me in the right direction?

Thanks in advance,

Niels

Edit:

This is a sample session in JSON format:

{
    "id": 22,
    "startDate": 1447368081000,
    "endDate": null,
    "messages": [
        {
            "id": 10,
            "date": 1447368159000,
            "participant": "1",
            "content": "This is a message",
            "comments": []
        },
        {
            "id": 11,
            "date": 1447368168000,
            "participant": "1",
            "content": "This is also a message",
            "comments": []
        },
        {
            "id": 12,
            "date": 1447368179000,
            "participant": "1",
            "content": "This is another message",
            "comments": [
                {
                    "id": 10,
                    "participant": "1",
                    "message": "This is a comment",
                    "date": 1447368227000
                },
                {
                    "id": 11,
                    "participant": "1",
                    "message": "This is also a comment",
                    "date": 1447368234000
                }
            ]
        }
    ],
    "participants": [
        "1",
        "23"
    ]
}

When I convert this to CSV I get something like this:

Indeed starting to think (a single) CSV might not be the best approach to this problem.

Answer 1

The data you have has many 1:n dependencies in it and is not really fit for a single CSV file.

Approaches I've used or seen used for this:

• One "hybrid" CSV with Session's own data, i.e. id, startDate, endDate in first columns and then two columns for messages and participants printed as a JSON

  "123", "2015-11-17", "2015-11-18", "[{id: 345, date: ...}, {id: 789, date: ...}]", "[...]"
  

  (notice you'll need to use a good CSV library that escapes the values containing , or "s)

• Multiple CSV files - modeled like you'd model a relational database for your structure, i.e.

  • sessions.csv containing id, startDate, endDate
  • messages.csv containing id, session_id, date, ...
  • ...

  then ZIP them for a single file download

• Ask your user for a more precise specification - work to find out what they intend to do with the data, then give them a "view" of the data in a format that will enable them to read them easily - just the way database views and reports are created to give users a task-oriented view of data.

Answer 2

We used openCSV

com.opencsv.bean.BeanToCsv to achieve this

   public void exportIronData(String destinationFilePath,
        List<ExportIronDataFileFormatDTO> dataList) throws Exception {
    try {
        if (validationUtil.isNullOrEmpty(destinationFilePath)
                || validationUtil.isNullOrEmpty(dataList)) {
            return;
        }
        ColumnPositionMappingStrategy<ExportIronDataFileFormatDTO> strategy = new ColumnPositionMappingStrategy<ExportIronDataFileFormatDTO>();
        strategy.setType(ExportIronDataFileFormatDTO.class);
        String[] columns = IlmcrCsvFileConstants.EXPORT_IRONDATA_COLUMN_HEADERS;
        strategy.setColumnMapping(columns);
        CSVWriter writer = new CSVWriter(
                new FileWriter(destinationFilePath),
                IlmcrCsvFileConstants.exportIronDataSeperator,
                CSVWriter.NO_ESCAPE_CHARACTER, System.getProperty("line.separator"));
        BeanToCsv<ExportIronDataFileFormatDTO> exportFormat = new BeanToCsv<ExportIronDataFileFormatDTO>();
        exportFormat.write(strategy, writer, dataList);
        writer.flush();
        writer.close();
    } catch (Exception e) {
        e.printStackTrace();
        throw e;
    }
}

Let me know if you need anything else

Answer 3

There is a library called json2flat.
What it does is it takes a complex JSON document and converts it to CSV format.

So what you need to do is convert your java objects into JSON format. After it
you need to pass the generated JSON into the library and it returns a 2D
representation of the JSON, you can also get the csv from it.

This library is not that mature but still it's promising.
You should give it a try.

Answer 4

You should use Hybrid CSV formats. I had same problem so I created a light weight framework. You can copy source code or example from : https://github.com/abhisoni96/dev-tools

You can create your own formatter for each collection based data members. Please refer example on above link.

Answer 5

I recommend using Apache Commons CSV instead. We've used it on several projects, including managing our mailing lists.

 Reader in = new StringReader("a,b,c");
 for (CSVRecord record : CSVFormat.DEFAULT.parse(in)) {
     for (String field : record) {
         System.out.print("\"" + field + "\", ");
     }
     System.out.println();
 }

---

Output: "a", "b", "c",