Convert CSV File to Coldfusion Query Object using ColdFusion & opencsv (Java)

I encountered some problems with ColdFusion 8/9/10 reading a CSV file that was created using Excel. The only solution that worked involved using opencsv, a Java parsing library. Here's the CFML code that I used to convert the results (a 2 dimensional array) to a ColdFusion query. (NOTE: OpenCSV is also the best solution if your Excel file has multi-line data in columns.)

	<!---
	Convert CSV file to a ColdFusion query object using opencsv.
	Requirements:
	- ColdFusion 8+ ( http://en.wikipedia.org/wiki/Adobe_ColdFusion )
	- opencsv - free parser library for Java ( http://opencsv.sourceforge.net/ )
	http://opencsv.sourceforge.net/
	opencsv supports all the basic csv-type things you're likely to want to do:
	- Arbitrary numbers of values per line
	- Ignoring commas in quoted elements
	- Handling quoted entries with embedded carriage returns (ie entries that span multiple lines)
	- Configurable separator and quote characters (or use sensible defaults)
	- Read all the entries at once, or use an Iterator style model
	- Creating csv files from String[] (ie. automatic escaping of embedded quote chars)
	NOTE: To use opencsv in ColdFusion:
	- copy "opencsv-2.3.jar" to "ColdFusion Class Path" (in CFAdmin > Server Settings > Java and JVM)
	- Specifying custom Java library path in the Application.cfc without dynamic loading
	http://help.adobe.com/en_US/ColdFusion/10.0/Developing/WSe61e35da8d318518-106e125d1353e804331-7ffe.html
	- Use JavaLoader http://javaloader.riaforge.org/
	--->
	<!--- Configure CSV file & delimiter --->
	<cfset CSVFile = "c:\sampleCSVFile.csv">
	<cfset Delimiter = ",">
	<!--- Read file using opencsv --->
	<cfscript>
	fileReader = createobject("java","java.io.FileReader");
	fileReader.init(CSVFile);
	csvReader = createObject("java","au.com.bytecode.opencsv.CSVReader");
	csvReader.init(fileReader, Delimiter);
	ArrData = csvReader.readAll();
	csvReader.close();
	fileReader.close();
	</cfscript>
	<!--- Determine if any records exist --->
	<cfif not arraylen(ArrData)>
	<p>No data in file.</p>
	<cfexit>
	<cfelseif arraylen(ArrData) lt 1>
	<p>No records.</p>
	<cfexit>
	</cfif>
	<!--- Convert 2 dimensional array of rows & columns to a ColdFusion query --->
	<cfscript>
	GetResults = QueryNew(ArrayToList(ArrData[1]));
	Rows = arraylen(ArrData);
	Fields = arraylen(ArrData[1]);
	for(thisRow=2; thisRow lte Rows; thisRow = thisRow + 1){
	queryaddrow(GetResults);
	for(thisField=1; thisField lte Fields; thisField = thisField + 1){
	QuerySetCell(GetResults, ArrData[1][thisfield], ArrData[thisRow][thisfield]);
	}
	}
	</cfscript>
	<cfsetting enablecfoutputonly="No">
	<cfdump var="#GetResults#">

view raw CSVtoQuery.cfm hosted with ❤ by GitHub

NOTE: This is a Tumblr repost from 2013.