How to Export and Import CSV in Spring Boot - Handling Character Encoding, Large Datasets, and Validation with OpenCSV

この記事で作るもの → Translated article body

Building enterprise systems, you’ll almost inevitably run into requirements like “I want to download the list as CSV” or “I want to import an uploaded CSV.” It looks simple, but once you start implementing it, you hit pitfalls: the file shows garbled characters when opened in Excel, memory overflows on hundreds of thousands of rows, and you struggle with how to handle malformed rows.

In this article, we’ll use OpenCSV to implement download/upload endpoints while avoiding these three common pitfalls. You can also read and write CSV with Jackson’s csv module, but we’ll build on OpenCSV, which makes Bean mapping via annotations and fine-grained control over behavior easier.

What we’ll build in this article

The goal is these two endpoints.

• A GET API that lets users download list data as CSV
• A POST API that parses and imports an uploaded CSV

On top of that, we’ll cover the three points you’ll always be asked about in practice: “no garbled characters in Excel,” “won’t crash on large row counts,” and “detects and returns malformed rows.” Let’s start by adding the dependencies.

Adding the OpenCSV dependency

We’ll use OpenCSV for CSV parsing and commons-io for stripping the BOM, so add both. For Maven, it looks like this.

<dependency>
    <groupId>com.opencsv</groupId>
    <artifactId>opencsv</artifactId>
    <version>5.9</version>
</dependency>
<dependency>
    <groupId>commons-io</groupId>
    <artifactId>commons-io</artifactId>
    <version>2.16.1</version>
</dependency>

For Gradle, it’s these two lines.

implementation 'com.opencsv:opencsv:5.9'
implementation 'commons-io:commons-io:2.16.1'

OpenCSV has the low-level CSVWriter / CSVReader, and StatefulBeanToCsv / CsvToBean, which handle Bean conversion. This time we want to map with annotations, so we’ll mainly use the latter. Think of it as dropping down to the low-level API only when you need fine-grained control over quote characters or delimiters.

Defining a Bean for CSV

@CsvBindByName maps CSV header names to fields by name, and @CsvBindByPosition maps them by column position. This time we want to handle both name-based mapping on import and column ordering on output, so we’ll add both.

public class UserCsv {

    @CsvBindByName(column = "ユーザーID")
    @CsvBindByPosition(position = 0)
    private Long id;

    @CsvBindByName(column = "氏名")
    @CsvBindByPosition(position = 1)
    private String name;

    @CsvBindByName(column = "メールアドレス")
    @CsvBindByPosition(position = 2)
    private String email;

    @CsvBindByName(column = "登録日")
    @CsvBindByPosition(position = 3)
    @CsvDate("yyyy/MM/dd")
    private LocalDate registeredAt;

    // getters / setters omitted
}

Let me share the most common pitfall up front. If you don’t explicitly specify a write strategy, StatefulBeanToCsvBuilder will automatically select ColumnPositionMappingStrategy (position-based) whenever the Bean has even one @CsvBindByPosition. And by default this strategy does not output a header row. In other words, if you leave both annotations on and write without specifying anything, the “ユーザーID/氏名/メールアドレス/登録日” header won’t appear at all, and you end up with a CSV of data rows only.

The read side is the same: when the position-based strategy is selected, the header row isn’t skipped, and the first line gets interpreted as data. So in this article, we avoid this auto-selection trap by explicitly passing a strategy for both download and upload.

Note that depending on the OpenCSV version, @CsvDate may require a converter setting for LocalDate. If only the date mapping breaks after you copy and paste, the fastest thing to suspect first is the correspondence between the format string and the type.

Letting users download a CSV

To write out the Bean with a header and a fixed column order, we prepare a strategy that extends ColumnPositionMappingStrategy and overrides only generateHeader. Since the data is arranged by the @CsvBindByPosition positions, returning header strings in the same order lines the two up neatly.

public class UserCsvWriteStrategy extends ColumnPositionMappingStrategy<UserCsv> {

    public UserCsvWriteStrategy() {
        setType(UserCsv.class);
    }

    @Override
    public String[] generateHeader(UserCsv bean) throws CsvRequiredFieldEmptyException {
        super.generateHeader(bean); // initialize the position mapping
        return new String[]{"ユーザーID", "氏名", "メールアドレス", "登録日"};
    }
}

The download itself just passes this strategy via withMappingStrategy. To avoid garbled characters in Excel, here we write in Shift_JIS (Windows-31J).

