CSV and Excel file processing

Business systems constantly import and export CSV and Excel files.

🔎 Unravel this example Prerequisites (1)

Learn these first

Files and I/O

At a glance

Mental model

Reading handwritten forms from many people.

What happens, in order

1Apache Commons CSV: CSVFormat.DEFAULT.builder().setHeader().setSkipHeaderRecord(true).build().parse(reader) gives CSVRecords read by header nameOpenCSV maps rows to beans with @CsvBindByName.
2Apache POI reads and writes Excel: WorkbookFactory.create(file), sheet.getRow(i).getCell(j); a DataFormatter converts any cell to the text a user seesFor huge exports SXSSFWorkbook streams rows to disk.
3Import flow: parse a row, validate it, collect the errors with the row number, write good rows in batches, and return a report instead of failing the entire file on the first bad line
4Always read with an explicit charset (StandardCharsets.UTF_8, or ISO-8859-1/windows-1252 for old Excel files) and close the reader

Remember

Splitting on commas (line.split(",")) breaks on quoted fields, embedded newlines and escaped quotes
Excel cells are typed: a date is a number with a format, a phone number may arrive as a double
Excel (.xlsx) is a zip of XML parts

Watch out

Splitting lines on commasLoading huge xlsx fully into memoryGuessing the character encodingExporting unescaped user text that begins with an equals sign

Example (highlighted lines explain it)

try (Reader reader = Files.newBufferedReader(path, StandardCharsets.UTF_8);     CSVParser parser = CSVFormat.DEFAULT.builder().setHeader().setSkipHeaderRecord(true).build().parse(reader)) {    for (CSVRecord row : parser) {        String sku = row.get("sku");        int qty = Integer.parseInt(row.get("quantity").trim());        stock.update(sku, qty);    }}

line 2 uses CSVParser, the core of CSV and Excel file processing
line 3 uses CSVRecord, the core of CSV and Excel file processing

Read the full explanation (about 2 min)

Business systems constantly import and export CSV and Excel files. Real files have quoted commas, wrong encodings, empty cells and millions of rows, so a parsing library and streaming matter.

A mental model

Reading handwritten forms from many people. Each person writes dates, numbers and blanks slightly differently, so you read carefully and check each box before filing it.

How it works

Apache Commons CSV: CSVFormat.DEFAULT.builder().setHeader().setSkipHeaderRecord(true).build().parse(reader) gives CSVRecords read by header name. OpenCSV maps rows to beans with @CsvBindByName.

Apache POI reads and writes Excel: WorkbookFactory.create(file), sheet.getRow(i).getCell(j); a DataFormatter converts any cell to the text a user sees. For huge exports SXSSFWorkbook streams rows to disk.

Import flow: parse a row, validate it, collect the errors with the row number, write good rows in batches, and return a report instead of failing the entire file on the first bad line.

Always read with an explicit charset (StandardCharsets.UTF_8, or ISO-8859-1/windows-1252 for old Excel files) and close the reader.

Going deeper

Splitting on commas (line.split(",")) breaks on quoted fields, embedded newlines and escaped quotes. Use a real parser.

Excel cells are typed: a date is a number with a format, a phone number may arrive as a double. Read by cell type, and be careful with leading zeros and scientific notation.

CSV injection: a cell beginning with =, +, - or @ in an export is run as a formula when opened in Excel. Prefix such values with an apostrophe when exporting user-provided text.

Under the hood

Excel (.xlsx) is a zip of XML parts. XSSFWorkbook loads the whole object model into memory (several times the file size), whereas the streaming reader (XSSFReader with SAX) and SXSSFWorkbook keep a sliding window of rows in memory, which is the difference between a working 1 million row export and an OutOfMemoryError.

Common mistakes

Splitting lines on commas.
Loading huge xlsx fully into memory.
Guessing the character encoding.
Exporting unescaped user text that begins with an equals sign.

How Unravel recognises it

When you paste code, Unravel looks for these signs of csv and excel file processing. Spotting them yourself is a good habit when you read unfamiliar code:

Annotations: @CsvBindByName @CsvBindByPosition
Types: CSVParser CSVFormat CSVPrinter CSVRecord CSVReader CSVWriter CsvToBeanBuilder CsvMapper CsvSchema XSSFWorkbook HSSFWorkbook SXSSFWorkbook Workbook WorkbookFactory and 6 more

Part of a learning path

Scheduled jobs, batch and integration · previous: Spring Batch jobs, steps and chunks · next: Spring Integration (channels and flows)

Related concepts

Spring Batch jobs, steps and chunksSpring Files and I/OI/O and data

Frequently asked questions

What is CSV and Excel file processing?

Business systems constantly import and export CSV and Excel files. Real files have quoted commas, wrong encodings, empty cells and millions of rows, so a parsing library and streaming matter.

What are the most common mistakes with CSV and Excel file processing?

Splitting lines on commas. Loading huge xlsx fully into memory. Guessing the character encoding. Exporting unescaped user text that begins with an equals sign.

How can I see CSV and Excel file processing in real code?

Paste your own Java into Unravel and it will point out where csv and excel file processing appears, explain how it works at the detail level you choose, and draw the flow of the code around it.