Lucee Developer

Ever had to work with CSV files? It stands for Comma Separated Values, and although the file format looks incredibly simple, it can be a pain to work with. An example of CSV data:

firstname,lastname,"lastname, firstname",age,description
Paul,Klinkenberg,"Klinkenberg, Paul",31,"CFML junkie, father"
Luce,Klinkenberg,"Klinkenberg, Luce",0,"Main actions during the day:
- sleeping
- drinking
- crying
- creating dirty diapers"
"Emma ""my love""",Klinkenberg,"Klinkenberg, Emma ""my love""",30,"Perfect :-)"

Now how would you parse this data? Line by line probably, and then do a ListToArray(line, ","). But that would not work in this case, because you would get 5 items instead of 4. And you would get 8 rows, instead of 4. These caveats, and some other troubles, make sure that CSV files can be pretty hard to parse. But not anymore!!!

Thanks to Ben Nadel's extensive research into CSV parsing, and a bit of extra coding and tweaking on my part, you can now start using <cfcsv> in Railo!

Usage

The tag has 2 actions:

• Parse: can take a file path or a string, and returns the results as a query (default), or as an array of arrays.
• Create: takes a query, and returns a string of CSV data.

Attributes

Examples

The output for these examples can be viewed here.

<!--- Parse a csv file, and return the resulting query into 'variables.myQuery'.
Also note the relative file path usage! --->
<cfcsv action="parse" file="testdata.csv" variable="myQuery" delimiter=";" />
<cfdump eval=myQuery />

<!---  some test data, ending with extra line breaks --->
<cfset myData = "test,test2
data 1,data 2
'quoted here, with a
line break',second col

" />
<!--- Parse a string of CSV data. The text qualifier is a single quote --->
<cfcsv action="parse" data="#myData#" variable="parsedDataQuery" textqualifier="'" />
<cfdump eval=parsedDataQuery />

<!--- Create CSV data from a query, returning it as 'variables.csvData' --->
<cfcsv action="create" query="#parsedDataQuery#" variable="csvData" />
<pre><cfoutput>#htmleditformat(csvData)#</cfoutput></pre>

<!---  Create CSV data from a query, without a first line of headers (query column names) --->
<cfcsv action="create" query="#parsedDataQuery#" variable="csvData" includeColumnNames=false />
<pre><cfoutput>#htmleditformat(csvData)#</cfoutput></pre>

<!--- Create CSV data from a query, using non-default delimiter and text-qualifier --->
<cfcsv action="create" query="#parsedDataQuery#" variable="csvData" delimiter="|" textqualifier="'" />
<pre><cfoutput>#htmleditformat(csvData)#</cfoutput></pre>

<!--- Parse a csv file, returning it as an array of arrays --->
<cfcsv action="parse" file="testdata.csv" variable="myArray" output="array" delimiter=";" />
<cfdump eval=myArray />

<!--- Return results as an array of arrays, and not removing optional trailing line breaks.
This will result in a few extra empty arrays, because every line will be seen as a row of data. --->
<cfcsv action="parse" file="testdata.csv" variable="myArray" output="array" trimendoffile=false delimiter=";" />
<cfdump eval=myArray />

<!--- Not removing optional trailing line breaks.
This will result in a few extra query rows, because every line will be seen as a row of data. --->
<cfcsv action="parse" file="testdata.csv" variable="myQuery" output="query" trimendoffile=false delimiter=";" />
<cfdump eval=myQuery />

<!--- Do not use first line as headers. This will give the query column names called 'col1', 'col2', etc. --->
<cfcsv action="parse" file="testdata.csv" variable="myQuery" hascolumnnames=false delimiter=";" />
<cfdump eval=myQuery />

Change log

Version 1.1, sept. 22 2011: Changed the way the regex pattern is built, so we can also use tabs /spaces as delimiters.

Version 1.2, March 2015:

• Lucee support;
• optional parameter "trimlines" in Parse function;
• timestamps are now formatted as yyyy-mm-dd HH:mm:ss, and dates as yyyy-mm-dd, instead of the curly brace notation of {d '2010-01-02'}
• optional parameter "charset" in Parse function, to read the file with the correct character set

Installation

You can install the custom tag by using your Railo server admin.

Go to your Server admin (default is http://localhost/railo-context/admin/server.cfm), click on Extension > Applications, and install the CFCSV custom tag from the Railo Extension Store.

Lastly, you have to restart Railo to be able to use the tag.

The source code

You can view all source code on GitHub, at https://github.com/paulklinkenberg/lucee-cfcsv. It mainly consists of:

• cfcsv.cfc: the code that does all the hard work
• CSV.cfc: the Railo custom tag which invokes cfcsv.cfc

Thanks!

A big thank you goes out to Ben Nadel and Steven Levithan, who wrote the CSV parsing code. Ben never stops to amaze me with his incredible amount of blog posts and researches into all kinds of Coldfusion related subjects. You rock dude!

Questions? Complaints?

Leave a message; I appreciate it!