Reading a CSV File in Java With OpenCSV and Commons CSV
Read CSV in Java with OpenCSV and Commons CSV: a backslash escape that breaks paths, ints that turn blanks into 0, and a writer that quotes everything.
In Java there are two libraries you will meet for CSV: OpenCSV, with a reader that returns String[] and an annotation-based bean mapper, and Apache Commons CSV, with a CSVFormat you configure. Both parse quoted fields correctly, both are small, and both have a default or two that is wrong for the file in front of you. The one I fell over first is a backslash. This article reads the same files as the rest of this series with OpenCSV 5.12.0 and Commons CSV 1.14.1, shows the failures, and times the reading of 2,000,000 rows. The comparison with other languages is in the guide.
Reading records
CSVReader wraps any Reader. readNext() returns the next record as a String[], or null at the end:
case "people" -> {
try (CSVReader r = new CSVReader(new FileReader(a[1], StandardCharsets.UTF_8))) {
String[] row;
while ((row = r.readNext()) != null)
System.out.println("line " + r.getLinesRead() + ": " + Arrays.toString(row).replace("\n", "\\n"));
}
}mvn -q dependency:copy-dependencies -DoutputDirectory=/tmp/lib >/dev/null 2>&1; javac -cp '/tmp/lib/*' -d /tmp/classes Lab.java; java -cp '/tmp/classes:/tmp/lib/*' Lab people ../data/people.csvline 1: [id, name, city, note]
line 2: [1, Ana Silva, Lisbon, likes tea]
line 3: [2, Smith, Jane, Boston, said "hello"]
line 5: [3, Bo Chen, Oslo, line one\nline two]
line 6: [4, Cy Dubois, Lyon, ]getLinesRead() is the number of lines consumed so far, which is why it reports 5 for the record that ends on line 5 and 6 for the next. Print Arrays.toString and the commas inside Smith, Jane look like separators, so it is worth checking row.length, which was 4. The versions in use:
mvn -q dependency:copy-dependencies -DoutputDirectory=/tmp/lib >/dev/null 2>&1; javac -cp '/tmp/lib/*' -d /tmp/classes Lab.java; java -cp '/tmp/classes:/tmp/lib/*' Lab version; ls /tmp/lib | grep -E 'opencsv|commons-csv'21.0.12.1 / OpenJDK 64-Bit Server VM
commons-csv-1.14.1.jar
opencsv-5.12.0.jarAlways pass a charset when you open the file. new FileReader(path) uses the platform default on older JDKs, and new FileReader(path, StandardCharsets.UTF_8) states the intent.
The escape character
OpenCSV's parser has an escape character, and the default is a backslash. In RFC 4180 CSV there is no such thing: a quote inside a quoted field is written twice. So a backslash is just a character, and in some files it is a very common one. Consider a field holding a Windows path that ends in a backslash:
case "escape" -> {
String text = "id,path\n1,\"C:\\temp\\\"\n2,plain\n";
System.out.println("file: " + text.replace("\n", "\\n"));
for (char esc : new char[] {'\\', '\0'}) {
CSVParser p = new CSVParserBuilder().withEscapeChar(esc).build();
try (CSVReader r = new CSVReaderBuilder(new StringReader(text)).withCSVParser(p).build()) {
List<String[]> all = r.readAll();
StringBuilder sb = new StringBuilder();
for (String[] row : all) sb.append(Arrays.toString(row).replace("\n", "\\n"));
System.out.println("escape=" + (esc == 0 ? "NULL" : "'\\\\'") + " -> " + sb);
} catch (Exception e) { System.out.println("escape=" + (esc == 0 ? "NULL" : "'\\\\'") + " -> " + e.getClass().getSimpleName() + ": " + e.getMessage()); }
}
}mvn -q dependency:copy-dependencies -DoutputDirectory=/tmp/lib >/dev/null 2>&1; javac -cp '/tmp/lib/*' -d /tmp/classes Lab.java; java -cp '/tmp/classes:/tmp/lib/*' Lab escapefile: id,path\n1,"C:\temp\"\n2,plain\n
escape='\\' -> CsvMalformedLineException: Unterminated quoted field at end of CSV line. Beginning of lost text: [C:temp"
2,plain
]
escape=NULL -> [id, path][1, C:\temp\][2, plain]The file contains 1,"C:\temp\". With the default escape character, the final \" is read as an escaped quote, not a closing one, and the parser went on looking for the end of the field: CsvMalformedLineException: Unterminated quoted field at end of CSV line, with the lost text shown (C:temp" and the next line, with the backslash already consumed). With the escape set to the NUL character, the parse gave [id, path], [1, C:\temp\] and [2, plain]. If your data has backslashes, set withEscapeChar('\0') on the CSVParserBuilder. If your data comes from a source that does use backslash escapes (MySQL's SELECT INTO OUTFILE, for one), leave it and know why.
Field counts and bad quotes
OpenCSV does not check the number of fields in a record:
case "widths" -> {
try (CSVReader r = new CSVReader(new FileReader(a[1], StandardCharsets.UTF_8))) {
int n = 0; String[] row;
while ((row = r.readNext()) != null) { n++; System.out.print(row.length + " "); }
System.out.println("\nrecords: " + n + " (field counts are not checked)");
}
}
case "unterminated" -> {
try (CSVReader r = new CSVReader(new FileReader(a[1], StandardCharsets.UTF_8))) {
String[] row; int n = 0;
while ((row = r.readNext()) != null) n++;
System.out.println("records: " + n);
} catch (CsvMalformedLineException e) {
System.out.println(e.getClass().getSimpleName() + ": " + e.getMessage() + " (line " + e.getLineNumber() + ")");
}
}mvn -q dependency:copy-dependencies -DoutputDirectory=/tmp/lib >/dev/null 2>&1; javac -cp '/tmp/lib/*' -d /tmp/classes Lab.java; java -cp '/tmp/classes:/tmp/lib/*' Lab widths ../data/ragged2.csv4 4 5 4 5 3 4 1 4
records: 9 (field counts are not checked)The ragged file came back as 9 records of widths 4 4 5 4 5 3 4 1 4, with no error. If a column count matters, check row.length yourself and keep the line number from getLinesRead(). A quote that never closes is an error with a line number:
mvn -q dependency:copy-dependencies -DoutputDirectory=/tmp/lib >/dev/null 2>&1; javac -cp '/tmp/lib/*' -d /tmp/classes Lab.java; java -cp '/tmp/classes:/tmp/lib/*' Lab unterminated ../data/unterminated.csvCsvMalformedLineException: Unterminated quoted field at end of CSV line. Beginning of lost text: [oops this quote never closes
3,Name3,some ordinary note for row 3
4,Name4,some ordinary note for ...] (line 3)CsvMalformedLineException: Unterminated quoted field at end of CSV line. Beginning of lost text: [oops this quote never closes ...] (line 3). The line is the one where the quote opened, and the text it quotes lets you find it. That is one of the better messages of the parsers in this series.
The BOM stays
A file starting with the three bytes EF BB BF gives a first header with an invisible character:
case "bom" -> {
try (CSVReader r = new CSVReader(new FileReader(a[1], StandardCharsets.UTF_8))) {
System.out.println("header: " + Arrays.toString(r.readNext()));
}
try (CSVReader r = new CSVReader(new InputStreamReader(new BOMStrip(new FileInputStream(a[1])), StandardCharsets.UTF_8))) {
System.out.println("after skipping 3 bytes: " + Arrays.toString(r.readNext()));
}
}mvn -q dependency:copy-dependencies -DoutputDirectory=/tmp/lib >/dev/null 2>&1; javac -cp '/tmp/lib/*' -d /tmp/classes Lab.java; java -cp '/tmp/classes:/tmp/lib/*' Lab bom ../data/bom.csvheader: [id, name]
after skipping 3 bytes: [id, name]The header printed as [id, name], where the first element begins with U+FEFF. Skipping three bytes on the input stream before decoding, as the second call does, gave [id, name]. Apache Commons CSV has the same issue, and its documentation points to BOMInputStream from Commons IO for it; I did not test that route. Whichever you use, do it once in a helper that opens files.
Beans, and a quiet default
OpenCSV can map records to objects with annotations. For the ZIP file, once with a String field and once with an int:
case "beans" -> {
try (Reader r = new FileReader(a[1])) {
System.out.println(new CsvToBeanBuilder<Zip>(r).withType(Zip.class).build().parse());
}
try (Reader r = new FileReader(a[1])) {
System.out.println(new CsvToBeanBuilder<ZipInt>(r).withType(ZipInt.class).build().parse());
}
}mvn -q dependency:copy-dependencies -DoutputDirectory=/tmp/lib >/dev/null 2>&1; javac -cp '/tmp/lib/*' -d /tmp/classes Lab.java; java -cp '/tmp/classes:/tmp/lib/*' Lab beans ../data/zips.csv[1 zip=02134 MA, 2 zip=90210 CA, 3 zip=00501 NY, 4 zip=10001 NY]
[1 zip=2134, 2 zip=90210, 3 zip=501, 4 zip=10001]With String zip the values are 02134, 90210, 00501, 10001. With int zip they are 2134, 90210, 501, 10001: the leading zeros are gone, the same trap as in every typed reader. A blank cell is quieter still:
mvn -q dependency:copy-dependencies -DoutputDirectory=/tmp/lib >/dev/null 2>&1; javac -cp '/tmp/lib/*' -d /tmp/classes Lab.java; java -cp '/tmp/classes:/tmp/lib/*' Lab beans-blank ../data/zips-blank.csv[1 zip=2134, 2 zip=0, 3 zip=501]With withThrowExceptions(false), the file with an empty ZIP produced 2 zip=0 for the second record, and the list of captured exceptions was empty. An empty int field became 0, silently, in a record where the ZIP was merely missing. If a zero is a legal value in your data, the two cases can no longer be told apart. Use Integer instead of int for any column that can be empty, or String plus your own parsing, and treat "no exception" as no evidence.
Writing, and a writer that quotes everything
CSVWriter's default is to put quotes around every field. Compare the three configurations I tried:
case "write" -> {
StringWriter sw = new StringWriter();
try (CSVWriter w = new CSVWriter(sw)) {
w.writeNext(new String[] {"id", "name", "note"});
w.writeNext(new String[] {"1", "Smith, Jane", "said \"hi\""});
w.writeNext(new String[] {"2", "Zoë", "line one\nline two"});
w.writeNext(new String[] {"3", " padded", null});
}
System.out.println("default CSVWriter:\n" + sw.toString().replace("\r\n", "<CRLF>\n").replace("\n\"", "\n\""));
StringWriter sw2 = new StringWriter();
try (CSVWriter w = new CSVWriter(sw2, ',', CSVWriter.NO_QUOTE_CHARACTER, CSVWriter.DEFAULT_ESCAPE_CHARACTER, "\n")) {
w.writeNext(new String[] {"1", "Smith, Jane", "said \"hi\""});
}
System.out.println("NO_QUOTE_CHARACTER:\n" + sw2);
StringWriter sw3 = new StringWriter();
try (CSVWriter w = new CSVWriter(sw3, ',', '"', '"', "\n")) {
w.writeNext(new String[] {"1", "Smith, Jane", "said \"hi\""}, false);
w.writeNext(new String[] {"2", "Bo", "plain"}, false);
}
System.out.println("quote only when needed? (applyQuotesToAll=false), escape = quote:\n" + sw3);
}mvn -q dependency:copy-dependencies -DoutputDirectory=/tmp/lib >/dev/null 2>&1; javac -cp '/tmp/lib/*' -d /tmp/classes Lab.java; java -cp '/tmp/classes:/tmp/lib/*' Lab writedefault CSVWriter:
"id","name","note"
"1","Smith, Jane","said ""hi"""
"2","Zoë","line one
line two"
"3"," padded",
NO_QUOTE_CHARACTER:
1,Smith", Jane,said ""hi""
quote only when needed? (applyQuotesToAll=false), escape = quote:
1,"Smith, Jane","said ""hi"""
2,Bo,plainThe default output is "id","name","note" and "1","Smith, Jane","said ""hi""", every field quoted, including the numbers; a null is an unquoted empty field. That is valid CSV, and it is not what most readers expect from a file with a column of ids. Passing NO_QUOTE_CHARACTER is a trap: it produced 1,Smith", Jane,said ""hi"", a quote character inserted before the comma and the doubled quotes left in, which no reader will accept. The useful configuration is the third: writeNext(row, false) with '"' as the escape character, which quoted only "Smith, Jane" and "said ""hi""". Set the escape to the quote character for RFC 4180 output.
Speed on 2,000,000 rows
Three ways to read the orders file and sum the amount column:
case "sum" -> {
long t = System.nanoTime(); double total = 0; long rows = 0;
if (a[2].equals("opencsv")) {
try (CSVReader r = new CSVReader(new BufferedReader(new FileReader(a[1], StandardCharsets.UTF_8), 1 << 20))) {
r.readNext(); String[] row;
while ((row = r.readNext()) != null) { total += Double.parseDouble(row[3]); rows++; }
}
} else if (a[2].equals("commons")) {
try (Reader in = new BufferedReader(new FileReader(a[1], StandardCharsets.UTF_8), 1 << 20)) {
for (var rec : org.apache.commons.csv.CSVFormat.RFC4180.builder().setHeader().setSkipHeaderRecord(true).get().parse(in)) {
total += Double.parseDouble(rec.get(3)); rows++;
}
}
} else {
try (BufferedReader br = new BufferedReader(new FileReader(a[1], StandardCharsets.UTF_8), 1 << 20)) {
br.readLine(); String l;
while ((l = br.readLine()) != null) { total += Double.parseDouble(l.split(",", -1)[3]); rows++; }
}
}
System.out.printf("%-8s rows=%d total=%.2f time=%.2fs peak_rss=%dMB%n", a[2], rows, total, (System.nanoTime() - t) / 1e9, peakRssMb());
}mvn -q dependency:copy-dependencies -DoutputDirectory=/tmp/lib >/dev/null 2>&1; javac -cp '/tmp/lib/*' -d /tmp/classes Lab.java; for m in opencsv commons split; do java -cp '/tmp/classes:/tmp/lib/*' Lab sum ../data/orders-2m.csv $m; doneopencsv rows=2000000 total=160174646.36 time=1.13s peak_rss=240MB
commons rows=2000000 total=160174646.36 time=1.15s peak_rss=198MB
split rows=2000000 total=160174646.36 time=0.65s peak_rss=285MBOpenCSV's readNext took 1.13 s; Commons CSV with a header record took 1.15 s; reading lines and calling split(","), which is only correct because this file has no quotes, took 0.65 s. All three got 160174646.36. The resident sizes (240, 198 and 285 MB) say more about the JVM's heap sizing than about the libraries, since each streams one record at a time; set -Xmx to measure real needs. The 0.5 s that a correct parser costs on this file is the price of quote handling, and it is a good price.
How to tell it worked
Compare the record count with another tool's count. Print row.length for each record, or at least the set of distinct lengths. Open a file with a backslash and a file with a BOM. For beans, read a file with a blank cell and check what you got. For the writer, read the output back with a different parser. And keep the library versions in the build file, since defaults have moved between OpenCSV releases.
Defaults that surprised me
A backslash escape that RFC 4180 does not have, an int that turns an empty cell into 0, a BOM that stays in the header, a writer that quotes everything and a NO_QUOTE_CHARACTER mode that corrupts the output. None of them is hidden; each is documented, and each is the sort of thing you only read about after it bites. For one-off analysis of a large file, DuckDB removes the question. For an import into a database, the bulk loader is faster than any Java loop, as the PostgreSQL article shows.
