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.

How to Remove WWW from the URL in Mura CMS with ColdFusion

Recently, a Mura CMS user asked how to remove the 'www' from the URL. So I thought I would whip up a quick post on how to do it.

  1. Login to the Admin and go to your 'Site Settings' (top-right on yellow toolbar).
  2. Select the site you wish to enforce this rule on.
  3. On the 'Basic' tab, make sure you have 'yourdomain.com' in the 'Domain' field.
  4. Also, make sure you list 'www.yourdomain.com' in the 'Domain Alias List' text area.
  5. Click 'Update'
  6. Now we'll edit a file that would probably be included on each page in your site such as \{siteid}\includes\themes\merced\templates\inc\html_head.cfm
  7. Copy and paste the code below into the top of the file that is located on each page:


<cfscript>
    myDomain = "yourPreferredDomain.com";
    domainIsCorrect = true;
    if ( getPageContext().getRequest().getServerName() neq myDomain ) {
        domainIsCorrect = false;
        urlstr = "http://" & myDomain & getPageContext().getRequest().getRequestURI();
        if ( len(trim(getPageContext().getRequest().getQueryString())) ) {
            urlstr = urlstr & "
?" & getPageContext().getRequest().getQueryString();
        };
    };
</cfscript>
<cfif not domainIsCorrect><cflocation url="
#urlstr#" addtoken="false" statuscode="301" /></cfif>

That's it! Enjoy.

Launched New Online Presence for Family Optical Centre Powered by ColdFusion + Mura CMS

Family Optical Centre, Inc. has officially launched their first ever online presence at www.familyopticalcentre.com. Family Optical Centre has been a part of the Rockford-area community for over forty-five years and currently operates three locations throughout the area. If you're in the market for some new frames and/or lenses, you might be interested in taking advantage of some of their Special Offers too.

The site is powered by Adobe® ColdFusion® and Microsoft® SQL Server with online content management provided via Mura CMS. Talented artist and designer Greg L. provided an elegant, yet simple design which I quickly and easily converted into HTML, CSS and Mura CMS templates.

Congratulations to the team at Family Optical Centre on your new online presence. Best wishes for continued success!

Family Optical Centre
Designer: Greg L. | Developer: Steve Withington

More Entries

© 2024, Stephen J. Withington, Jr.  |  Hosted by Hostek.com

Creative Commons License   |   This work is licensed under a Creative Commons Attribution 3.0 Unported License.