Having recently improved the support for reading CSV files in the Spreadsheet CFML library, it seemed appropriate to also look at the other side of the coin: generating CSV from CFML data.

The library already lets you do this via the queryToCsv() method, but it has limitations, not the least of which is that it will only convert a query object.

Queries may be the most common type of data object in CFML, but arrays are simpler, more universal, and possibly more performant so it would be good to support those as an input.

And these days CFML engines can also return database queries as arrays of structs, where each row in the array is a struct with the query column names as keys, for example:

sql = "SELECT ID, title FROM posts ORDER BY datePublished DESC"
myArrayOfStructs= QueryExecute( sql, {}, { datasource: "blog", returnType: "array" } )

writeCsv()

Enter a new method with version 3.12.0: writeCsv() which will take data in any of these three forms and either return a CSV string...

spreadsheet = New spreadsheet.Spreadsheet();
csv = spreadsheet.writeCsv()
  .fromData( myData )
  .execute()

...or write it directly to a file.

spreadsheet = New spreadsheet.Spreadsheet();
spreadsheet.writeCsv()
  .fromData( myData )
  .toFile( myCsvFilePath )
  .execute()

Including column names/headers

queryToCsv() gives you the option of including the query column names as the first "header" row of the CSV and the new method supports that too.

spreadsheet = New spreadsheet.Spreadsheet();
csv = spreadsheet.writeCsv()
  .fromData( myQuery )
  .withQueryColumnsAsHeader()
  .execute()

Or if you're returning an array of structs...

spreadsheet = New spreadsheet.Spreadsheet();
csv = spreadsheet.writeCsv()
  .fromData( myArrayOfStructs )
  .withStructKeysAsHeader()
  .execute()

Other options

Unlike queryToCsv() though, writeCsv() takes full advantage of the fact that it's a wrapper for the Apache Commons CSV java library and gives you easy access to its range of formatting options. This means you can configure the delimiter, quote or escape characters to use, as well as various other advanced settings.

Parallel peril

Finally, as long as your CFML engine is recent enough to support it, writeCsv() gives you the option of using multiple processor threads to build your CSV in parallel.

spreadsheet = New spreadsheet.Spreadsheet();
spreadsheet.writeCsv()
  .fromData( myBigData )
  .toFile( myCsvFilePath )
  .withParallelThreads( 4 )
  .execute();

This may speed things up.

Or it it may not. And it will almost certainly mean your rows are written out of order.

This is very much an experimental feature in the sense that you should only use it in production after you have experimented with it. In my experience, parallel processing can indeed improve performance, but it can also seriously degrade performance if used inappropriately.

You have been warned!

writeCsv() documentation