Skip to content

Instantly share code, notes, and snippets.

@ivanionut
Forked from JamoCA/CSVtoQuery.cfm
Last active May 23, 2023 16:56
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save ivanionut/eb31c3226266ee2a8e6a to your computer and use it in GitHub Desktop.
Save ivanionut/eb31c3226266ee2a8e6a to your computer and use it in GitHub Desktop.
Convert CSV File to Coldfusion Query Object using ColdFusion & opencsv (Java)
<!---
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#">
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment