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.

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
The stylish and generous black http://www.bestukwatches.co.uk panel has been specially treated to present a http://www.replicawatches0.co.uk delicate embossed pattern, which is visually distinct from the http://www.replicawatchesshop.co.uk outer circle of the second, making it easier to read. Both the hour markers and the willow-shaped hands http://www.replicawatchess.uk.com are in rose gold, showing quality and luxury.
# Posted By rolex replica watches | 7/23/18 2:07 AM
Wig female long https://www.humanhairwig.org.uk curly hair, big waves fluffy, natural https://www.hair-extensions2go.co.uk sweet, shaved face, net https://www.finesthairextensions.co.uk red cute Korean air bangs, natural https://www.moptopz.co.uk realistic wig set.
# Posted By hair extensions | 8/30/18 2:47 AM
The Knovva Academy understands the need for online high school classes, as is all set to combat the post-pandemic circumstances by the virtual model G20 summit.
# Posted By K12 school | 11/17/20 2:38 PM
Null’s Clash is an unofficial private server of the Clash of Clans game. Our server constantly receives update, as the official game. Here you can fight in online battles and participate in clan wars. There are no restrictions for you, as an unlimited of gems with resources are accrued.
# Posted By Nulls Clash APK | 3/1/21 3:04 AM
Nice post paying little character to I was considering whether you could make a little on a massively key estimation astoundingly all the all the in no way, shape or form at all, using any techniques, using all frameworks, move to concern this issue? I'd be to a central degree appreciative in the event that you could wrap up being to a surprising degree further. Regard it.
http://www.meenakshikaur.com/
http://www.thegurgaonescorts.com/
http://www.hotgurgaoncallgirls.com/

# Posted By Kavya Arora | 4/17/21 7:32 AM
The writers of Writing Service design each writing assignment so that it has a clear purpose connected to the learning objectives for the reader. It is a great way to serve the community. Hats off to team InstaDissertation.
# Posted By Do My Dissertation | 11/4/21 2:13 PM

© 2023, Stephen J. Withington, Jr.  |  BlogCFC was created by Raymond Camden – Version 5.9.004

Creative Commons License  |  This work is licensed under a Creative Commons Attribution 3.0 Unported License.  |  Hosted by Hostek.com