General Considerations for Loading Data With a CSV File
Date and Datetime Considerations
Note, that date and datetime must be in one of the following ISO formats.
yyyy-MM-dd'T'HH:mm:ss
yyyy-MM-dd HH:mm:ss
yyyy-MM-dd'T'HH:mm:ss.S
Path Considerations
If the CSV file is stored in the project resources directory - {DaticalDBWorkspace}/{DaticalDBProjectName}/Resources, then you can use a relative path to reference the file.
Full Path:
/home/kevin/datical/ecomm/Resources/data/contacts.csvRelative Path:
data/contacts.csv
Note, it is a good practice to create a data subdirectory in the Resources directory and keep your CSV files there.
Valid Data Types
When specifying columns in the loadData refactoring, you may choose from the following generic data types.
BOOLEAN
NUMERIC
DATE
DATETIME
STRING
COMPUTED
SKIP
Loading Data with a CSV file
Create the CSV file that contains the data you wish to have loaded. As you would expect, the first line needs to be the list of columns to be loaded. Liquibase Enterprise will create the INSERTs with the columns and data listed in the file.
Example (Oracle)
Changeset
While specifying the columns in the loadData refactoring is not explicitly required, providing this information gives us the data types so the best possible SQL is generated.
<changeSet author="Kevin" id="2014-01-25-data-load-1"> <loadData encoding="UTF-8" file="data/contacts.csv" tableName="contacts"> <column header="id" name="id" type="NUMERIC"/> <column header="activeflag" name="activeflag" type="BOOLEAN"/> <column header="firstname" name="firstname" type="STRING"/> <column header="lastname" name="lastname" type="STRING"/> <column header="age" name="age" type="NUMERIC"/> <column header="lastcall" name="lastcall" type="DATETIME"/> </loadData> </changeSet> |
CSV File Path
{DaticalDBWorkspace}/{DaticalDBProjectName}/Resources/data/contacts.csv
|
CSV File
id,activeflag,firstname,lastname,age,lastcall contacts_seq.nextval,0,Chris,Klackson,32,2012-07-04 12:33:27 contacts_seq.nextval,1,Samantha,Sallers,47,2012-07-15 01:01:16 contacts_seq.nextval,1,Pete,Prosser,50,2012-07-15 06:45:48 contacts_seq.nextval,0,Joeseph,Scala,70,2012-07-28 03:14:32 contacts_seq.nextval,0,Umbert,Klassen,31,2012-09-17 22:11:08 contacts_seq.nextval,1,Gary,Finer,35,2012-09-17 18:25:09 |
Generated SQL Code
The CSV file generates the following SQL code.
INSERT INTO INTUSER.contacts (id, activeflag, firstname, lastname, age, lastcall) VALUES (contacts_seq.nextval, 0, 'Chris', 'Klackson', 32, to_date('2012-07-04 12:33:27', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO INTUSER.contacts (id, activeflag, firstname, lastname, age, lastcall) VALUES (contacts_seq.nextval, 0, 'Samantha', 'Sallers', 47, to_date('2012-07-15 01:01:16', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO INTUSER.contacts (id, activeflag, firstname, lastname, age, lastcall) VALUES (contacts_seq.nextval, 0, 'Pete', 'Prosser', 50, to_date('2012-07-15 06:45:48', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO INTUSER.contacts (id, activeflag, firstname, lastname, age, lastcall) VALUES (contacts_seq.nextval, 0, 'Joeseph', 'Scala', 70, to_date('2012-07-28 03:14:32', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO INTUSER.contacts (id, activeflag, firstname, lastname, age, lastcall) VALUES (contacts_seq.nextval, 0, 'Umbert', 'Klassen', 31, to_date('2012-09-17 22:11:08', 'YYYY-MM-DD HH24:MI:SS'));
INSERT INTO INTUSER.contacts (id, activeflag, firstname, lastname, age, lastcall) VALUES (contacts_seq.nextval, 0, 'Gary', 'Finer', 35, to_date('2012-09-17 18:25:09', 'YYYY-MM-DD HH24:MI:SS'));
|
Updating Data with a CSV file
The previous example works nicely when you're loading data for the first time. But, what do you do if you want to use a CSV file to update data? How would that work?
In this case, you'll want to use the loadUpdateData refactoring. It's similar to using loadData with one exception – you need to specify the primary key.
Once specified, we generate the appropriate "INSERT or UPDATE" SQL to be applied to your database.
Example (Oracle)
Changeset
Make sure to specify the primary key column.
While specifying the columns in the loadUpdateData refactoring is not explicitly required, providing this information gives us the data types so the best possible SQL is generated.
<changeSet id="2014-01-26-contacts-data-update-1" author="Kevin" > <loadUpdateData encoding="UTF-8" file="data/updated_contacts_1.csv" tableName="contacts" primaryKey="id"> <column name="id" header="id" type="NUMERIC"/> <column name="activeflag" header="activeflag" type="NUMERIC"/> <column name="firstname" header="firstname" type="STRING"/> <column name="lastname" header="lastname" type="STRING"/> <column name="age" header="age" type="NUMERIC"/> <column name="lastcall" header="lastcall" type="DATETIME"/> </loadUpdateData> </changeSet> |
CSV File Path
{DaticalDBWorkspace}/{DaticalDBProjectName}/Resources/data/updated_contacts_1.csv
|
CSV File
id,activeflag,firstname,lastname,age,lastcall 1,1,Chris,Klackson,32,2013-07-04 12:33:27 2,0,Samantha,Sallers,47,2013-07-15 01:01:16 3,1,Pete,Prosser,50,2013-07-15 06:45:48 4,0,Joeseph,Scala,71,2012-07-28 03:14:32 5,0,Umbert,Klassenmass,31,2012-09-17 22:11:08 6,1,Gary,Finer,36,2013-09-17 18:25:09 |
Generated SQL File
The CSV file generates the following SQL code.
DECLARE
v_reccount NUMBER := 0;
BEGIN
SELECT COUNT INTO v_reccount FROM INTUSER.contacts WHERE id = 1;
IF v_reccount = 0 THEN
INSERT INTO INTUSER.contacts (id, activeflag, firstname, lastname, age, lastcall) VALUES (1, 1, 'Chris', 'Klackson', 32, to_date('2013-07-04 12:33:27', 'YYYY-MM-DD HH24:MI:SS'));
ELSIF v_reccount = 1 THEN
UPDATE INTUSER.contacts SET activeflag = 1, age = 32, firstname = 'Chris', lastcall = to_date('2013-07-04 12:33:27', 'YYYY-MM-DD HH24:MI:SS'), lastname = 'Klackson' WHERE id = 1;
END IF;
END;
/;
DECLARE
v_reccount NUMBER := 0;
BEGIN
SELECT COUNT INTO v_reccount FROM INTUSER.contacts WHERE id = 2;
IF v_reccount = 0 THEN
INSERT INTO INTUSER.contacts (id, activeflag, firstname, lastname, age, lastcall) VALUES (2, 0, 'Samantha', 'Sallers', 47, to_date('2013-07-15 01:01:16', 'YYYY-MM-DD HH24:MI:SS'));
ELSIF v_reccount = 1 THEN
UPDATE INTUSER.contacts SET activeflag = 0, age = 47, firstname = 'Samantha', lastcall = to_date('2013-07-15 01:01:16', 'YYYY-MM-DD HH24:MI:SS'), lastname = 'Sallers' WHERE id = 2;
END IF;
END;
/;
DECLARE
v_reccount NUMBER := 0;
BEGIN
SELECT COUNT INTO v_reccount FROM INTUSER.contacts WHERE id = 3;
IF v_reccount = 0 THEN
INSERT INTO INTUSER.contacts (id, activeflag, firstname, lastname, age, lastcall) VALUES (3, 1, 'Pete', 'Prosser', 50, to_date('2013-07-15 06:45:48', 'YYYY-MM-DD HH24:MI:SS'));
ELSIF v_reccount = 1 THEN
UPDATE INTUSER.contacts SET activeflag = 1, age = 50, firstname = 'Pete', lastcall = to_date('2013-07-15 06:45:48', 'YYYY-MM-DD HH24:MI:SS'), lastname = 'Prosser' WHERE id = 3;
END IF;
END;
/;
DECLARE
v_reccount NUMBER := 0;
BEGIN
SELECT COUNT INTO v_reccount FROM INTUSER.contacts WHERE id = 4;
IF v_reccount = 0 THEN
INSERT INTO INTUSER.contacts (id, activeflag, firstname, lastname, age, lastcall) VALUES (4, 0, 'Joeseph', 'Scala', 71, to_date('2012-07-28 03:14:32', 'YYYY-MM-DD HH24:MI:SS'));
ELSIF v_reccount = 1 THEN
UPDATE INTUSER.contacts SET activeflag = 0, age = 71, firstname = 'Joeseph', lastcall = to_date('2012-07-28 03:14:32', 'YYYY-MM-DD HH24:MI:SS'), lastname = 'Scala' WHERE id = 4;
END IF;
END;
/;
DECLARE
v_reccount NUMBER := 0;
BEGIN
SELECT COUNT INTO v_reccount FROM INTUSER.contacts WHERE id = 5;
IF v_reccount = 0 THEN
INSERT INTO INTUSER.contacts (id, activeflag, firstname, lastname, age, lastcall) VALUES (5, 0, 'Umbert', 'Klassenmass', 31, to_date('2012-09-17 22:11:08', 'YYYY-MM-DD HH24:MI:SS'));
ELSIF v_reccount = 1 THEN
UPDATE INTUSER.contacts SET activeflag = 0, age = 31, firstname = 'Umbert', lastcall = to_date('2012-09-17 22:11:08', 'YYYY-MM-DD HH24:MI:SS'), lastname = 'Klassenmass' WHERE id = 5;
END IF;
END;
/;
DECLARE
v_reccount NUMBER := 0;
BEGIN
SELECT COUNT INTO v_reccount FROM INTUSER.contacts WHERE id = 6;
IF v_reccount = 0 THEN
INSERT INTO INTUSER.contacts (id, activeflag, firstname, lastname, age, lastcall) VALUES (6, 1, 'Gary', 'Finer', 36, to_date('2013-09-17 18:25:09', 'YYYY-MM-DD HH24:MI:SS'));
ELSIF v_reccount = 1 THEN
UPDATE INTUSER.contacts SET activeflag = 1, age = 36, firstname = 'Gary', lastcall = to_date('2013-09-17 18:25:09', 'YYYY-MM-DD HH24:MI:SS'), lastname = 'Finer' WHERE id = 6;
END IF;
END;
/;
|