Liquibase CSV loadData fails with quoted string containing a comma

Question

I am trying to load CSV file into SQLserver table using Liquibase change log set. When saved XLSX file as CSV file, column containing comma saved in double quotes (please see 3rd value below), this is fine as per standards but liquibase is ignoring double quotes and considering comma inside the double-quotes.

13,OV,"Diabetes outpatient self-management training services individual,per 30 minutes",77.82,1,0,1/4/2016,,G0108

Error messgae from command line terminal:

CSV file v2.1/r21/TestData20212021.csv Line 21 has 10 values defined, Header has 9. Numbers MUST be equal (check for unquoted string with embedded commas)

<changeSet  author="sprint-developer" id="sprint1-09">

<loadData 
file="v2.1/r21/TestData2021.csv" 
 tableName = "tbl_Votes" encoding="UTF-8"   >  

 <column header="VcenarioID" name="VcenarioID" type="numeric"/>
     <column header="venefitCode" name="venefitCode" type="string"/>
     <column header="KostDescription" name="KostDescription" type="string"/>
     <column header="Kost" name="Kost" type="NUMERIC"/>
     <column header="OcKurrences" name="OKcurrences" type="numeric"/>
     <column header="KostIsPerIncident" name="KostIsPerIncident" type="boolean"/>
     <column header="KostDate" name="KostDate"  type="date"/>
     <column header="VundleId" name="VundleId"  type="NUMERIC"/>
     <column header="VillingCode" name="VillingCode" type="string"/>
    </loadData>
 <rollback>Delete from tbl_Votes where VcenarioID=13 </rollback>
</changeSet>

htshame htshame · Accepted Answer · 2020-02-25T11:11:14

Try adding quotchar='"' to your changeSet. This should tell liqbuiase to treat everything inside "" as one single value.

Check out loadData docs.

So your changeSet could look like this:

<changeSet  author="sprint-developer" id="sprint1-09">
    <loadData
            file="v2.1/r21/TestData2021.csv"
            tableName = "tbl_Votes" encoding="UTF-8" quotchar='"'>

        <column header="VcenarioID" name="VcenarioID" type="numeric"/>
        <column header="venefitCode" name="venefitCode" type="string"/>
        <column header="KostDescription" name="KostDescription" type="string"/>
        <column header="Kost" name="Kost" type="NUMERIC"/>
        <column header="OcKurrences" name="OKcurrences" type="numeric"/>
        <column header="KostIsPerIncident" name="KostIsPerIncident" type="boolean"/>
        <column header="KostDate" name="KostDate"  type="date"/>
        <column header="VundleId" name="VundleId"  type="NUMERIC"/>
        <column header="VillingCode" name="VillingCode" type="string"/>
    </loadData>
    <rollback>Delete from tbl_Votes where VcenarioID=13 </rollback>
</changeSet>

Liquibase CSV loadData fails with quoted string containing a comma

1 Answers