readCsv

Reads a csv file and processes and/or returns the data.

Performance-optimized for large files and offering a range of options available via the Apache Commons CSV library.

By default the method returns an object to which further optional calls can be chained before finally calling execute() to process the file and (if required) return the data.

readCsv( filepath )[...optional configuration calls].execute();

Reading a csv file into an array

filepath = "c:/temp/my.csv";
result = readCsv( filepath )
  .intoAnArray()
  .execute();

Return value

A struct with 2 keys:

1. columns: array of header/column names (empty if none specified)
2. data: array of row value arrays*

(* Note that, for performance reasons, each row value array is a native Java array rather than a CFML array.)

Specifying predefined formats

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

filepath = "c:/temp/myTabDelimited.csv";
myArray = readCsv( filepath )
  .intoAnArray()
  .withPredefinedFormat( "TDF" )
  .execute();

If not specified the DEFAULT predefined format will be used.

Other configuration options

withAllowMissingColumnNames( required boolean )

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

withAutoFlush( required boolean )

Whether to flush on close. 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

withFirstRowIsHeader( boolean state )

Whether to use the first row as the header and exclude it from being processed as part of the data. Defaults to true if no state is specified.

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

withIgnoreEmptyLines( required boolean )

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

withIgnoreHeaderCase( required boolean )

Sets the parser case mapping behavior, true to access name/values, false to leave the mapping as is. Commons CSV documentation

withIgnoreSurroundingSpaces( required boolean )

Sets the parser trimming behavior, true to remove the surrounding spaces, false to leave the spaces as is. Commons CSV documentation

withNullString( required string value )

Converts strings equal to the given nullString to null when reading records. Commons CSV documentation

withQuoteCharacter( required string character )

Sets the quote character. Commons CSV documentation

withSkipFirstRows( required numeric numberOfRowsToSkip )

Ignore the specified number of rows at the start of the file. Should be a positive integer.

withSkipHeaderRecord( required boolean )

Sets whether to skip the header record. Commons CSV documentation

withTrailingDelimiter( required boolean )

Sets whether to add a trailing delimiter. Commons CSV documentation

withTrim( required boolean )

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

Filtering rows

If you would like to exclude or only include certain rows in your CSV file from being processed you can supply a User Defined Function (UDF) which accepts the array of row values and returns true if it should be included. For example to skip any row that contains columns with the word "tobacco" in them:

var filter = function( rowValues ){
  return !ArrayFindNoCase( rowValues, "tobacco" );
};
var actual = s.readCsv( tempCsvPath )
  .intoAnArray()
  .withRowFilter( filter )
  .execute();

Important: Your function should avoid using "member functions" on the passed array of row values since it is a native Java array rather than a CFML array. For example instead of:

return !rowValues.FindNoCase( "tobacco" );

use

return !ArrayFindNoCase( rowValues, "tobacco" );

Passing a UDF/Closure to process rows

[Details to come]