Liz Douglass

LiquiBase: Inserting data from a CSV file

with one comment

BA couple of weeks ago Nick pointed out to me that it was possible to insert data into a LiquiBase change set from a CSV file. This Load Data feature was added in version 1.7.0 of LiquiBase and is particularly useful for two reasons:

  1. It keeps the length of the changelog file down
  2. It’s easier to edit a CSV file than it is an XML file, especially when you’re adding a large amount of data to a table

While this is a great option there are a couple of things worth keeping an eye out for if you go down this path…

First, dates:
As I’ve written about before, we are using HSQLDB for development but need to support Oracle. We’ve had relatively few problems in working with both, except for the date compatibility issue.

When inserting XML date data into table entries we were using SYSDATE as the value. This works fine in XML, but does not work when loading data from a CSV file. In the CSV case LiquiBase attempts to create a formatted timestamp from the ‘SYSDATE’ string. We tried a number of ways to enter dates that were compatible in both HSQLDB and Oracle, including adding extra columns in the CSV to cater for the different databases (see below). We also tried updating the table date columns after inserting the entries using the CSV file, but this was messy because those columns have not-null constraints.

The solution to the date compatibility problem turns out is very simple (drumroll) – just define the date in one of the date formats in the Liquibase.util.ISODateFormat class (below) and LiquiBase will generate a database specific date for you!

yyyy-MM-dd'T'HH:mm:ss
yyyy-MM-dd HH:mm:ss
yyyy-MM-dd'T'HH:mm:ss.S

Second, columns:

The documentation for the CSV load data feature gives the impression that you need to map each column in the CSV to one in the table. In fact LiquiBase will map the CSV file column headings to table column headings automatically. You only need to map the columns that have a different heading in the CSV to that in the table. Note however that we found it was necessary to map the columns containing date data, even if the headings were the same:

    <loadData tableName="My_Table" file="My_File.csv">
        <column name="CREATED_DATE" header="CREATED_DATE" type="DATE"/>
    </loadData>

Unfortunately the automatic mapping also means that you cannot have extra columns in the CSV.

Advertisements

Written by lizdouglass

February 1, 2009 at 11:06 am

Posted in Java

Tagged with , , ,

One Response

Subscribe to comments with RSS.

  1. The reason for mapping the column name to the header in the CSV file is to be able to match the type properly, otherwise it will default to STRING

    pfschwartz

    May 18, 2010 at 9:03 pm


Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: