Using ColdFusion to Parse CSV via JavaLoader and OpenCSV

Posted At: May 21, 2010 9:12 AM

Posted By: Stephen Withington

Related Categories: ColdFusion, Java, Mura CMS

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.

Comments (6)

Comments

The Http tag can read csv just fine

<cfhttp url="http://localhost:8500/NY_NJ_PA_Combined-ColumnsSyn...;
method="GET"
name="qStockItems"
delimiter=","""
firstrowasheaders="yes"
columns="Company,Contact,blank1,Phone,PhoneExt,fax,email,pager,homephone,cellphon,altphone,Address1,Address2,City,State,Zip,blank2,Title">
</cfhttp>

# Posted By Dan Plesse | 8/30/10 11:19 AM

Nice post. Helped me out :)

# Posted By Jeff | 2/16/11 12:58 PM

thanks for the post! I got it to work but I'm having a little problem. My program is a loop and the java object does not release resources... so I can not manipulate the files during the program. I see that there is a close() function but I don't know how to implement it... can you help... here is my script:

<cfscript>
fileReader = createobject("java","java.io.FileReader");
fileReader.init("c:/inetpub/sfnet/t1_portfolio/gg_parsing/client/workingDir/#DirTxt.name#");
csvReader = createObject("java","au.com.bytecode.opencsv.CSVReader");
csvReader.init(fileReader, "#chr(9)#");
</cfscript>

Thanks

# Posted By Wayne | 5/16/11 8:37 PM

csvReader.close();

# Posted By dan | 5/16/11 8:56 PM

Great post. This help a ton.

# Posted By Matt Graf | 12/19/12 9:53 PM

It doesn't work if files is in https

# Posted By Alec | 7/25/14 11:05 AM