Parsing CSV

Parse and write CSV correctly, including quoted fields, byte order marks and bad rows, and know when a library is the better choice.

Syntax

String[] fields = line.split(",", -1);   // only for unquoted data
try (BufferedReader in = Files.newBufferedReader(path)) {
    String header = in.readLine();
    // parse each following line into a record
}

CSV is still how spreadsheets, finance systems and bulk imports exchange data. It looks like line.split(","), and that works right up until the first customer called Smith, Jane.

What the format really allows

RFC 4180 is the closest thing to a standard. A field that contains a comma, a double quote or a line break is wrapped in double quotes, and a quote inside such a field is written twice:

id,name,note
1001,"Smith, Jane","She said ""hi"""

So a correct parser has to track whether it is inside quotes. A quoted field may even span several physical lines, which means you cannot always process one readLine() at a time.

The mistakes people make with split

Quoted commas. split(",") breaks "Smith, Jane" into two fields and keeps the quote characters.
Trailing empty fields vanish. "a,b,,".split(",") has two elements, not four. Pass a limit of -1 to keep them.
The byte order mark. Files saved from Excel as "CSV UTF-8" start with U+FEFF, so the first header is not id but an invisible character followed by id.
Line endings. Windows files use CRLF. BufferedReader.readLine handles both, but a hand-written split on \n leaves a \r on every last field.

Imports in a service

Treat an uploaded CSV as untrusted input. Check the header, map each row to a record, validate it, and collect errors with their line numbers instead of stopping at the first bad row. Users fix a file far faster from a list like line 14: email is required than from a 500 response. Stream the rows with a BufferedReader so a large upload does not fill the heap, and cap the number of rows you accept.

Exports

When writing CSV, quote any field that contains a comma, quote or line break, and double the quotes inside it. If people will open the file in a spreadsheet, values that start with =, +, - or @, or with a tab or carriage return, can be run as formulas, a problem known as CSV injection. Prefix such text values with a single quote so they display as text.

When to use a library

A short parser like the one below is fine for files you control, such as fixtures or exports from your own system. For files from customers and third parties, with multi-line fields, other delimiters and odd encodings, a maintained library such as Apache Commons CSV or Jackson's CSV module is less work than finding every edge case yourself.

Example

Loading editor…

Press Run to execute the code.

When to use it

Import a customer list uploaded through an admin screen and return every invalid row with its line number.
Export orders for the finance team as CSV that opens cleanly in a spreadsheet without running injected formulas.
Load test fixtures and seed data for a service from small CSV files kept in the repository.

More examples

Importing rows into records with line-numbered errors

NumberFormatException is a subclass of IllegalArgumentException, so it must be caught first. This file has no quoted fields, which is the only reason split is acceptable here; for customer files use a quote-aware parser.

record Customer(int id, String email, int credits) {}

void main() throws IOException {
    String upload = """
        id,email,credits
        1,[email protected],120
        2,[email protected],abc
        3,,15
        4,[email protected],40
        """;
    List<Customer> imported = new ArrayList<>();
    List<String> errors = new ArrayList<>();

    try (BufferedReader reader = new BufferedReader(new StringReader(upload))) {
        String header = reader.readLine();
        if (!"id,email,credits".equals(header)) {
            throw new IllegalArgumentException("unexpected header: " + header);
        }
        int lineNo = 1;
        String line;
        while ((line = reader.readLine()) != null) {
            lineNo++;
            if (line.isBlank()) {
                continue;
            }
            String[] f = line.split(",", -1);
            try {
                if (f.length != 3) {
                    throw new IllegalArgumentException("expected 3 fields, got " + f.length);
                }
                if (f[1].isBlank()) {
                    throw new IllegalArgumentException("email is required");
                }
                imported.add(new Customer(Integer.parseInt(f[0].strip()), f[1].strip(),
                        Integer.parseInt(f[2].strip())));
            } catch (NumberFormatException e) {
                errors.add("line " + lineNo + ": not a number (" + e.getMessage() + ")");
            } catch (IllegalArgumentException e) {
                errors.add("line " + lineNo + ": " + e.getMessage());
            }
        }
    }

    IO.println("imported " + imported.size() + " customers");
    errors.forEach(IO::println);
    IO.println("total credits: " + imported.stream().mapToInt(Customer::credits).sum());
}

// Output:
// imported 2 customers
// line 3: not a number (For input string: "abc")
// line 4: email is required
// total credits: 160

Writing CSV that survives commas, quotes and spreadsheets

Quoting only the fields that need it keeps the file readable while staying valid. The leading quote on the last note stops a spreadsheet from treating customer-supplied text as a formula when someone in finance opens the export.

record Order(long id, String customer, String note) {}

static String cell(String value) {
    String v = value;
    // Text starting with = + - @, a tab or a carriage return may be run as a formula.
    if (!v.isEmpty() && "=+-@\t\r".indexOf(v.charAt(0)) >= 0) {
        v = "'" + v;
    }
    if (v.contains(",") || v.contains("\"") || v.contains("\n") || v.contains("\r")) {
        v = "\"" + v.replace("\"", "\"\"") + "\"";
    }
    return v;
}

void main() {
    List<Order> orders = List.of(
            new Order(1001, "Smith, Jane", "Leave at door"),
            new Order(1002, "O'Brien", "Said \"ring twice\""),
            new Order(1003, "Mallory", "=SUM(A1:A9)"));

    StringBuilder csv = new StringBuilder("id,customer,note\n");
    for (Order o : orders) {
        csv.append(o.id()).append(',')
           .append(cell(o.customer())).append(',')
           .append(cell(o.note())).append('\n');
    }
    IO.print(csv);
}

// Output:
// id,customer,note
// 1001,"Smith, Jane",Leave at door
// 1002,O'Brien,"Said ""ring twice"""
// 1003,Mallory,'=SUM(A1:A9)

Byte order marks and Windows line endings

Java's UTF-8 decoder keeps the byte order mark as a character, so header checks fail on files that look perfect in an editor. readLine strips both CRLF and LF, so the row lengths show no stray carriage return.

void main() throws IOException {
    // What a "CSV UTF-8" export from a spreadsheet looks like once decoded.
    String exported = "\uFEFFid,name\r\n1,Ada\r\n2,Linus\r\n";
    try (BufferedReader reader = new BufferedReader(new StringReader(exported))) {
        String header = reader.readLine();
        IO.println("first column is id: " + header.split(",")[0].equals("id"));
        if (header.startsWith("\uFEFF")) {
            header = header.substring(1);
        }
        IO.println("after removing the BOM: " + header.split(",")[0].equals("id"));
        reader.lines().forEach(row -> IO.println("row " + row + " length " + row.length()));
    }
}

// Output:
// first column is id: false
// after removing the BOM: true
// row 1,Ada length 5
// row 2,Linus length 7

Discussion

Be the first to comment on this lesson.

What the format really allows

The mistakes people make with split

Imports in a service

Exports

When to use a library

Example

When to use it

More examples

Importing rows into records with line-numbered errors

Writing CSV that survives commas, quotes and spreadsheets

Byte order marks and Windows line endings

Related

Discussion