Liquibase® version 3.8.5 is now available! Get it for free.
2018 XML Announcement
News All Previous Posts >>

Subscribe for email updates

- and/or -

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.

Available Attributes

NameDescription
catalogNameName of the catalog@ v3.0E.g. 'cat'
commentLineStartsWithLines starting with this character are treated as comment and ignored.Default: '#'
encodingEncoding of the CSV file (defaults to UTF-8)Default: 'utf-8'
fileCSV file to loadE.g. 'com/example/users.csv'
quotcharThe quote character for string fields containing the separator character.Default: '"'
relativeToChangelogFilebooleanWhether the file path is relative to the root changelog file rather than to the classpath.
schemaNameName of the schemaE.g. 'public'
separatorCharacter separating the fields.Default: ','
tableNameName of the table to insert data intoE.g. 'person'
usePreparedStatementsbooleanUse prepared statements instead of insert statement strings if the DB supports it

Nested Properties

NameDescription
columns / column [0..N]CSV -> table column mapping can be defined. Either the 'header' or 'index' attribute needs to be defined for columns if the header name in the CSV is different than the column needs to be inserted If no `column` defined at all, header names has to match the column names in the table.The column type it is taken from the DB. Otherwise for non-string columns the type definition might be requiredNote: columns tag not required in XML
Attributes
name Name of the column.
type Data type of the column. Its value has to be one of the LOAD_DATA_TYPE
header Name of the column in the CSV file from which the value for the column shall be taken if it's different from the column name.
Note: Ignored if index is also defined.
index integer Index of the column in the CSV file from which the value for the column shall be taken
<changeSet author="liquibase-docs" id="loadData-example">
    <loadData catalogName="cat"
            commentLineStartsWith="/"
            encoding="UTF-8"
            file="com/example/users.csv"
            quotchar="'"
            relativeToChangelogFile="true"
            schemaName="public"
            separator=";"
            tableName="person"
            usePreparedStatements="true">
        <column header="header1"
                name="id"
                type="NUMERIC"/>
        <column index="3"
                name="name"
                type="BOOLEAN"/>
    </loadData>
</changeSet>
changeSet:
  id: loadData-example
  author: liquibase-docs
  changes:
  - loadData:
      catalogName: cat
      columns:
      - column:
          header: header1
          name: id
          type: NUMERIC
      - column:
          index: 3
          name: name
          type: BOOLEAN
      commentLineStartsWith: /
      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",
    "changes": [
      {
        "loadData": {
          "catalogName": "cat",
          "columns": [
            {
              "column": {
                "header": "header1",
                "name": "id",
                "type": "NUMERIC"
              }
            },
            {
              "column": {
                "index": 3,
                "name": "name",
                "type": "BOOLEAN"
              }
            }]
          ,
          "commentLineStartsWith": "/",
          "encoding": "UTF-8",
          "file": "com/example/users.csv",
          "quotchar": "'",
          "relativeToChangelogFile": true,
          "schemaName": "public",
          "separator": ";",
          "tableName": "person",
          "usePreparedStatements": true
        }
      }]
    
  }
}

Database Support

DatabaseNotesAuto Rollback
DB2/LUWSupportedNo
DB2/zSupportedNo
DerbySupportedNo
FirebirdSupportedNo
H2SupportedNo
HyperSQLSupportedNo
INGRESSupportedNo
InformixSupportedNo
MariaDBSupportedNo
MySQLSupportedNo
OracleSupportedNo
PostgreSQLSupportedNo
SQL ServerSupportedNo
SQLiteSupportedNo
SybaseSupportedNo
Sybase AnywhereSupportedNo