@GetMapping("/users/csv")
public void download(HttpServletResponse response) throws Exception {
    response.setContentType("text/csv;charset=Windows-31J");
    String filename = URLEncoder.encode("ユーザー一覧.csv", StandardCharsets.UTF_8)
            .replace("+", "%20");
    response.setHeader("Content-Disposition",
            "attachment; filename*=UTF-8''" + filename);

    Writer w = new OutputStreamWriter(
            response.getOutputStream(), Charset.forName("Windows-31J"));

    List<UserCsv> users = userService.findAllForCsv();
    var writer = new StatefulBeanToCsvBuilder<UserCsv>(w)
            .withMappingStrategy(new UserCsvWriteStrategy())
            .build();
    writer.write(users);
    w.flush();
}

If you write a Japanese filename directly in filename=, it gets garbled in some browsers, so encode it using RFC 5987’s filename*=UTF-8'' format. URLEncoder converts spaces to +, which strictly differs from percent-encoding, so correcting it with replace("+", "%20") keeps filenames with spaces intact. Explicitly setting charset in Content-Type also prevents garbling when opened in clients other than Excel. Writing throws Exception is a shortcut to keep the explanation brief; in production code it’s preferable to narrow down the specific exceptions you throw.

Character encoding and BOM to prevent garbled characters

The reason a UTF-8 CSV shows garbled characters when double-clicked in Excel is that Excel tries to interpret BOM-less UTF-8 as the system’s default code page (Shift_JIS in a Japanese environment).

There are two countermeasures. One, as in the download example above, is to output in Shift_JIS (which is really Windows-31J / MS932), which Excel reads straightforwardly. Strictly speaking, MS932 and Windows-31J can differ in how they handle some platform-dependent characters, and conversion results may vary subtly depending on the JVM or environment, so for data that includes things like circled numbers, we recommend verifying with real data.

The other is to keep UTF-8 but prepend a BOM (EF BB BF) so Excel recognizes it as UTF-8.

response.setContentType("text/csv;charset=UTF-8");
OutputStream out = response.getOutputStream();
out.write(new byte[]{(byte) 0xEF, (byte) 0xBB, (byte) 0xBF});
Writer writer = new OutputStreamWriter(out, StandardCharsets.UTF_8);

If the data may include platform-dependent characters such as circled numbers or Roman numerals, Shift_JIS can’t convert them and they’ll be garbled, so choose UTF-8 with a BOM. The rule of thumb: use Shift_JIS if the recipient is an old internal system that can’t read UTF-8. The BOM byte sequence itself is defined in the Unicode specification, so when in doubt, checking the primary source is the sure way.

Streaming large data with StreamingResponseBody

If you load hundreds of thousands of rows entirely into a List before writing, the heap overflows before the response is even assembled. With StreamingResponseBody, you can fetch a bit at a time from the DB and write to the OutputStream incrementally.

@GetMapping("/users/csv/large")
public ResponseEntity<StreamingResponseBody> downloadLarge() {
    StreamingResponseBody body = out -> {
        out.write(new byte[]{(byte) 0xEF, (byte) 0xBB, (byte) 0xBF});
        Writer w = new OutputStreamWriter(out, StandardCharsets.UTF_8);
        var beanToCsv = new StatefulBeanToCsvBuilder<UserCsv>(w)
                .withMappingStrategy(new UserCsvWriteStrategy())
                .build();

        int page = 0;
        List<UserCsv> chunk;
        try {
            do {
                chunk = userService.findForCsv(PageRequest.of(page++, 1000));
                beanToCsv.write(chunk);
                w.flush();
            } while (!chunk.isEmpty());
        } catch (CsvDataTypeMismatchException | CsvRequiredFieldEmptyException e) {
            // writeTo can only throw IOException, so wrap it
            throw new IOException("CSVの書き出しに失敗しました", e);
        }
    };

    return ResponseEntity.ok()
            .contentType(MediaType.parseMediaType("text/csv;charset=UTF-8"))
            .header(HttpHeaders.CONTENT_DISPOSITION, "attachment; filename=users.csv")
            .body(body);
}

There are three key points. Fetch with paging and flush() per chunk; the checked exceptions that write() throws (such as CsvDataTypeMismatchException) must be wrapped in IOException because writeTo only allows IOException; and StatefulBeanToCsv outputs the header row only on the first write() call and appends only data rows from the second call onward, so calling it per page won’t duplicate the header.

One thing to watch out for: if an exception occurs partway through after the header (status 200) has been sent, you’ll return a broken CSV that still says 200. So the receiving side can verify completeness, adding a mechanism—such as appending a line with the expected row count at the end, or returning the count in a trailer—prevents the accident of importing a file that was cut off midway. If you truly need heavy-duty bulk import/export, combining it with How to process large volumes of data safely with Spring Batch is the safe approach.

