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’

Date/Time values included in the CSV file should be in ISO format 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.

Available Attributes

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

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">
    <loadData catalogName="cat"
            encoding="UTF-8"
            file="com/example/users.csv"
            quotchar="A String"
            schemaName="public"
            separator="A String"
            tableName="person">
        <column name="address" type="varchar(255)"/>
    </loadData>
</changeSet>
changeSet:
  id: loadData-example
  author: liquibase-docs
  changes:
  - loadData:
      catalogName: cat
      columns:
      - column:
          name: address
          type: varchar(255)
      encoding: UTF-8
      file: com/example/users.csv
      quotchar: A String
      schemaName: public
      separator: A String
      tableName: person
{
  "changeSet": {
    "id": "loadData-example",
    "author": "liquibase-docs",
    "changes": [
      {
        "loadData": {
          "catalogName": "cat",
          "columns": [
            {
              "column": {
                "name": "address",
                "type": "varchar(255)"
              }
            }]
          ,
          "encoding": "UTF-8",
          "file": "com/example/users.csv",
          "quotchar": "A String",
          "schemaName": "public",
          "separator": "A String",
          "tableName": "person"
        }
      }]
    
  }
}

SQL Generated From Above Sample (MySQL)

INSERT INTO cat.person (`id,
 name,
 age`) VALUES ('1,
 Fred,
 21');

INSERT INTO cat.person (`id,
 name,
 age`) VALUES ('2,
 Wilma,
 22');

INSERT INTO cat.person (`id,
 name,
 age`) VALUES ('3,
 Barney,
 42');

Database Support

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

Missing, incomplete or wrong information? Please send a pull request