In Java, how do you deal with double quote inside of a CSV that you need to parse

Question

here is what I want to do,

This my spend.csv file :

"Date","Description","Detail","Amount"
"5/03/21","Cinema","Batman","7.90"
"15/02/20","Groceries","Potatoes","23.00"
"9/12/21","DIY","Wood Plates","33.99"
"9/07/22","Fuel","Shell","$56.00"
"23/08/19","Lamborghini","Aventador","800,000.00"

From a table view :

Table View of the csv

And here is what I want as my output file named spend.xml :

 <?xml version="1.0" encoding="UTF-8"?>
    <SPEND>
    <RECORD DATE="5/03/21">
        <DESC>Cinema</DESC>
        <DETAIL>Batman</DETAIL>
        <AMOUNT>7.90</AMOUNT>
    </RECORD>
    <RECORD DATE="15/02/20">
        <DESC>Groceries</DESC>
        <DETAIL>Potatoes</DETAIL>
        <AMOUNT>23.00</AMOUNT>
    </RECORD>
    <RECORD DATE="9/12/21">
        <DESC>DIY</DESC>
        <DETAIL>Wood Plates</DETAIL>
        <AMOUNT>33.99</AMOUNT>
    </RECORD>
    <RECORD DATE="9/07/22">
        <DESC>Fuel</DESC>
        <DETAIL>Shell</DETAIL>
        <AMOUNT>$56.00</AMOUNT>
    </RECORD>
    <RECORD DATE="23/08/19">
        <DESC>Lamborghini</DESC>
        <DETAIL>Aventador</DETAIL>
        <AMOUNT>800,000.00</AMOUNT>
    </RECORD>
    </SPEND>

In order to do that, I found some stuff here and there and managed to get this :

    public class Main {
    
       public static void main(String[] args) throws FileNotFoundException {
         
            List<String> headers = new ArrayList<String>(5);
    
            File file = new File("spend.csv");
            BufferedReader reader = null;
    
            try {
    
                DocumentBuilderFactory domFactory = DocumentBuilderFactory.newInstance();
                DocumentBuilder domBuilder = domFactory.newDocumentBuilder();
    
                Document newDoc = domBuilder.newDocument();
                // Root element
                Element rootElement = newDoc.createElement("XMLCreators");
                newDoc.appendChild(rootElement);
    
                reader = new BufferedReader(new FileReader(file));
                int line = 0;
    
                String text = null;
                while ((text = reader.readLine()) != null) {
    
                    StringTokenizer st = new StringTokenizer(text, "", false);
    
                    int index = 0;
    
    
                    String[] rowValues = text.split(",");
    
                    if (line == 0) { // Header row
                        for (String col : rowValues) {
                            headers.add(col);
                        }
                    } else { // Data row
                        Element rowElement = newDoc.createElement("RECORDS");
                        rootElement.appendChild(rowElement);
                        for (int col = 0; col < headers.size(); col++) {
                            String header = headers.get(col);
                            String value = null;
    
                            if (col < rowValues.length) {
                                value = rowValues[col];
                            } else {
                                value = "";
                            }
    
                            Element curElement = newDoc.createElement(header);
                            curElement.appendChild(newDoc.createTextNode(value));
                            rowElement.appendChild(curElement);
                        }
                    }
                    line++;
                }
    
                ByteArrayOutputStream baos = null;
                OutputStreamWriter osw = null;
    
                try {
                    baos = new ByteArrayOutputStream();
                    osw = new OutputStreamWriter(baos);
    
                    TransformerFactory tranFactory = TransformerFactory.newInstance();
                    Transformer aTransformer = tranFactory.newTransformer();
                    aTransformer.setOutputProperty(OutputKeys.INDENT, "yes");
                    aTransformer.setOutputProperty(OutputKeys.METHOD, "xml");
                    aTransformer.setOutputProperty("{http://xml.apache.org/xslt}indent-amount", "4");
    
                    Source src = new DOMSource(newDoc);
                    Result result = new StreamResult(osw);
                    aTransformer.transform(src, result);
    
                    osw.flush();
                    System.out.println(new String(baos.toByteArray()));
                } catch (Exception exp) {
                    exp.printStackTrace();
                } finally {
                    try {
                        osw.close();
                    } catch (Exception e) {
                    }
                    try {
                        baos.close();
                    } catch (Exception e) {
                    }
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
    
        }
    }

At this point the programm should print in the terminal the XML file but;

Sadly, because of the double quotes of each value in my CSV file, I'm having this issue :

java org.w3c.dom.domexception invalid_character_err an invalid or illegal xml character is specified

I think I'm missing something around those lines :

    StringTokenizer st = new StringTokenizer(text, "", false);
    int index = 0;
    String[] rowValues = text.split(",");

I would like to keep the double quotes in my CSV, if anyone as an idea feel free to tell me please!

Answer 1

Before you run your conversion, do a

String.replaceAll("\"", "####")

Then run the conversion and when it is complete, reverse it and replace all the "####" in the string with double quotes

Answer 2

Another possible approach using OpenCsv and Jackson:

public class FileProcessor {
    public static void main(String[] args) throws IOException {
        List<DataStructure> importList =  new CsvToBeanBuilder<DataStructure>(
                new FileReader("pathIn"))
                    .withIgnoreEmptyLine(true)
                    .withType(DataStructure.class)
                    .build()
                    .parse();

        ListLoader exportList = new ListLoader(importList);

        XmlMapper xmlMapper = new XmlMapper();
        xmlMapper.configure(ToXmlGenerator.Feature.WRITE_XML_DECLARATION, true)
                .enable(SerializationFeature.INDENT_OUTPUT)
                .writeValue(new File("pathOut"), exportList);
    }
}

Class to serialize each element:

@Data
public class DataStructure {
    @CsvBindByName
    @JacksonXmlProperty(isAttribute = true, localName = "DATE")
    private String date;
    @CsvBindByName
    @JacksonXmlProperty(localName = "DESC")
    private String description;
    @CsvBindByName
    @JacksonXmlProperty(localName = "DETAIL")
    private String detail;
    @CsvBindByName
    @JacksonXmlProperty(localName = "AMOUNT")
    private String amount;
}

Class to serialize full list:

@JacksonXmlRootElement(localName = "SPEND")
public class ListLoader {
    @JacksonXmlElementWrapper(useWrapping = false)
    @JacksonXmlProperty(localName = "RECORD")
    private List<DataStructure> list;

    public ListLoader(List<DataStructure> list){
        this.list = list;
    }
}