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
| Name | Description |
|---|---|
| catalogName | Name of the catalog@ v3.0E.g. 'cat' |
| commentLineStartsWith | Lines starting with this character are treated as comment and ignored.Default: '#' |
| encoding | Encoding of the CSV file (defaults to UTF-8)Default: 'utf-8' |
| file | CSV file to loadE.g. 'com/example/users.csv' |
| quotchar | The quote character for string fields containing the separator character.Default: '"' |
| relativeToChangelogFile | booleanWhether the file path is relative to the root changelog file rather than to the classpath. |
| schemaName | Name of the schemaE.g. 'public' |
| separator | Character separating the fields.Default: ',' |
| tableName | Name of the table to insert data intoE.g. 'person' |
| usePreparedStatements | booleanUse prepared statements instead of insert statement strings if the DB supports it |
Nested Properties
| Name | Description | ||||||||
|---|---|---|---|---|---|---|---|---|---|
| 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
|
<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
| Database | Notes | Auto Rollback |
|---|---|---|
| DB2/LUW | Supported | No |
| DB2/z | Supported | No |
| Derby | Supported | No |
| Firebird | Supported | No |
| H2 | Supported | No |
| HyperSQL | Supported | No |
| INGRES | Supported | No |
| Informix | Supported | No |
| MariaDB | Supported | No |
| MySQL | Supported | No |
| Oracle | Supported | No |
| PostgreSQL | Supported | No |
| SQL Server | Supported | No |
| SQLite | Supported | No |
| Sybase | Supported | No |
| Sybase Anywhere | Supported | No |