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#"> | |
NOTE: This is a Tumblr repost from 2013.
Top comments (0)