Using ColdFusion to Parse CSV via JavaLoader and OpenCSV
Recently I needed a quick and easy way to parse a CSV file with ColdFusion, and while there are a few projects floating around out in the wild, I had used OpenCSV in the past and remembered how easy it was to use.
While I've seen a few examples for ColdFusion users on how to parse and read a CSV file with OpenCSV, they've all used Java's FileReader to do it. This meant you had to have the file stored on your server somewhere and then get the full path to its location. For example, C:\csvfiles\sample.csv. In addition, most all of the examples I've found assumed you had OpenCSV installed somewhere in your server's classpath.
Unfortunately, I couldn't rely on this method for a number of reasons. The primary reason was because I was building this as a plugin for Mura CMS. So, if it's going to be a plugin, I can't just assume everyone has OpenCSV installed. In addition, Mura offers three different file storage options: 1) locally, 2) Amazon S3 and 3) database. While we could easily use Java's FileReader method with the first option, the other two would bomb.
My first stroke of luck was that Mark Mandel contributed a nifty little project called JavaLoader to the ColdFusion community awhile back. I was also fortunate because Mura offers a way to serve most of its files via the URL. So, using a wee bit of Java and JavaLoader, I can read in the URL of a CSV file in much the same way as the FileReader method.
So for completeness, let's look at both options and then you can decide which one would work best for you.
sample.csv
You can use any csv file that you want to. This is one I put together for my recent project.
LocationName,Lat,Lng,Address,Phone,InfoWindow,Zindex,Icon
Chicago White Sox,,,"333 W 35th St, Chicago, IL 60609",(312) 674-1000,,1,
Cleveland Indians,,,"2401 Ontario St, Cleveland, OH 44115",(216) 241-8888,,2,
Detroit Tigers,,,"2100 Woodward Ave, Detroit, MI 48201",(313) 962-4000,,3,
Kansas City Royals,,,"1 Royal Way, Kansas City, MO 64129",(816) 921-8000,,4,
Minnesota Twins,,,"351-413 5th Ave N, Minneapolis, MN 55401",(612) 659-3400,,5,
Parsing CSV With FileReader
<cfscript>
csvFile = ExpandPath("/sample.csv");
csvData = [];
// FileReader
fileReader = createobject("java","java.io.FileReader");
fileReader.init(csvFile);
// use JavaLoader to load OpenCSV
paths = [ExpandPath("/opencsv-2.2/deploy/opencsv-2.2.jar")];
loader = CreateObject("component", "javaloader.JavaLoader").init(paths);
csvReader = loader.create("au.com.bytecode.opencsv.CSVReader");
csvReader.init(fileReader);
csvData = csvReader.readAll();
// release system resources
csvReader.close();
fileReader.close();
</cfscript>
<cfdump var="#csvData#" />
Parsing CSV With URL and InputStreamReader
<cfscript>
csvUrl = "http://yourdomain.com/sample.csv";
csvData = [];
// InputStreamReader
streamUrl = CreateObject("java","java.net.URL").init(csvUrl);
streamReader = CreateObject("java","java.io.InputStreamReader").init(streamUrl.openStream());
// use JavaLoader to load OpenCSV
paths = [ExpandPath("/opencsv-2.2/deploy/opencsv-2.2.jar")];
loader = CreateObject("component", "javaloader.JavaLoader").init(paths);
csvReader = loader.create("au.com.bytecode.opencsv.CSVReader");
csvReader.init(streamReader);
csvData = csvReader.readAll();
// release system resources
csvReader.close();
streamReader.close();
</cfscript>
<cfdump var="#csvData#" />
CFDump Result
I've only scratched the surface of what OpenCSV can do for you by the way ... I'll leave it up to you on how to write CSV files and even dump out SQL tables to CSV with OpenCSV. It's pretty cool stuff!
Peace.
