Using ColdFusion to Parse CSV via JavaLoader and OpenCSV

Fri, 21 May 2010 09:12:00 -0700

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



	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();

Parsing CSV With URL and InputStreamReader



	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();

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.

I've Decided To (try to) Learn Java

Sat, 27 Dec 2008 08:15:00 -0700

As the new year approaches, I'm adding "Learn Java" to my personal list of things to do in 2009. I've been wanting to pick up some Java skills for awhile now and never really set it as a priority. Don't worry, I have no plans to abandon ColdFusion. Quite the contrary really.

In my school days, I was required to learn a foreign language. Victims of "location" and "school budgets," each grade school within my childhood hometown of Akron, Ohio offered one foreign language option and mine happened to be Spanish. (Friends at other schools were able to learn French, or even German.) Eventually, in high school, Latin was also offered as a foreign language option. To my excitement, I took both Latin and Spanish. In the end, I truly believe Latin helped me in not only my Spanish, but also my English! (Which some of my readers might take argument with, to be sure.) Anyway, as it turned out, Latin was/is the basis of approximately 60% of the English language. In addition, Spanish, French, and many other languages are also based on Latin in some form or another, so it's no wonder that picking up Latin skills could in turn help one in other languages as well.

As most users of ColdFusion know, Java (or J2EE) is the underlying engine that drives ColdFusion. So in much the same way that I felt Latin helped me in my English and Spanish, I'm hoping Java will also help me in not only my ColdFusion programming, but in other areas as well (object oriented code, etc.).

I understand that learning a language is an ongoing process and never really "ends." Heck, I'm still learning ColdFusion even though I've been using it since CF5. I also understand that I'll probably never really achieve "greatness" in Java considering the fact that I personally know of people that have spent their full four years in college learning this extremely complex language.