writeCsv - cfsimplicity/spreadsheet-cfml GitHub Wiki

Generates a CSV string or file from a data object.

writeCsv().fromData( data )[...optional configuration calls].execute();

By default the method returns an object to which further optional calls can be chained before finally calling execute() to generate the CSV string/file.

Chainable? No.

Accepted data objects

Use the .fromData() chained method to pass in the data you wish to convert to CSV. This can be:

• A query object
• An array of arrays (1 per row)
• An array of structs (1 per row)

Write to a CSV string

From an array of arrays

data = [ [ "first", "last" ], [ "Frumpo", "McNugget" ] ];
csv = spreadsheet.writeCsv()
  .fromData( data )
  .execute();

From a query

Use .withQueryColumnsAsHeader()to include the column names as the first row of the CSV.

data = QueryNew( "first,last", "VarChar,VarChar", [ [ "Frumpo", "McNugget" ] ] );
csv = spreadsheet.writeCsv()
  .fromData( data )
  .withQueryColumnsAsHeader()
  .execute();

From an array of structs

Use .withStructKeysAsHeader()to include the row struct keys as the first row of the CSV.

data = [ { first: "Frumpo", last: "McNugget" } ];
csv = spreadsheet.writeCsv()
  .fromData( data )
  .withStructKeysAsHeader()
  .execute();

Writing to a CSV File

You can write your data directly to a CSV file.

data = QueryNew( "first,last", "VarChar,VarChar", [ [ "Frumpo", "McNugget" ] ] );
spreadsheet.writeCsv()
  .fromData( data )
  .toFile( pathToYourFile )
  .withQueryColumnsAsHeader()
  .execute();

Specifying predefined formats

Predefined Formats are preset Commons CSV configuration combinations optimized for for different contexts, such as tab-delimited data.

data = [ [ "first", "last" ], [ "Frumpo", "McNugget" ] ];
csv= spreadsheet.writeCsv()
  .fromData( data )
  .withPredefinedFormat( "TDF" )
  .execute();

If not specified the EXCEL predefined format will be used.

Other configuration options

withAllowMissingColumnNames( boolean )

Whether to allow missing column names in the header line. Default: true. Commons CSV documentation

withAutoFlush( boolean )

Whether to flush on close. Default: true. Commons CSV documentation

withCommentMarker( required string marker )

Sets the comment start marker to the specified character. Commons CSV documentation

withDelimiter( required string delimiter )

Sets the delimiter character. Commons CSV documentation To set tab as the delimiter you can use any of the following values as parameters: "#Chr( 9 )#", "\t", "tab", "TAB"

withDuplicateHeaderMode( required string value )

Sets the duplicate header names behavior. Possible values: "ALLOW_ALL", "ALLOW_EMPTY", "DISALLOW". Commons CSV documentation

withEscapeCharacter( required string character )

Sets the escape character. Commons CSV documentation

withHeader( required array header )

Manually sets the row values which will be detected as the header. To auto-detect the header from the first row, use withFirstRowIsHeader() (see above) Commons CSV documentation

withHeaderComments( required array comments )

Sets the header comments set to the given values. The comments will be printed first, before the headers. Commons CSV documentation

withIgnoreEmptyLines( boolean )

Sets the empty line skipping behavior, true to ignore the empty lines between the records, false to translate empty lines to empty records. Default: true. Commons CSV documentation

withQuoteCharacter( required string character )

Sets the quote character. Commons CSV documentation

withQuoteMode( required string value )

Sets the quote mode. Possible values: "ALL", "ALL_NON_NULL", "MINIMAL", "NON_NUMERIC", "NONE". Commons CSV documentation

withTrailingDelimiter( boolean )

Sets whether to add a trailing delimiter. Default: true. Commons CSV documentation

withTrim( boolean )

Sets whether to trim leading and trailing blanks. Default: true. Commons CSV documentation

Using parallel threads

If your CFML engine supports parallel loop processing (Lucee 5+, ACF2021+) you can specify how many threads you would like to use to construct the CSV in parallel. WARNING: Using this option can have unexpected results such as rows out of order or system crashes. Use with care.

spreadsheet.writeCsv()
  .fromData( mybigdata )
  .toFile( myfilepath )
  .withParallelThreads( 4 )
  .execute();