{"type":"doc","content":[{"type":"extension","attrs":{"layout":"default","extensionType":"com.atlassian.confluence.macro.core","extensionKey":"toc","parameters":{"macroParams":{"style":{"value":"none"}},"macroMetadata":{"macroId":{"value":"389b3756-2965-4de5-92a3-4897cff10949"},"schemaVersion":{"value":"1"},"title":"Table of Contents"}},"localId":"c8ad2b91-c29e-4893-b01e-8bc15fba0c2b"}},{"type":"heading","attrs":{"level":1},"content":[{"text":"Liquibase Enterprise and CSV Files","type":"text"}]},{"type":"paragraph","content":[{"text":"Liquibase Enterprise does have the ability to load and update data using CSV formatted files.","type":"text"}]},{"type":"paragraph","content":[{"text":"Note the following with the CSV loadData and loadUpdateData:","type":"text"}]},{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"The Deploy Report does ","type":"text"},{"text":"not","type":"text","marks":[{"type":"underline"}]},{"text":" show the ","type":"text"},{"text":"Generated SQL & Output","type":"text","marks":[{"type":"strong"}]},{"text":".","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Rollback","type":"text","marks":[{"type":"strong"}]},{"text":" of CSV data files is ","type":"text"},{"text":"not","type":"text","marks":[{"type":"underline"}]},{"text":" supported.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"CSV file format is mandatory and should contain headers in the first line.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"CSV file formats and CSV data files are ","type":"text"},{"text":"not","type":"text","marks":[{"type":"underline"}]},{"text":" currently supported in ","type":"text"},{"text":"Packager automation","type":"text","marks":[{"type":"strong"}]},{"text":". Files must be placed manually or via external automation in the Resources directory of the Liquibase Project.","type":"text"}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"(Optional) Oracle Setup Data","type":"text"}]},{"type":"paragraph","content":[{"text":"The below samples are provided for an Oracle database. In order to run the below samples, the following Setup Data is required on your database.","type":"text"}]},{"type":"orderedList","attrs":{"order":1},"content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Create a ","type":"text"},{"text":"dbo schema","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Create a ","type":"text"},{"text":"dbo.contacts table","type":"text","marks":[{"type":"strong"}]},{"text":", eg. ","type":"text"}]},{"type":"codeBlock","content":[{"text":"CREATE TABLE dbo.contacts \n(\"id\" NUMBER NOT NULL, \n\"activeflag\" NUMBER(1), \n\"firstname\" VARCHAR2(50), \n\"lastname\" VARCHAR2(50), \n\"age\" NUMBER, \n\"lastcall\" TIMESTAMP, \nCONSTRAINT \"PK_CONTACTS\" PRIMARY KEY (\"id\"));","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Create a ","type":"text"},{"text":"dbo.contacts_seq","type":"text","marks":[{"type":"strong"}]},{"text":", eg. ","type":"text"}]},{"type":"codeBlock","content":[{"text":"CREATE SEQUENCE dbo.contacts_seq\n START WITH 1\n INCREMENT BY 1\n NOCACHE\n NOCYCLE;","type":"text"}]}]}]},{"type":"heading","attrs":{"level":1},"content":[{"text":"Loading Data with a CSV file","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Step 1:","type":"text"}]},{"type":"paragraph","content":[{"text":"Create the CSV file that contains the data you wish to have loaded. 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.","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"CSV File and Format","type":"text"}]},{"type":"table","attrs":{"layout":"default","width":760.0,"localId":"c0e0eea9-8523-476d-b822-40dc121de2d5"},"content":[{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"background":"initial","rowspan":1},"content":[{"type":"codeBlock","content":[{"text":"id,activeflag,firstname,lastname,age,lastcall\ndbo.contacts_seq.nextval,0,Chris,Klackson,32,2025-07-04 12:33:27\ndbo.contacts_seq.nextval,1,Samantha,Sallers,47,2025-07-15 01:01:16\ndbo.contacts_seq.nextval,1,Pete,Prosser,50,2025-07-15 06:45:48\ndbo.contacts_seq.nextval,0,Joeseph,Scala,70,2025-07-28 03:14:32\ndbo.contacts_seq.nextval,0,Umbert,Klassen,31,2025-09-17 22:11:08\ndbo.contacts_seq.nextval,1,Gary,Finer,35,2025-09-17 18:25:09","type":"text"}]}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Step 2:","type":"text"}]},{"type":"paragraph","content":[{"text":"Place the file in the Resources directory of the Project Repo. ","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"CSV File Path","type":"text"}]},{"type":"table","attrs":{"layout":"default","width":760.0,"localId":"0c6c1d5b-70c8-4d9c-bbcf-e676f11d078e"},"content":[{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"background":"initial","rowspan":1},"content":[{"type":"codeBlock","content":[{"text":"{DaticalDBWorkspace}/{DaticalDBProjectName}/Resources/data/contacts.csv","type":"text"}]}]}]}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Path Considerations","type":"text"}]},{"type":"paragraph","content":[{"text":"If the CSV file is stored in the project resources directory - ","type":"text"},{"text":"{DaticalDBWorkspace}/{DaticalDBProjectName}/Resources","type":"text","marks":[{"type":"code"}]},{"text":", then you can use a relative path to reference the file.","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Full Path: ","type":"text"},{"text":"/home/kevin/datical/ecomm/Resources/data/contacts.csv","type":"text","marks":[{"type":"code"}]}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"Relative Path:","type":"text"},{"text":"data/contacts.csv","type":"text","marks":[{"type":"code"}]}]}]}]},{"type":"panel","attrs":{"panelType":"info"},"content":[{"type":"paragraph","content":[{"text":"It is a good practice to create a ","type":"text"},{"text":"data","type":"text","marks":[{"type":"code"}]},{"text":" subdirectory in the ","type":"text"},{"text":"Resources","type":"text","marks":[{"type":"code"}]},{"text":" directory and keep your CSV files there.","type":"text"}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Step 3:","type":"text"}]},{"type":"paragraph","content":[{"text":"Create a changeset in the ","type":"text"},{"text":"Changelog/changelog.xml","type":"text","marks":[{"type":"strong"}]},{"text":" file of the Project Repo.","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Changeset","type":"text"}]},{"type":"paragraph","content":[{"text":"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.","type":"text"}]},{"type":"paragraph","content":[{"text":"Note you can include changeset attributes such as ","type":"text"},{"text":"labels","type":"text","marks":[{"type":"strong"}]},{"text":" and ","type":"text"},{"text":"contexts","type":"text","marks":[{"type":"strong"}]},{"text":".","type":"text"}]},{"type":"paragraph","content":[{"text":"See ","type":"text"},{"text":"Valid Data Types","type":"text","marks":[{"type":"link","attrs":{"href":"https://datical-cs.atlassian.net/wiki/spaces/DDKB/pages/3809476611/How+To+Load+Data+with+a+CSV+File#Valid-Data-Types"}}]},{"text":" for supported values.","type":"text"}]},{"type":"table","attrs":{"layout":"default","width":760.0,"localId":"c99fac8f-b598-4127-a91b-a25a63008ec8"},"content":[{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"background":"initial","rowspan":1},"content":[{"type":"codeBlock","content":[{"text":"\n \n \n \n \n \n \n \n \n","type":"text"}]}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Step 4:","type":"text"}]},{"type":"paragraph","content":[{"text":"The changeset is now eligible to be deployed to the various databases on the pipeline. If you are using artifacts, be sure the package the changelog updates prior to running any deployments.","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Generated SQL Code","type":"text"}]},{"type":"paragraph","content":[{"text":"The CSV file generates the following SQL code. ","type":"text"}]},{"type":"paragraph","content":[{"text":"Note the Deploy Report does not show this ","type":"text"},{"text":"Generated SQL & Output","type":"text","marks":[{"type":"strong"}]},{"text":".","type":"text"}]},{"type":"table","attrs":{"layout":"default","width":760.0,"localId":"585bd966-0c1a-483f-8687-bb6a27d96650"},"content":[{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"background":"initial","rowspan":1},"content":[{"type":"codeBlock","content":[{"text":"INSERT INTO INTUSER.contacts (id, activeflag, firstname, lastname, age, lastcall) VALUES (contacts_seq.nextval, 0, 'Chris', 'Klackson', 32, to_date('2025-07-04 12:33:27', 'YYYY-MM-DD HH24:MI:SS'));\nINSERT INTO INTUSER.contacts (id, activeflag, firstname, lastname, age, lastcall) VALUES (contacts_seq.nextval, 0, 'Samantha', 'Sallers', 47, to_date('2025-07-15 01:01:16', 'YYYY-MM-DD HH24:MI:SS'));\nINSERT INTO INTUSER.contacts (id, activeflag, firstname, lastname, age, lastcall) VALUES (contacts_seq.nextval, 0, 'Pete', 'Prosser', 50, to_date('2025-07-15 06:45:48', 'YYYY-MM-DD HH24:MI:SS'));\nINSERT INTO INTUSER.contacts (id, activeflag, firstname, lastname, age, lastcall) VALUES (contacts_seq.nextval, 0, 'Joeseph', 'Scala', 70, to_date('2025-07-28 03:14:32', 'YYYY-MM-DD HH24:MI:SS'));\nINSERT INTO INTUSER.contacts (id, activeflag, firstname, lastname, age, lastcall) VALUES (contacts_seq.nextval, 0, 'Umbert', 'Klassen', 31, to_date('2025-09-17 22:11:08', 'YYYY-MM-DD HH24:MI:SS'));\nINSERT INTO INTUSER.contacts (id, activeflag, firstname, lastname, age, lastcall) VALUES (contacts_seq.nextval, 0, 'Gary', 'Finer', 35, to_date('2025-09-17 18:25:09', 'YYYY-MM-DD HH24:MI:SS'));","type":"text"}]}]}]}]},{"type":"heading","attrs":{"level":1},"content":[{"text":"Updating Data with a CSV file","type":"text"}]},{"type":"paragraph","content":[{"text":"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?","type":"text"},{"type":"hardBreak"},{"text":"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.","type":"text"},{"type":"hardBreak"},{"text":"Once specified, Liquibase Enterprise will generate the appropriate \"INSERT or UPDATE\" SQL to be applied to your database.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Step 1:","type":"text"}]},{"type":"paragraph","content":[{"text":"Create the CSV file that contains the data you wish to have loaded. 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.","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"CSV File and Format","type":"text"}]},{"type":"table","attrs":{"layout":"default","width":774.0,"localId":"1e5ed60d-0673-4f4b-bc60-b2292ff0cc29"},"content":[{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"background":"initial","rowspan":1},"content":[{"type":"codeBlock","content":[{"text":"id,activeflag,firstname,lastname,age,lastcall\n1,1,Chris,Klackson,32,2025-07-04 12:33:27\n2,0,Samantha,Sallers,47,2025-07-15 01:01:16\n3,1,Pete,Prosser,50,2025-07-15 06:45:48\n4,0,Joeseph,Scala,71,2025-07-28 03:14:32\n5,0,Umbert,Klassenmass,31,2025-09-17 22:11:08\n6,1,Gary,Finer,36,2025-09-17 18:25:09","type":"text"}]}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Step 2:","type":"text"}]},{"type":"paragraph","content":[{"text":"Place the file in the Resources directory of the Project Repo. ","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"CSV File Path","type":"text"}]},{"type":"table","attrs":{"layout":"default","width":760.0,"localId":"999f2803-6e55-43ed-be8b-a39b73880a10"},"content":[{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"background":"initial","rowspan":1},"content":[{"type":"codeBlock","content":[{"text":"{DaticalDBWorkspace}/{DaticalDBProjectName}/Resources/data/updated_contacts_1.csv","type":"text"}]}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Step 3:","type":"text"}]},{"type":"paragraph","content":[{"text":"Create a changeset in the ","type":"text"},{"text":"Changelog/changelog.xml","type":"text","marks":[{"type":"strong"}]},{"text":" file of the Project Repo.","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Changeset","type":"text"}]},{"type":"paragraph","content":[{"text":"Make sure to specify the primary key column.","type":"text"}]},{"type":"paragraph","content":[{"text":"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.","type":"text"}]},{"type":"paragraph","content":[{"text":"Note you can include changeset attributes such as ","type":"text"},{"text":"labels","type":"text","marks":[{"type":"strong"}]},{"text":" and ","type":"text"},{"text":"contexts","type":"text","marks":[{"type":"strong"}]},{"text":".","type":"text"}]},{"type":"paragraph","content":[{"text":"See ","type":"text"},{"text":"Valid Data Types","type":"text","marks":[{"type":"link","attrs":{"href":"https://datical-cs.atlassian.net/wiki/spaces/DDKB/pages/3809476611/How+To+Load+Data+with+a+CSV+File#Valid-Data-Types"}}]},{"text":" for supported values.","type":"text"}]},{"type":"table","attrs":{"layout":"default","width":760.0,"localId":"443a8cfe-3ad7-4252-8f33-b31df8582893"},"content":[{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"background":"initial","rowspan":1},"content":[{"type":"codeBlock","content":[{"text":"\n \n \n \n \n \n \n \n \n","type":"text"}]}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Step 4:","type":"text"}]},{"type":"paragraph","content":[{"text":"The changeset is now eligible to be deployed to the various databases on the pipeline. If you are using artifacts, be sure the package the changelog updates prior to running any deployments.","type":"text"}]},{"type":"heading","attrs":{"level":3},"content":[{"text":"Generated SQL Code","type":"text"}]},{"type":"paragraph","content":[{"text":"The CSV file generates the following SQL code. ","type":"text"}]},{"type":"paragraph","content":[{"text":"Note the Deploy Report does not show this ","type":"text"},{"text":"Generated SQL & Output","type":"text","marks":[{"type":"strong"}]},{"text":".","type":"text"}]},{"type":"table","attrs":{"layout":"default","width":760.0,"localId":"901eca29-27c7-47e0-afe7-7bbe01862ee1"},"content":[{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"background":"initial","rowspan":1},"content":[{"type":"codeBlock","content":[{"text":"DECLARE\nv_reccount NUMBER := 0;\nBEGIN\nSELECT COUNT INTO v_reccount FROM INTUSER.contacts WHERE id = 1;\nIF v_reccount = 0 THEN\nINSERT 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'));\nELSIF v_reccount = 1 THEN\nUPDATE INTUSER.contacts SET activeflag = 1, age = 32, firstname = 'Chris', lastcall = to_date('2025-07-04 12:33:27', 'YYYY-MM-DD HH24:MI:SS'), lastname = 'Klackson' WHERE id = 1;\nEND IF;\nEND;\n/;\nDECLARE\nv_reccount NUMBER := 0;\nBEGIN\nSELECT COUNT INTO v_reccount FROM INTUSER.contacts WHERE id = 2;\nIF v_reccount = 0 THEN\nINSERT 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'));\nELSIF v_reccount = 1 THEN\nUPDATE INTUSER.contacts SET activeflag = 0, age = 47, firstname = 'Samantha', lastcall = to_date('2025-07-15 01:01:16', 'YYYY-MM-DD HH24:MI:SS'), lastname = 'Sallers' WHERE id = 2;\nEND IF;\nEND;\n/;\nDECLARE\nv_reccount NUMBER := 0;\nBEGIN\nSELECT COUNT INTO v_reccount FROM INTUSER.contacts WHERE id = 3;\nIF v_reccount = 0 THEN\nINSERT 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'));\nELSIF v_reccount = 1 THEN\nUPDATE INTUSER.contacts SET activeflag = 1, age = 50, firstname = 'Pete', lastcall = to_date('2025-07-15 06:45:48', 'YYYY-MM-DD HH24:MI:SS'), lastname = 'Prosser' WHERE id = 3;\nEND IF;\nEND;\n/;\nDECLARE\nv_reccount NUMBER := 0;\nBEGIN\nSELECT COUNT INTO v_reccount FROM INTUSER.contacts WHERE id = 4;\nIF v_reccount = 0 THEN\nINSERT 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'));\nELSIF v_reccount = 1 THEN\nUPDATE INTUSER.contacts SET activeflag = 0, age = 71, firstname = 'Joeseph', lastcall = to_date('2025-07-28 03:14:32', 'YYYY-MM-DD HH24:MI:SS'), lastname = 'Scala' WHERE id = 4;\nEND IF;\nEND;\n/;\nDECLARE\nv_reccount NUMBER := 0;\nBEGIN\nSELECT COUNT INTO v_reccount FROM INTUSER.contacts WHERE id = 5;\nIF v_reccount = 0 THEN\nINSERT 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'));\nELSIF v_reccount = 1 THEN\nUPDATE INTUSER.contacts SET activeflag = 0, age = 31, firstname = 'Umbert', lastcall = to_date('2025-09-17 22:11:08', 'YYYY-MM-DD HH24:MI:SS'), lastname = 'Klassenmass' WHERE id = 5;\nEND IF;\nEND;\n/;\nDECLARE\nv_reccount NUMBER := 0;\nBEGIN\nSELECT COUNT INTO v_reccount FROM INTUSER.contacts WHERE id = 6;\nIF v_reccount = 0 THEN\nINSERT 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'));\nELSIF v_reccount = 1 THEN\nUPDATE INTUSER.contacts SET activeflag = 1, age = 36, firstname = 'Gary', lastcall = to_date('2025-09-17 18:25:09', 'YYYY-MM-DD HH24:MI:SS'), lastname = 'Finer' WHERE id = 6;\nEND IF;\nEND;\n/;","type":"text"}]}]}]}]},{"type":"heading","attrs":{"level":1},"content":[{"text":"General Considerations for Loading Data With a CSV File","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Date and Datetime Considerations","type":"text"}]},{"type":"paragraph","content":[{"text":"Note, that date and datetime must be in one of the following ISO formats.","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"yyyy-MM-dd'T'HH:mm:ss","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"yyyy-MM-dd HH:mm:ss","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"yyyy-MM-dd'T'HH:mm:ss.S","type":"text"}]}]}]},{"type":"paragraph"},{"type":"heading","attrs":{"level":2},"content":[{"text":"Valid Data Types","type":"text"}]},{"type":"paragraph","content":[{"text":"When specifying columns in the loadData refactoring, you may choose from the following generic data types.","type":"text"}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"BOOLEAN","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"NUMERIC","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"DATE","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"DATETIME","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"STRING","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"COMPUTED","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"SKIP","type":"text"}]}]}]}],"version":1}

Browser not supported