Writing xml content with comma ',' to csv

Question

My csv file is a comma delimited file. I need to write XML contained in a variable to last cell of each row as part of my requirement. This XML contains commas(') which when written through CSVWriter, writes incorrectly. each comma is treated as delimiter and the XML is split into different cells.

Part of my xml as sample

<tns:Nature>Residential</tns:Nature>
<tns:Type>Duplex</tns:Type>
<tns:OccupancyPurpose>Owner Occupied Primary Residence</tns:OccupancyPurpose>
<tns:Valuation>
<tns:Sequence>1</tns:Sequence>
<tns:ValuationAmount>700000</tns:ValuationAmount>
<tns:ValuationDate>2014-08-13</tns:ValuationDate>
<tns:ValuerCompany>C J A **Replace_Comma** Lee **Replace_Comma** Property</tns:ValuerCompany>
<tns:ValuationType>Electronic Assessment</tns:ValuationType>
</tns:Valuation>

My code goes as below.

    strLine = strLine + strXMLContent;
    File file = new File(strFilePath); 

        FileWriter outputfile = null;
        try 
        {
            outputfile = new FileWriter(file,true);
            CSVWriter writer = new CSVWriter(outputfile,',', CSVWriter.NO_QUOTE_CHARACTER,CSVWriter.DEFAULT_ESCAPE_CHARACTER,System.getProperty("line.separator"));;

            String arr[] = strLine.split(",",-1);
            for(int i = 0;  i< arr.length; i++)
            {
                arr[i] = arr[i].replace("Replace_Comma", ","); //Replaces filler as a comma before being placed in csv
            }
            writer.writeNext(arr);
            writer.close();
        } 
        catch (IOException e) 
        {
            e.printStackTrace();
        }

The filler when translated as a comma is treated as a delimiter and the following text is written in the next cell.

I have tried many things but to no avail. Please help.

Have you tried enclosing each data between ' or " ? (That CSVWriter constructor gives me hints it should be possible - CSVWriter.NO_QUOTE_CHARACTER) — BigMike, Dec 31 '19 at 09:16
I tried CSVWriter.DEFAULT_QUOTE_CHARACTER but it replaces some other quote characters instead of adding to each field — Mithilesh Indurkar, Dec 31 '19 at 09:31
According to the javadoc that parameter should instruct the Writer to quote all datas with its "quoteChar", what do you mean it replaces other quotes ? — BigMike, Dec 31 '19 at 10:10

BHAWANI SINGH · Answer 1 · 2019-12-31T10:49:03.627

0

As @BigMike suggested, Replacing

CSVWriter writer = new CSVWriter(outputfile,',', CSVWriter.NO_QUOTE_CHARACTER,CSVWriter.DEFAULT_ESCAPE_CHARACTER,System.getProperty("line.separator"));;

with

CSVWriter writer = new CSVWriter(outputfile);

or

CSVWriter writer = new CSVWriter(outputfile,',', CSVWriter.DEFAULT_ESCAPE_CHARACTER,CSVWriter.NO_ESCAPE_CHARACTER,System.getProperty("line.separator"));

should work.

edited Dec 31 '19 at 10:49

answered Dec 31 '19 at 10:43

BHAWANI SINGH

729
4
8

Sorry to say it did not work. I am facing the same problem – Mithilesh Indurkar Dec 31 '19 at 12:00
@MithileshIndurkar can you please share your version of **opencsv** ? – BHAWANI SINGH Dec 31 '19 at 12:42
@MithileshIndurkar can you please also share _content_ of generated **csv** file. – BHAWANI SINGH Dec 31 '19 at 12:46
FYI: mine **opencsv** version was **opencsv-5.0** – BHAWANI SINGH Dec 31 '19 at 12:49

score 0 · Accepted Answer · answered Jan 02 '20 at 10:33

I was able to solve it with the help of answer of this question and using the below,

String escaped = StringEscapeUtils.escapeCsv(strXML);   

File file = new File("File.csv");
String arr[] = 
{
        "123456789&94859614357",
        escaped
};

FileWriter outputfile = null;
try 
{
        outputfile = new FileWriter(file,true);
        CSVWriter writer = new CSVWriter(outputfile,',', CSVWriter.NO_QUOTE_CHARACTER,CSVWriter.NO_ESCAPE_CHARACTER,System.getProperty("line.separator"));;
        writer.writeNext(arr);
        writer.close();
} 
catch (IOException e) 
{
        e.printStackTrace();
}

Writing xml content with comma ',' to csv

2 Answers2