Apache Commons CSV User Guide

Apache Commons CSV User Guide
- Parsing files
  - Example: Parsing an Excel CSV File
  - Handling Byte Order Marks
- Working with headers

Parsing files

Parsing files with Apache Commons CSV is relatively straight forward. The CSVFormat class provides some commonly used CSV variants:

DEFAULT: Standard Comma Separated Value format, as for RFC4180 but allowing empty lines.
EXCEL: The Microsoft Excel CSV format.
INFORMIX_UNLOAD^1.3: Informix UNLOAD format used by the UNLOAD TO file_name operation.
INFORMIX_UNLOAD_CSV^1.3: Informix CSV UNLOAD format used by the UNLOAD TO file_name operation (escaping is disabled.)
MONGO_CSV^1.7: MongoDB CSV format used by the mongoexport operation.
MONGO_TSV^1.7: MongoDB TSV format used by the mongoexport operation.
MYSQL: The MySQL CSV format.
ORACLE^1.6: Default Oracle format used by the SQL*Loader utility.
POSTGRESSQL_CSV^1.5: Default PostgreSQL CSV format used by the COPY operation.
POSTGRESSQL_TEXT^1.5: Default PostgreSQL text format used by the COPY operation.
RFC-4180: The RFC-4180 format defined by RFC-4180.
TDF: A tab delimited format.

Example: Parsing an Excel CSV File

To parse an Excel CSV file, write:

Reader in = new FileReader("path/to/file.csv");
Iterable<CSVRecord> records = CSVFormat.EXCEL.parse(in);
for (CSVRecord record : records) {
    String lastName = record.get("Last Name");
    String firstName = record.get("First Name");
}

Handling Byte Order Marks

To handle files that start with a Byte Order Mark (BOM) like some Excel CSV files, you need an extra step to deal with these optional bytes. You can use the BOMInputStream class from Apache Commons IO for example:


try (Reader reader = new InputStreamReader(BOMInputStream.builder()
        .setPath(path)
        .get(), "UTF-8");
        CSVParser parser = CSVFormat.EXCEL.builder()
                .setHeader()
                .get()
                .parse(reader)) {
    for (final CSVRecord record : parser) {
        final String string = record.get("ColumnA");
        // ...
    }
}

You might find it handy to create something like this:


/**
 * Creates a reader capable of handling BOMs.
 *
 * @param path The path to read.
 * @return a new InputStreamReader for UTF-8 bytes.
 * @throws IOException if an I/O error occurs.
 */
public InputStreamReader newReader(final Path path) throws IOException {
    return new InputStreamReader(BOMInputStream.builder()
            .setPath(path)
            .get(), StandardCharsets.UTF_8);
}

Working with headers

Apache Commons CSV provides several ways to access record values. The simplest way is to access values by their index in the record. However, columns in CSV files often have a name, for example: ID, CustomerNo, Birthday, etc. The CSVFormat class provides an API for specifying these header names and CSVRecord on the other hand has methods to access values by their corresponding header name.

Accessing column values by index

To access a record value by index, no special configuration of the CSVFormat is necessary:

Reader in = new FileReader("path/to/file.csv");
Iterable<CSVRecord> records = CSVFormat.RFC4180.parse(in);
for (CSVRecord record : records) {
    String columnOne = record.get(0);
    String columnTwo = record.get(1);
}

Defining a header manually

Indices may not be the most intuitive way to access record values. For this reason it is possible to assign names to each column in the file:

Reader in = new FileReader("path/to/file.csv");
Iterable<CSVRecord> records = CSVFormat.RFC4180.builder()
  .setHeader("ID", "CustomerNo", "Name")
  .build()
  .parse(in);
for (CSVRecord record : records) {
    String id = record.get("ID");
    String customerNo = record.get("CustomerNo");
    String name = record.get("Name");
}

Note that column values can still be accessed using their index.

Using an enum to define a header

Using String values all over the code to reference columns can be error prone. For this reason, it is possible to define an enum to specify header names. Note that the enum constant names are used to access column values. This may lead to enums constant names which do not follow the Java coding standard of defining constants in upper case with underscores:

public enum Headers {
    ID, CustomerNo, Name
}
Reader in = new FileReader("path/to/file.csv");
Iterable<CSVRecord> records = CSVFormat.RFC4180.builder()
  .setHeader(Headers.class)
  .build()
  .parse(in);
for (CSVRecord record : records) {
    String id = record.get(Headers.ID);
    String customerNo = record.get(Headers.CustomerNo);
    String name = record.get(Headers.Name);
}

Again it is possible to access values by their index and by using a String (for example "CustomerNo").

Header auto detection

Some CSV files define header names in their first record. If configured, Apache Commons CSV can parse the header names from the first record:

Reader in = new FileReader("path/to/file.csv");
Iterable<CSVRecord> records = CSVFormat.RFC4180.builder()
  .setHeader()
  .setSkipHeaderRecord(true)
  .build()
  .parse(in);
for (CSVRecord record : records) {
    String id = record.get("ID");
    String customerNo = record.get("CustomerNo");
    String name = record.get("Name");
}

This will use the values from the first record as header names and skip the first record when iterating.

Printing with headers

To print a CSV file with headers, you specify the headers in the format:

final Appendable out = ...;
final CSVPrinter printer = CSVFormat.DEFAULT.builder()
  .setHeader("H1", "H2")
  .build()
  .print(out);

To print a CSV file with JDBC column labels, you specify the ResultSet in the format:

try (final ResultSet resultSet = ...) {
  final CSVPrinter printer = CSVFormat.DEFAULT.builder()
    .setHeader(resultSet)
    .build()
    .print(out);
}