Java CSVReader 忽略双引号内的逗号

Question

我有一个CSV文件，但是在解析时遇到了困难。我正在使用opencsv库。这是我的数据样子和我想要实现的目标。

RPT_PE,CLASS,RPT_MKT,PROV_CTRCT,CENTER_NM,GK_TY,MBR_NM,MBR_PID "20150801","NULL","33612","00083249P PCP602","JOE SMITH ARNP","NULL","FRANK, LUCAS E","50004655200"

我遇到的问题是成员名（"FRANK, LUCAS E"）被拆分成两列，而成员名应该是一列。同样，我正在使用逗号作为分隔符来解析，是否有办法忽略双引号内的逗号？

        public void loadCSV(String csvFile, String tableName,
            boolean truncateBeforeLoad) throws Exception {

        CSVReader csvReader = null;
        if (null == this.connection) {
            throw new Exception("Not a valid connection.");
        }
        try {

            csvReader = new CSVReader(new FileReader(csvFile), this.seprator);

        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("Error occured while executing file. "
                    + e.getMessage());
        }
        String[] headerRow = csvReader.readNext();

        if (null == headerRow) {
            throw new FileNotFoundException(
                    "No columns defined in given CSV file."
                    + "Please check the CSV file format.");
        }

        String questionmarks = StringUtils.repeat("?,", headerRow.length);
        questionmarks = (String) questionmarks.subSequence(0, questionmarks
                .length() - 1);

        String query = SQL_INSERT.replaceFirst(TABLE_REGEX, tableName);
        System.out.println("Base Query: " + query);
        String headerRowMod = Arrays.toString(headerRow).replaceAll(", ]", "]");
        String[] strArray = headerRowMod.split(",");

        query = query
                .replaceFirst(KEYS_REGEX, StringUtils.join(strArray, ","));

        System.out.println("Add Headers: " + query);
        query = query.replaceFirst(VALUES_REGEX, questionmarks);
        System.out.println("Add questionmarks: " + query);

        String[] nextLine;
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = this.connection;
            con.setAutoCommit(false);
            ps = con.prepareStatement(query);

            if (truncateBeforeLoad) {
                //delete data from table before loading csv
                con.createStatement().execute("DELETE FROM " + tableName);
            }

            final int batchSize = 1000;
            int count = 0;
            Date date = null;
            while ((nextLine = csvReader.readNext()) != null) {
                System.out.println("Next Line: " + Arrays.toString(nextLine));
                if (null != nextLine) {
                    int index = 1;
                    for (String string : nextLine) {
                        date = DateUtil.convertToDate(string);
                        if (null != date) {
                            ps.setDate(index++, new java.sql.Date(date
                                    .getTime()));
                        } else {
                            ps.setString(index++, string);
                        }
                    }
                    ps.addBatch();
                }
                if (++count % batchSize == 0) {
                    ps.executeBatch();
                }
            }
            ps.executeBatch(); // insert remaining records
            con.commit();
        } catch (SQLException | IOException e) {
            con.rollback();
            e.printStackTrace();
            throw new Exception(
                    "Error occured while loading data from file to database."
                    + e.getMessage());
        } finally {
            if (null != ps) {
                ps.close();
            }
            if (null != con) {
                con.close();
            }
            csvReader.close();
        }
    }

    public char getSeprator() {
        return seprator;
    }

    public void setSeprator(char seprator) {
        this.seprator = seprator;
    }

    public char getQuoteChar() {
        return quoteChar;
    }

    public void setQuoteChar(char quoteChar) {
        this.quoteChar = quoteChar;
    }
}

- KalebD

---

根据CSVReader文档，此情况应得到处理。请发布您代码的关键部分。 - Eric J.

看我的代码示例。 - KalebD

我写了一个简单的程序，对我来说似乎工作正常。与额外的列相比，我得到了带逗号的全名，这是预期的。你可以尝试一下，它可能会给你一点线索。 - Jegg

Answer 1

你尝试过以下操作吗？

CSVReader reader = new CSVReader(new FileReader("yourfile.csv"), ',');

我编写了下面的程序，它对我有用，我得到了以下结果：

[20150801] [NULL] [33612] [00083249P PCP602] [JOE SMITH ARNP] [NULL] [FRANK，LUCAS E] [50004655200]

import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;

import au.com.bytecode.opencsv.CSVReader;

public class CVSTest {

    /**
     * @param args
     */
    public static void main(String[] args) {
        CSVReader reader = null;
        try {

            reader = new CSVReader(new FileReader(
                    "C:/Work/Dev/Projects/Pure_Test/Test/src/cvs"), ',');
        } catch (FileNotFoundException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }
        String[] nextLine;
        try {
            while ((nextLine = reader.readNext()) != null) {
                // nextLine[] is an array of values from the line
                System.out.println("[" + nextLine[0] + "] [" + nextLine[1]
                        + "] [" + nextLine[2] + "] [" + nextLine[3] + "] ["
                        + nextLine[4] + "] [" + nextLine[5] + "] ["
                        + nextLine[6] + "] [" + nextLine[7] + "]");
            }
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

    }

}

- Jegg

---

这不起作用是因为csvreader正在寻找一个字符，而不是一个字符串。 - KalebD

Answer 2

根据文档，您可以在构造函数中提供自定义分隔符和引号字符来处理它：

CSVReader(Reader reader, char separator, char quotechar)

使用逗号作为分隔符，双引号作为引用字符来构建您的读取器。

- Ankit

---

我尝试了这个方法，但它仍然将名称拆分成两列。 - KalebD

Answer 3

您的情况应该可以直接处理，无需进行特殊配置。

如果您无法使其正常运行，则只需切换到 uniVocity-parsers 来代替 - 它比 OpenCSV 快两倍，在代码方面需要更少，并且包含了许多功能。

CsvParserSettings settings = new CsvParserSettings();     // you have many configuration options here - check the tutorial.

CsvParser parser = new CsvParser(settings);

List<String[]> allRows = parser.parseAll(new FileReader(new File("C:/Work/Dev/Projects/Pure_Test/Test/src/cvs")));

声明：本库的作者是我。它是开源和免费的（Apache V2.0许可证）。

- Jeronimo Backes

Answer 4

将CSV文件作为SQL表加载到HSQLDB中并选择要插入到另一个数据库中的行非常简单。HSQLDB可以处理引号内的逗号。您需要将文本源定义为“带引号”。请参见以下内容：

http://hsqldb.org/doc/2.0/guide/texttables-chapt.html

- fredt