Liquibase Change Types LoadData

Liquibase provides multiple ways to insert data into a table such as inserting a ChangeLog or reading from CSV (Comma Separated Value) file.
LoadData Change Types load CSV file data into the database table. "NULL" string value in CSV file will be converted into database NULL. Lines starting with # will be created as Comments of the file. Liquibase provides commentLineStartsWith tag to change comments identification, commentLineStartsWith tag with empty value will disable comments.

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xmlns:pro="http://www.liquibase.org/xml/ns/pro" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-latest.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd">
	<changeSet author="wesome" id="1695457570852-1">
		<createTable catalogName="appledb" tableName="apple">
			<column autoIncrement="true" name="apple_id" type="BIGINT">
				<constraints nullable="false" primaryKey="true" />
			</column>
			<column name="apple_name" type="VARCHAR(255)" />
			<column defaultValueComputed="CURRENT_TIMESTAMP" name="current_date_time" type="datetime" />
		</createTable>
	</changeSet>
	<changeSet author="wesome" id="1695457570852-2">
		<loadData catalogName="appledb" commentLineStartsWith="#" encoding="UTF-8" file="csvData/apple.csv" quotchar="&quot;" separator="," tableName="apple">
			<column header="apple_id" name="apple_id" type="NUMERIC" />
			<column header="apple_name" name="apple_name" type="STRING" />
			<column header="current_date_time" name="current_date_time" type="DATE" />
		</loadData>
	</changeSet>
</databaseChangeLog>

 liquibase.properties (update changeLog File file format with SQL, XML, YAML, JSON as per changelog.mysql.)

changeLogFile=changelog.mysql.xml
liquibase.command.url:jdbc:mysql://localhost:3306/appleDb?createDatabaseIfNotExist=true&autoReconnect=true&useSSL=false&allowPublicKeyRetrieval=true
liquibase.command.username : root
liquibase.command.password : rootroot
dataOutputDirectory:csvData

csvData/apple.csv

"apple_id","apple_name","current_date_time"
"1","Macintosh","2023-09-23T13:53:49"
"2","Fuji","2023-09-23T13:53:49"
"3","NULL","2023-09-23T13:53:49"

Run command liquibase update

The CSV file has been loaded into the database and can be verified by running the command.

SELECT * FROM appleDb.apple;

Skip Columns from CSV

Liquibase reads columns from CSV file and map with column of loadData Change Type. To skip certain columns, use type="skip" in column tag.

<?xml version="1.1" encoding="UTF-8" standalone="no"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext" xmlns:pro="http://www.liquibase.org/xml/ns/pro" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/pro http://www.liquibase.org/xml/ns/pro/liquibase-pro-latest.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd">
	<changeSet author="shriksha (generated)" id="1695457570852-1">
		<createTable catalogName="appledb" tableName="apple">
			<column autoIncrement="true" name="apple_id" type="BIGINT">
				<constraints nullable="false" primaryKey="true" />
			</column>
			<column name="apple_name" type="VARCHAR(255)" />
			<column defaultValueComputed="CURRENT_TIMESTAMP" name="current_date_time" type="datetime" />
		</createTable>
	</changeSet>
	<changeSet author="shriksha (generated)" id="1695457570852-2">
		<loadData catalogName="appledb" commentLineStartsWith="#" encoding="UTF-8" file="csvData/apple.csv" quotchar="&quot;" separator="," tableName="apple">
			<column header="apple_id" name="apple_id" type="NUMERIC" />
			<column header="apple_name" name="apple_name" type="skip" />
			<column header="current_date_time" name="current_date_time" type="DATE" />
		</loadData>
	</changeSet>
</databaseChangeLog>

 liquibase.properties (update changeLog File file format with SQL, XML, YAML, JSON as per changelog.mysql.)

changeLogFile=changelog.mysql.xml
liquibase.command.url:jdbc:mysql://localhost:3306/appleDb?createDatabaseIfNotExist=true&autoReconnect=true&useSSL=false&allowPublicKeyRetrieval=true
liquibase.command.username : root
liquibase.command.password : rootroot
dataOutputDirectory:csvData

csvData/apple.csv

"apple_id","apple_name","current_date_time"
"1","Macintosh","2023-09-23T13:53:49"
"2","Fuji","2023-09-23T13:53:49"
"3","NULL","2023-09-23T13:53:49"

Run command liquibase update

The CSV file has been loaded into the database and the apple_name column has been skipped, the same can be verified by running the command.

SELECT * FROM appleDb.apple;