Write List of Maps to a CSV

Question

I have a standalone application, which connects to a SQL database and saves ResultSet in a list of Map. This is what I have so far:

List<Map<String, Object>> rows;
stmt = conn.createStatement();
Resultset rs = stmt.executeQuery(queryString);
ResultSetMetaData rsmd; //Properties of Resultset object and column count

while(rs.next){
  Map<String, Object> rowResult = new HashMap<String, Object>(columnCount);
   for(int i =1; i <=columnCount; i++){
     rowResult.put(rsmd.getColumnName(i), rs.getObject(i));
   }
  rows.add(rowResult);
}

//WRITE TO CSV  
String csv = "C:\\Temp\\data.csv";
CSVWriter writer = new CSVWriter(new FileWriter(csv));

//Write the record to file
writer.writeNext(rows);

//close the writer
writer.close();

How do I add this "rows" of List to a csv with columns? Any clues and suggestions. Your help is appreciated.

You say you are having a hard time saving the list to a file. What have you tried? — Michael Markidis, Apr 25 '17 at 01:47
I was following this example @MichaelMarkidis : http://stackoverflow.com/questions/31172003/java-write-hashmap-to-a-csv-file — user2501165, Apr 25 '17 at 01:52
Are you trying to use a library? Can you show the code you have tried in your question. — Michael Markidis, Apr 25 '17 at 01:56

score 0 · Answer 1 · answered Apr 26 '17 at 03:14

Since every record will have the same columns in the same order, then I would just use a List<List<Object>> for the rows.

For the headers, you don't need to get them on every row. Just get them once like so:

List<String> headers = new ArrayList<>();
for (int i = 1; i <= columnCount; i++ )
{
    String colName = rsmd.getColumnName(i);
    headers.add(colName);
}

Next, you can get the rows like this:

List<List<Object>> rows = new ArrayList<>();

while(rs != null && rs.next)
{
   List<Object> row = new ArrayList<>();
   for(int i =1; i <=columnCount; i++)
   {
       row.add(rs.getObject(i));
   }
   rows.add(row);
}

Finally, to create the CSV file, you can do this:

// create the CSVWriter
String csv = "C:\\Temp\\data.csv";
CSVWriter writer = new CSVWriter(new FileWriter(csv));

// write the header line
for (String colName : headers)
{
    writer.write(colName);
}
writer.endRecord();

// write the data records
for (List<Object> row : rows)
{
    for (Object o : row)
    {
        // handle nulls how you wish here
        String val = (o == null) ? "null" : o.toString();
        writer.write(val);
    }
    writer.endRecord();
}

// you should close the CSVWriter in a finally block or use a 
// try-with-resources Statement
writer.close;

Note: In my code examples, I'm using Type Inference

See: Try-With-Resources Statement.

score 0 · Answer 2 · answered May 08 '17 at 20:09

Honestly for what you are trying to do I would recommend you use the writeAll method in CSVWriter and pass in the ResultSet.

writer.writeAll(rs, true);

The second parameter is the include column names so the first row in your csv file will be the column names. Then when you read the file you can translate that back into your Map if you want to (though it will be all strings unless you know when you are reading it what the types are).

Hope that helps.

Scott :)

Write List of Maps to a CSV

2 Answers2