#217 In consistency between CsvReader and CsvWriter with backslash

[Leonard Siu]

The default CsvWriter is able to write the content out with backslash at the end "correctly". And by correctly, I meant when I open the csv in excel, it shows a the content correctly with single backslash.

However, when using the default CsvReader, we are unable to read the same file.

To illustrate/reproduce this issue, I have created a test case here. https://github.com/lsiu/opencsv-issue/blob/main/src/test/java/BugDemoTest.java

[Leonard Siu]

This is somewhat related to this: https://sourceforge.net/p/opencsv/support-requests/45/

[Leonard Siu]

My first thought is to default to no escape value for the CsvParser for my use case, but I realizes this has large implication, and want to put this up here for discussion first.

[Scott Conway]

I will see if I have the chance to take a closer look at it this weekend but for now I am not calling this a bug as you said the output of the writer is "correct".

The issue is the default CSVReader treats the slash as an escape character. That being said you cannot use an default CSVReader in this case. You either need to set the escape character to null (basically there are no escape characters) OR use a CSVReader with an RFC4180Parser (which does not recognize escape characters).

I created the following two tests demonstrating this and they both pass (as long as you don't run them in parallel <bg>). </bg>

        @Test
    public void usingRFC4180ParserinCSVReader() throws Exception {
        Connection conn = DriverManager.getConnection("jdbc:h2:mem:example;INIT=RUNSCRIPT FROM 'classpath:sql/create-database.sql'");
        //noinspection SqlResolve,SqlNoDataSourceInspection
        final String sql = "select * from example";
        {
            ResultSet rs = conn.createStatement().executeQuery(sql);
            while (rs.next()) {
                int id = rs.getInt(1);
                String text = rs.getString(2);
                System.out.format("%d\t%s\n", id, text);
            }
        }

        ResultSet rs = conn.createStatement().executeQuery(sql);
        ResultSetHelperService rsHelper = new ResultSetHelperService();
        File file = new File("test.csv");
        if (file.exists()) {
            boolean deleted = file.delete();
            if (!deleted) {
                throw new RuntimeException("Cannot delete file:" + file.getAbsolutePath());

            }
        }

        // if you open 'test.csv', with excel, it looks proper, i.e. second row text ends with one backslash
        CSVWriter csvWriter = new CSVWriter(new FileWriter(file));

        int rows = 0;
        while (rs.next()) {
            rows++;
            String[] values = rsHelper.getColumnValues(rs);
            csvWriter.writeNext(values);
        }
        csvWriter.flush();

        RFC4180ParserBuilder parserBuilder = new RFC4180ParserBuilder();
        ICSVParser parser = parserBuilder.build();

        CSVReaderBuilder readerBuilder = new CSVReaderBuilder(new FileReader(file));
        CSVReader csvReader = readerBuilder.withCSVParser(parser).build();
        // to get this to work, we cannot just use the default reader like we did with the default writer, we need to "disable" the escape char
        // CSVReader csvReader = new CSVReaderBuilder(new FileReader(file)).withCSVParser(new CSVParserBuilder().withEscapeChar((char)0).build()).build();
        List<String[]> allLines = csvReader.readAll();

        Assertions.assertEquals(rows, allLines.size());
    }

    @Test
    public void usingNoEscapeCharacterInCSVReader() throws Exception {
        Connection conn = DriverManager.getConnection("jdbc:h2:mem:example;INIT=RUNSCRIPT FROM 'classpath:sql/create-database.sql'");
        //noinspection SqlResolve,SqlNoDataSourceInspection
        final String sql = "select * from example";
        {
            ResultSet rs = conn.createStatement().executeQuery(sql);
            while (rs.next()) {
                int id = rs.getInt(1);
                String text = rs.getString(2);
                System.out.format("%d\t%s\n", id, text);
            }
        }

        ResultSet rs = conn.createStatement().executeQuery(sql);
        ResultSetHelperService rsHelper = new ResultSetHelperService();
        File file = new File("test.csv");
        if (file.exists()) {
            boolean deleted = file.delete();
            if (!deleted) {
                throw new RuntimeException("Cannot delete file:" + file.getAbsolutePath());
            }
        }

        // if you open 'test.csv', with excel, it looks proper, i.e. second row text ends with one backslash
        CSVWriter csvWriter = new CSVWriter(new FileWriter(file));

        int rows = 0;
        while (rs.next()) {
            rows++;
            String[] values = rsHelper.getColumnValues(rs);
            csvWriter.writeNext(values);
        }
        csvWriter.flush();

        CSVParserBuilder parserBuilder = new CSVParserBuilder();
        ICSVParser parser = parserBuilder.withEscapeChar(ICSVParser.NULL_CHARACTER).build();

        CSVReaderBuilder readerBuilder = new CSVReaderBuilder(new FileReader(file));
        CSVReader csvReader = readerBuilder.withCSVParser(parser).build();
        // to get this to work, we cannot just use the default reader like we did with the default writer, we need to "disable" the escape char
        // CSVReader csvReader = new CSVReaderBuilder(new FileReader(file)).withCSVParser(new CSVParserBuilder().withEscapeChar((char)0).build()).build();
        List<String[]> allLines = csvReader.readAll();

        Assertions.assertEquals(rows, allLines.size());
    }

[Leonard Siu]

I agree we probably shouldn't be calling this a bug, more like a suggestion.
I am probably not the first one to trip up on this as shown in support request: https://sourceforge.net/p/opencsv/support-requests/45/.

Yes, I did found a way to work around it as show in the commented out line in my test case:

// to get this to work, we cannot just use the default reader like we did with the default writer, we need to "disable" the escape char
// CSVReader csvReader = new CSVReaderBuilder(new FileReader(file)).withCSVParser(new CSVParserBuilder().withEscapeChar((char)0).build()).build();

Which I think is very similar to your suggested work around.

I guess my question is, why default to using "\" as escape for the CsvParser? Can we have no escape char by default?

[Scott Conway]

The CSVParser was made from the original parsing code in CSVReader. To change the defaults would break backwards compatibility so I leave it alone.

Personally if you have an issue with the escape character instead of setting it to null I would recommend the RFC4180Parser.

[Scott Conway]

Okay - sorry I am surprised I forgot this because it bit us a couple of years ago.

So the default escape character for the CSVReader is the slash \ but for some reason the default escape character for the CSVWriter is the double quote.

To have the reader and writer both work the same just change the escape character to a slash and it will work - albeit if you look at the file you will see two slashes.

Sorry but I am not going to change that one because it will break backwards compatibility. Namely for the people who really wanted quotes to always be escaped with quotes.

Attached is a test that shows the solution. I will leave this in for future reference and to expand on as items like this will most likely come up again.