Importing an uploaded CSV

Next is upload. We pass the MultipartFile’s InputStream to CsvToBean and parse it. Since we want to map by name on import, we explicitly specify HeaderColumnNameMappingStrategy. This avoids the auto-selection of the position-based strategy, correctly skips the header row, and lets us import even if the column order differs somewhat. On the upload side too, don’t forget to strip the BOM and specify the character encoding.

@PostMapping("/users/csv")
@Transactional
public ResponseEntity<?> upload(@RequestParam MultipartFile file) throws Exception {
    // use BOMInputStream, which reads files with or without a BOM transparently
    InputStream bomIn = BOMInputStream.builder()
            .setInputStream(file.getInputStream())
            .get();
    Reader reader = new InputStreamReader(bomIn, StandardCharsets.UTF_8);

    var strategy = new HeaderColumnNameMappingStrategy<UserCsv>();
    strategy.setType(UserCsv.class);

    CsvToBean<UserCsv> csvToBean = new CsvToBeanBuilder<UserCsv>(reader)
            .withMappingStrategy(strategy)
            .withIgnoreLeadingWhiteSpace(true)
            .build();

    List<UserCsv> rows = csvToBean.parse();
    // proceed to validation here
}

Interposing BOMInputStream (commons-io) ensures no extra characters get mixed in at the start whether the incoming file has a BOM or not. As of commons-io 2.12, the new BOMInputStream(in) constructor is deprecated, and the BOMInputStream.builder() notation shown above is recommended. Because we’ve added @CsvBindByName, mapping fails if the header names differ from what’s expected, letting us detect format mismatches early.

The @Transactional on the method is intended to group everything through saving the imported rows to the DB into a single transaction. If you want “roll back everything if even one row errors,” the key is to keep the save processing within this method (the same transaction boundary).

Validating per row and returning error rows

Just importing is easy, but in practice you’re required to be able to return “which item on which line is invalid.” Apply Bean Validation to each row and aggregate errors with line numbers.

private final Validator validator; // jakarta.validation

List<RowError> errors = new ArrayList<>();
List<UserCsv> valid = new ArrayList<>();
int line = 2; // header is line 1, so data starts at line 2

for (UserCsv row : rows) {
    var violations = validator.validate(row);
    if (violations.isEmpty()) {
        valid.add(row);
    } else {
        for (var v : violations) {
            errors.add(new RowError(line, v.getPropertyPath().toString(), v.getMessage()));
        }
    }
    line++;
}

RowError is just a DTO holding the line number, item name, and message. From there, depending on your policy, you choose whether to stop everything and return errors if there’s even one error, or to import only the valid rows (valid) and return them along with the error list. Import processing often needs a clear “all succeed or all fail,” so designing it to throw an exception and have @Transactional roll back if there’s even one error will reduce accidents to start with.

Handling CSV-specific edge cases

Finally, the parts that tend to break in the field. When a field contains a comma, a newline, or a double quote, RFC 4180 wraps the entire field in double quotes and escapes internal quotes by doubling them. OpenCSV handles this reading and writing by default, so you basically don’t need to think about it.

When you want to handle tab-separated values (TSV) or a custom quote character, connect a parser built with CSVParserBuilder via withCSVParser.

CSVParser parser = new CSVParserBuilder()
        .withSeparator('\t')      // tab-separated
        .withQuoteChar('"')
        .build();

CsvToBean<UserCsv> csvToBean = new CsvToBeanBuilder<UserCsv>(reader)
        .withType(UserCsv.class)
        .withCSVParser(parser)    // connect the parser you built
        .build();

There’s also a way to specify withSeparator directly, but if you’re customizing the quote character and such together, passing them all at once via withCSVParser is more consistent. Rows that are empty or have a mismatched column count can cause exceptions, so it’s considerate to add a light row-count check before import, or to catch the exception and turn it into a RowError.

Conclusion

We’ve implemented CSV export and import with OpenCSV in line with practical requirements. Looking back, the flow was: for download, the key is to explicitly specify a write strategy to fix the header and column order; for garbled characters, decide up front between BOM-prefixed UTF-8 and Shift_JIS; for large data, output in chunks with StreamingResponseBody; and for import, parse with HeaderColumnNameMappingStrategy and return error rows via per-row validation.

In particular, knowing up front about the behavior where the presence of @CsvBindByPosition auto-selects the position-based strategy and makes the header disappear will help you avoid a needless pitfall.

If you want to produce reports in Excel format or PDF, see How to export Excel with Apache POI and How to generate PDFs with OpenPDF; for general-purpose file sending and receiving, refer to How to implement file upload/download with MultipartFile.