-
-
Save ivanionut/eb31c3226266ee2a8e6a to your computer and use it in GitHub Desktop.
Convert CSV File to Coldfusion Query Object using ColdFusion & opencsv (Java)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<!--- | |
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