Text qualifier - invalid char between tokn encapsulated and delimiter

Question

If there is a comma in the field, but the whole is closed with quotation marks, then I should not treat it as a column divider. How can this be done?

Example aaaa, "bb,bb", cccc and I get aaaa | bb | bb |ccc

How can I receive aaaa | "bb,bb" | cccc ?

public List<CSVRecord> collectAllEntries(Path path) throws IOException {
        logger.info("Parsing the input file" + path);
        List<CSVRecord> store = new ArrayList<>();
        try (
                Reader reader = Files.newBufferedReader(path, Charset.forName("ISO-8859-2"));
                CSVParser csvParser = new CSVParser(reader, CSVFormat.EXCEL.withQuote(';'))
        ) {
            for (CSVRecord csvRecord : csvParser) {
                store.add(csvRecord);
            }
        } catch (IOException e) {
            e.printStackTrace();
            throw e;
        }
        return store;
    }

private void csvToXlsx(Path csvFilePath, Path excelFilePath) throws Exception {
    logger.info("Converting CSV to XLSX" + excelFilePath);
    List<CSVRecord> records = collectAllEntries(csvFilePath);
    XSSFWorkbook myWorkBook = new XSSFWorkbook();
    FileOutputStream writer = new FileOutputStream(new File(excelFilePath.toString()));
    XSSFSheet mySheet = myWorkBook.createSheet();
    IntStream.range(0, records.size())
            .forEach(rowNum -> {
                XSSFRow myRow = mySheet.createRow(rowNum);
                CSVRecord record = records.get(rowNum);
                for (int i = 0; i < record.size(); i++) {
                    XSSFCell myCell = myRow.createCell(i);
                    myCell.setCellValue(record.get(i));
                }
            });
        myWorkBook.write(writer);
        writer.close();
    }

Check for ", when it is detected you need to treat everything as strict string until another " is detected. You can use a state for this. — m0skit0, Mar 22 '21 at 09:57
`CSVFormat.EXCEL.withQuote(';')` that's not the right quote character. Better just use [CSVFormat.EXCEL](http://commons.apache.org/proper/commons-csv/apidocs/org/apache/commons/csv/CSVFormat.html#EXCEL) which already has the correct settings — Aaron, Mar 22 '21 at 09:59
@Aaron So I have to set it to include quote ? like this ? CSVParser csvParser = new CSVParser(reader, CSVFormat.EXCEL. withQuote('"').withDelimiter(';')) — user14797575, Mar 22 '21 at 10:52
I'd just use `CSVFormat.EXCEL`, check the link in my previous comment, you'll see its default configuration is appropriate for your data (`withDelimiter(',')`, `withQuote('"')`) — Aaron, Mar 22 '21 at 10:59
@Aaron Previously, I only used CSVFormat.EXCEL configuration but it did not work me. Could it be dependent on the encoding? Or maybe this method is wrong csvToXlsx? — user14797575, Mar 22 '21 at 11:10
If you wanted to convert your CSV file to the Excel XLSX format it's the right method. — Aaron, Mar 22 '21 at 11:52
@Aaron It turned out that I had the wrong set CSVPrinter in a different method. — user14797575, Mar 22 '21 at 14:28

score 1 · Accepted Answer · answered Jul 27 '21 at 19:26

 private void processOrderSet(HashMap<String, List<CSVRecord>> entries, FileWriter out, List<String> headers) throws IOException {
        try (CSVPrinter printer = new CSVPrinter(out, CSVFormat.EXCEL.withHeader(headers.toArray(new String[0])).withQuote('"').withDelimiter(';')))

....

score 0 · Answer 2 · answered Mar 23 '21 at 05:59

The following works for me when using the latest version of commons-csv-1.8:

    Reader in = new StringReader("aaaa,\"bb,bb\",cccc");
    Iterable<CSVRecord> records = CSVFormat.DEFAULT.withDelimiter(',').withQuote('"').parse(in);
    for (CSVRecord record : records) {
        for (int i = 0; i < record.size(); i++) {
            System.out.println("At " + i + ": " + record.get(i));
        }
    }

As well as using the predefined EXCEL format:

    Iterable<CSVRecord> records = CSVFormat.EXCEL.parse(in);

Text qualifier - invalid char between tokn encapsulated and delimiter

2 Answers2