liquibase is putting single quote around all values of a csv line for the insert sql statement

Question

This is what I have in the csv file:

CONTACT_TYP_CD,CONTACT_TYP_DESC,CREATE_DATE,CREATE_USER,UPDATE_DATE,UPDATE_USER
"ALL","Contact to be used for all communications","2014-03-14 00:00:00","CS_MAIN",null,null

This is how I load this file through liquibase:

<loadData file="src/main/resources/METAINF/install/seed_data/seed_contact_type.csv"
        tableName="CONTACT_TYPE">
</loadData>

This is what liquibase uses to insert the data into oracle:

liquibase.exception.DatabaseException: Error executing SQL INSERT INTO CONTACT_TYPE (CONTACT_TYP_CD,CONTACT_TYP_DESC,CREATE_DATE,CREATE_USER,UPDATE_DATE,UPDATE_USER) VALUES ('"LL","Contact to be used for all communications","2014-03-14 00:00:00","CS_MAIN",null,null'): ORA-00947: not enough values

Can someone tell me what I am doing wrong? Thank you

Answer 1

Your Problem here is the separator, not the quote. Liquibase seems to use semikolons by default, so you have to specify separator=",". Otherwise the whole line is considered a single value.

Answer 2

you need to add the xml escape for double quote in the quotechar

quotchar="""

Answer 3

Try removing the double quotes from your csv file, and define columns types, see the following test case as an example:

changeSet source

csv file

Another solution might be to enclose also the column titles in quotes, as seen here