Pwned: CSV export/import with Grails

Yesterday I woke up to face an awful requirement: a site administrator needs the ability to download the contents of just about any table to CSV, edit them, and then re-upload the CSV. Seriously – why’d we bother building an application?

Nine hours of work later, I’m done. This post isn’t about the “how” – it’s about where and why Grails made this easier than I think I could do it in other languages.

(Actually, it makes sense: the application is focused on reporting on thousands of rows of numerical information. Editing them one at a time through a Web interface is awful).

The Theory

In theory, it’s all pretty easy:

  • The export should send out a CSV file that:
    1. Includes the primary key of a row.
    2. Excludes date_created and date_updated.
    3. For relationships, includes the foreign key value.
  • On import, it should:
    1. Update when a row has a primary key
    2. Insert when the primary key column is an empty string
    3. Delete when the primary key column starts with “-”
    4. Look up associated records by the provided foreign key and save relationships
    5. Provide decent error messages
  • I mean, no real worries, right? Some model/database metadata to deal with, but otherwise, pretty 101.

    Heh.

    Here’s where Grails helped out when I got down to the nitty-gritty, contrasted with raw J2EE and ColdFusion. (I don’t know enough about Rails, Play, or PHPland to make real comparisons, but I’d love to find out.)

    Problem 1: Exporting to CSV reliably

    Comma-delimited CSV sounds simple. However, you’ve got some weirdnesses to deal with:

    1. Values may contain a comma. Arrrrgh!
    2. Most people open CSV in Excel, which LOVES to munge datetime values into unusable goop.

    Here’s how Grails helped:

    CSV Plugin

    There’s a prebuilt CSV plugin for Grails. It’s focused on Export, but it’ll pull in all of the OpenCSV library, giving you a CSVWriter. All you have to do is instantiate it, write arrays of strings to it, and ask it to flush itself to the response.

    Bam, done, reliable CSV export that’s qualified by quotation marks, escaped nicely, and all that jazz.

    With J2EE, I could get OpenCSV and use it, but that’d mean dealing with Maven. That’s fine and dandy on my development laptop, but…all I need to say is “Maven administration overhead involved in corporate-controlled proxy repositories” and you either 1) get it and run or 2) don’t get it and run.

    Over in ColdFusionland, I’d probably either by downloading and using OpenCSV or writing my own string parsers. That means either 1) dealing with Java from ColdFusion, which is like saying you have a good relationship with someone even through you only talk through the plumbing connecting your jailcells, or 2) writing string parsing and hoping you can get all the formats correct.

    Winner: Grails plugins and simplified dependency resolution.

    Dealing with dates

    Ok, this isn’t Grails-specific. new SimpleDateFormat(“yyyy-MM-dd’T'HH:mm:ss.SSSZ”). Done. Just as easy in JEE, and usable from ColdFusion.

    Winners: Grails and JEE. In ColdFusion, something would go wrong with passing data to Java. It always does, and for some funky reason, and always on line 1,454 of file for some odd reason dealing with UNIX vs. DOS carriage returns or the like.

    Dealing with uploaded files

    I have to allow the user to upload a file and get to its contents.

    In Grails, that means I just do “request.getFile(‘formFieldName’).” ColdFusion’s just as easy, but with its usual reverse polish syntax: ““. Java…readers and lengths and bytes…oh my. There’s an entire Commons project devoted to it (which happens to be what Grails uses).

    Winner: Grails, because its syntax isn’t from 1998.

    Parsing the uploaded content

    To compare apples to apples, I’m going to compare using Grails (Hibernate), JEE on Hibernate, and ColdFusion ORM (which is…Hibernate).

    In any of the above, grabbing the model to update/delete is easy: I can ask for it by id. No worries.

    Now comes the fun part: I need to convert the uploaded CSV data, which is String, to the proper types.

    Simple values

    ColdFusion’s surprisingly bad for this: if you’re using typed properties for Hibernate, you’ve got to figure out how to convert them. Alternatively, you can leave your properties untyped and hope they cast to their appropriate Hibernate types at persistence time. All in an untyped language. I’ve gone there when I’ve tried to write generic bean populators for ORM-managed CFCs, and it’s just no fun.

    JEE fairs a little better: you’ve got to convert to types, and you can use either reflection or the Hibernate metadata to figure out what types are appropriate for each property. Both APIs are fairly miserable.

    Grails does nicely: its metadata is intended for normal humans of less than Gavin King-level-intelligence to comprehend. Look up the property, snag its type, and use the handy Groovy “as” operator to do a cast.

    Winner: Grails, again on the humanity of its API.

    Related values

    I’ve got foreign key values in my CSV, and I need to transmogrify them into their appropriate related instances. The crux of this is that for a given property, I need to figure out which entity is related.

    In JEE, it’s back to Hibernate metadata. It’s no fun, because it’s a damn labyrinth in there.

    ColdFusion and Grails both do nicely: ColdFusion’s getMetadata( SomeEntity ) gives a really human-readable map of metadata. In Grails, you’ve got the GrailsDomainClass for the entity, which describes the types of its properties very simply.

    Winner: Grails, but by just a smidge. Instead of giving you a string for the related type, it actually gives you the Class reference.

    Validating content

    Once I’ve populated a model for saving, I need to validate its content.

    In JEE, I’d be using bean validation. It’s pretty easy: I can simply say “Hey, is this thing valid?” and get a logical response.

    In Grails, it’s largely the same (guess what it uses under the hood?).

    In ColdFusion – no such luck. There’s validation available on setters, but it looks like it throws exception. On our remaining ColdFusion projects, we’re still using third-party validations that don’t tie to Hibernate.

    Winners: Grails and JEE, because I can state my validation rules directly in the model and they’re instantly tied to the persistence tier.

    Collecting errors and notifying the user

    If I hit an error, I’ve got two things I really want to do:

    1. Make sure nothing in the database changes
    2. Inform the user which line contained an issue

    Rolling back changes

    In Grails, if I write this code in a Service, I’ve got a class that’s instantly testable, integrated with Spring, and implicitly transactional. In other words, I don’t have to do anything.

    In JEE, I need to understand/apply transaction demarcation. That’s easy if you’ve got a project already set up for it. If you don’t…have fun.

    In ColdFusion, I can wrap things in tags and call it a day.

    Winner: Grails, because it does the obvious implicitly.

    Informing the user

    Any decent framework in any platform has a way to do a redirect that allows you to pass some state, like a message, via session.

    I’d call it a tie.

    Conclusion

    I could’ve gotten the job done in any language, but Grails just made it…nice. I was able to focus on the logical problems at hand, and never found myself fighting the tools I was using. That may seem like a simple concept, but it’s rare.

    Joe Rinehart's been developing software for Web, mobile, and desktop since 1998. While he mainly now works in Java, Grails, and HTML5, he has a long history of community involvement in the Flash, Flex, and ColdFusion space. As a published author and award-winning speaker, he's bringing his skills to CompileDammit to help users new to Grails and Java. When he's not coding, he's either spending time with his family or feeding an appetite for endurance mountain bike racing. (8/9/2012 update: He's now either spending time with his family or getting over a huge knee injury caused by endurance mountain bike racing.)

    Posted in ColdFusion / CFML, Grails for CFML Developers, Groovy/Grails
    2 comments on “Pwned: CSV export/import with Grails
    1. Any reason why you didn’t do it client side? You mentioned not wanting to edit a record at a time, ala the tradition list->detail UI, but what about a grid based format (like jqGrid)?

    2. Joe Rinehart says:

      Hey Ray,

      Grids were brought up, but they need to import our CSV’s into Access and some other tools, then do some munging, etc., then re-export back to CSV.

    Leave a Reply

    Your email address will not be published. Required fields are marked *

    *

    You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>