Liquibase® version 3.8.1 is now available! Get it for free.
2018 XML Announcement
News Liquibase 3.8.1 Released
06 Nov 2019

- and/or -

All Previous Posts >>

Change: ‘loadData’

Loads data from a CSV file into an existing table. A value of NULL in a cell will be converted to a database NULL rather than the string ‘NULL’. Lines starting with # (hash) sign are treated as comments. You can change comment pattern by specifying ‘commentLineStartsWith’ property in loadData tag.To disable comments set ‘commentLineStartsWith’ to empty value’

If the data type for a load column is set to NUMERIC, numbers are parsed in US locale (e.g. 123.45). Date/Time values included in the CSV file should be in ISO format http://en.wikipedia.org/wiki/ISO_8601 in order to be parsed correctly by Liquibase. Liquibase will initially set the date format to be ‘yyyy-MM-dd’T’HH:mm:ss’ and then it checks for two special cases which will override the data format string.

If the string representing the date/time includes a ‘.’, then the date format is changed to ‘yyyy-MM-dd’T’HH:mm:ss.SSS’ If the string representing the date/time includes a space, then the date format is changed to ‘yyyy-MM-dd HH:mm:ss’ Once the date format string is set, Liquibase will then call the SimpleDateFormat.parse() method attempting to parse the input string so that it can return a Date/Time. If problems occur, then a ParseException is thrown and the input string is treated as a String for the INSERT command to be generated. If UUID type is used UUID value is stored as string and NULL in cell is supported. Column config should be used in xml.

Available Attributes

NameDescriptionRequired ForSupportsSince
catalogNameName of the catalogall3.0
commentLineStartsWithall
encodingEncoding of the CSV file (defaults to UTF-8)all
fileCSV file to loadallall
quotcharall
relativeToChangelogFileall
schemaNameName of the schemaall
separatorall
tableNameName of the table to insert data intoallall
usePreparedStatementsall

Nested Properties

NameDescriptionRequired ForSupportsMultiple AllowedSince
columnsDefines how the data should be loaded.

See the column tag documentation for more information
allallyes
<changeSet author="liquibase-docs"
        id="loadData-example"
        objectQuotingStrategy="LEGACY">
    <loadData catalogName="cat"
            commentLineStartsWith="A String"
            encoding="UTF-8"
            file="com/example/users.csv"
            quotchar="'"
            relativeToChangelogFile="true"
            schemaName="public"
            separator=","
            tableName="person"
            usePreparedStatements="true">
        <column name="address" type="varchar(255)"/>
    </loadData>
</changeSet>
changeSet:
  id: loadData-example
  author: liquibase-docs
  objectQuotingStrategy: LEGACY
  changes:
  - loadData:
      catalogName: cat
      columns:
      - column:
          name: address
          type: varchar(255)
      commentLineStartsWith: A String
      encoding: UTF-8
      file: com/example/users.csv
      quotchar: ''''
      relativeToChangelogFile: true
      schemaName: public
      separator: ','
      tableName: person
      usePreparedStatements: true
{
  "changeSet": {
    "id": "loadData-example",
    "author": "liquibase-docs",
    "objectQuotingStrategy": "LEGACY",
    "changes": [
      {
        "loadData": {
          "catalogName": "cat",
          "columns": [
            {
              "column": {
                "name": "address",
                "type": "varchar(255)"
              }
            }]
          ,
          "commentLineStartsWith": "A String",
          "encoding": "UTF-8",
          "file": "com/example/users.csv",
          "quotchar": "'",
          "relativeToChangelogFile": true,
          "schemaName": "public",
          "separator": ",",
          "tableName": "person",
          "usePreparedStatements": true
        }
      }]
    
  }
}

Database Support

DatabaseNotesAuto Rollback
DB2SupportedNo
DB2SupportedNo
DerbySupportedNo
FirebirdSupportedNo
H2SupportedNo
HyperSQLSupportedNo
INGRESSupportedNo
InformixSupportedNo
MariaDBSupportedNo
MySQLSupportedNo
OracleSupportedNo
PostgreSQLSupportedNo
SQL ServerSupportedNo
SQLiteSupportedNo
SybaseSupportedNo
Sybase AnywhereSupportedNo