Apache Commons logo Commons CSV™ logo

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:

final URL url = ...;
final Reader reader = new InputStreamReader(new BOMInputStream(url.openStream()), "UTF-8");
final CSVParser parser = new CSVParser(reader, CSVFormat.EXCEL.withHeader());
try {
    for (final CSVRecord record : parser) {
        final String string = record.get("SomeColumn");
        ...
    }
} finally {
    parser.close();
    reader.close();
}

You might find it handy to create something like this:

/**
 * Creates a reader capable of handling BOMs.
 */
public InputStreamReader newReader(final InputStream inputStream) {
    return new InputStreamReader(new BOMInputStream(inputStream), StandardCharsets.UTF_8);
}

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.withHeader("H1", "H2").print(out)

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

final ResultSet resultSet = ...;
final CSVPrinter printer = CSVFormat.DEFAULT.withHeader(resultSet).print(out)