Ebooks

Opencsv tutorial

In Opencsv tutorial, we show how to work with the Opencsv library which is used to read and write CSV files in Java. We provide several code examples to work with CSV in Java. The sources from this tutorial are also available at author's Github repository.

CSV (Comma Separated Values) format is a very popular import and export format used in spreadsheets and databases.

Each line in a CSV file is a data record. Each record consists of one or more fields, separated by commas. Despite CSV format being a very simple format, there can be many differecies, such as different delimiters, new lines, or quoting characters.

Opencsv library

Opencsv is a very simple CSV parser library for Java. It was developed because of the lack of commercial-friendly licenses.

<dependencies>    
    <dependency>
        <groupId>com.opencsv</groupId>
        <artifactId>opencsv</artifactId>
        <version>4.1</version>
    </dependency>
</dependencies>

This is the Maven dependency for Opencsv.

Opencsv reading data

The following example reads numbers from a CSV file.

$ tree
.
├── nbactions.xml
├── pom.xml
└── src
    ├── main
    │   ├── java
    │   │   └── com
    │   │       └── zetcode
    │   │           └── OpenCSVReadEx.java
    │   └── resources
    │       └── numbers.csv
    └── test
        └── java

This is the project structure.

numbers.csv
3,5,6,2,1,7,8
4,5,7,3,2,8,9

We have two records of data in the numbers.csv file.

OpenCSVReadEx.java
package com.zetcode;

import com.opencsv.CSVReader;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.nio.charset.StandardCharsets;

public class OpenCSVReadEx {

    public static void main(String[] args) throws IOException {

        String fileName = "src/main/resources/numbers.csv";

        try (FileInputStream fis = new FileInputStream(fileName);
                InputStreamReader isr = new InputStreamReader(fis, 
                        StandardCharsets.UTF_8);
                CSVReader reader = new CSVReader(isr)) {
            String[] nextLine;

            while ((nextLine = reader.readNext()) != null) {

                for (String e : nextLine) {
                    System.out.format("%s ", e);
                }
            }
        }
    }
}

The example reads numbers from the numbers.csv file and prints them to the console.

String fileName = "src/main/resources/numbers.csv";

The file is located in the src/main/resources directory.

try (FileInputStream fis = new FileInputStream(fileName);
        InputStreamReader isr = new InputStreamReader(fis, 
                StandardCharsets.UTF_8);
        CSVReader reader = new CSVReader(isr)) {

The CSVReader is a class used for reading CSV files.

while ((nextLine = reader.readNext()) != null) {
    
    for (String e: nextLine) {
        System.out.format("%s ", e);
    }
}

We iterate through the reader and print the value to the terminal. The readNext() method reads the next line from the buffer and converts to a string array.

3 5 6 2 1 7 8 4 5 7 3 2 8 9 

This is the output of the program.

Opencsv reading with different separator

Despite its name, CSV files can be separated with a delimiter other than a comma. The following example shows how to read numbers separated by a pipe | character.

This example is built with the Gradle tool.

$ tree
.
├── build.gradle
├── settings.gradle
└── src
    ├── main
    │   ├── java
    │   │   └── com
    │   │       └── zetcode
    │   │           └── OpenCSVReadEx2.java
    │   └── resources
    │       └── numbers.csv
    └── test
        ├── java
        └── resources

We show the project structure.

settings.gradle
rootProject.name = 'OpenCSVReadEx2'

This is Gradle settings file.

build.gradle
apply plugin: 'application'

archivesBaseName = "readnumbers2"
version = '1.0'
mainClassName = "com.zetcode.OpenCSVReadEx2"

sourceCompatibility = '1.8'
compileJava.options.encoding = 'UTF-8'

repositories {
  mavenCentral()
}

dependencies {
  compile group: 'com.opencsv', name: 'opencsv', version: '4.1'
}

This is the Gradle build file.

numbers.csv
1|2|3|4|5
6|7|3|9|8
9|1|1|0|2

We have three rows of numbers separated with the | character.

OpenCSVReadEx2.java
package com.zetcode;

import com.opencsv.CSVParser;
import com.opencsv.CSVParserBuilder;
import com.opencsv.CSVReader;
import com.opencsv.CSVReaderBuilder;
import java.io.BufferedReader;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.List;

public class OpenCSVReadEx2 {

    public static void main(String[] args) throws IOException {

        String fileName = "src/main/resources/numbers.csv";
        Path myPath = Paths.get(fileName);

        CSVParser parser = new CSVParserBuilder().withSeparator('|').build();
        
        try (BufferedReader br = Files.newBufferedReader(myPath, 
                StandardCharsets.UTF_8);
                CSVReader reader = new CSVReaderBuilder(br).withCSVParser(parser)
                        .build()) {

            List<String[]> rows = reader.readAll();

            for (String[] row : rows) {

                for (String e : row) {
                    System.out.format("%s ", e);
                }

                System.out.println();
            }
        }
    }
}

The example reads values from the numbers.csv file and prints them to the console.

CSVParser parser = new CSVParserBuilder().withSeparator('|').build();

A CSVParser with specific parser character is created.

try (BufferedReader br = Files.newBufferedReader(myPath, 
        StandardCharsets.UTF_8);
        CSVReader reader = new CSVReaderBuilder(br).withCSVParser(parser)
                .build()) {

A CSVReader is created with CSVReaderBuilder.

List<String[]> rows = reader.readAll();

We read all the elements into a list in one shot with the readAll() method. This method should not be used for large files.

$ gradle build
$ gradle run
:compileJava UP-TO-DATE
:processResources UP-TO-DATE
:classes UP-TO-DATE
:run
1 2 3 4 5 
6 7 3 9 8 
9 1 1 0 2 

We build and run the example.

Opencsv writing data

The CSVWriter class is used to write data to a CSV file.

OpenCSVWriteEx.java
package com.zetcode;

import com.opencsv.CSVWriter;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.nio.charset.StandardCharsets;

public class OpenCSVWriteEx {

    public static void main(String[] args) throws IOException {

        String[] entries = { "book", "coin", "pencil", "cup" }; 
        String fileName = "src/main/resources/items.csv";
        
        try (FileOutputStream fos = new FileOutputStream(fileName);
                OutputStreamWriter osw = new OutputStreamWriter(fos, 
                        StandardCharsets.UTF_8);
                CSVWriter writer = new CSVWriter(osw)) {
            
            writer.writeNext(entries);
        }        
    }
}

The example writes data from an array to the items.csv file. The file is written into the project root directory. The writeNext() method writes an array of elements to the file.

In the next code example, we write all data in one shot.

OpenCSVWriteEx2.java
package com.zetcode;

import com.opencsv.CSVWriter;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.nio.charset.StandardCharsets;
import java.util.ArrayList;
import java.util.List;

public class OpenCSVWriteEx2 {

    public static void main(String[] args) throws IOException {

        String[] items1 = {"book", "coin", "pencil"};
        String[] items2 = {"pen", "chair", "lamp"};
        String[] items3 = {"ball", "bowl", "spectacles"};

        List<String[]> entries = new ArrayList<>();
        entries.add(items1);
        entries.add(items2);
        entries.add(items3);

        String fileName = "src/main/resources/items.csv";

        try (FileOutputStream fos = new FileOutputStream(fileName);
                OutputStreamWriter osw = new OutputStreamWriter(fos, 
                        StandardCharsets.UTF_8);
                CSVWriter writer = new CSVWriter(osw)) {
            
            writer.writeAll(entries);
        }
    }
}

The example writes a list of arrays to the items.csv file using the writeAll() method.

SQL data to CSV file

The following example retrieves data from a database table and writes it into a CSV file. We use MySQL database. Refer to MySQL tutorial and MySQL Java tutorial for further information about MySQL and MySQL Java programming.

cars_mysql.sql
-- SQL for the Cars table

CREATE TABLE Cars(Id BIGINT PRIMARY KEY AUTO_INCREMENT, Name VARCHAR(150),
    Price INTEGER);
    
INSERT INTO Cars(Name, Price) VALUES('Audi', 52642);
INSERT INTO Cars(Name, Price) VALUES('Mercedes', 57127);
INSERT INTO Cars(Name, Price) VALUES('Skoda', 9000);
INSERT INTO Cars(Name, Price) VALUES('Volvo', 29000);
INSERT INTO Cars(Name, Price) VALUES('Bentley', 350000);
INSERT INTO Cars(Name, Price) VALUES('Citroen', 21000);
INSERT INTO Cars(Name, Price) VALUES('Hummer', 41400);
INSERT INTO Cars(Name, Price) VALUES('Volkswagen', 21600);

This is the Cars table from which we retrieve the data.

pom.xml
<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" 
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 
                http://maven.apache.org/xsd/maven-4.0.0.xsd">
    
    <modelVersion>4.0.0</modelVersion>
    <groupId>com.zetcode</groupId>
    <artifactId>OpenCSVDatabaseEx</artifactId>
    <version>1.0-SNAPSHOT</version>
    <packaging>jar</packaging>
    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
    </properties>
    
    <dependencies>    
        <dependency>
            <groupId>com.opencsv</groupId>
            <artifactId>opencsv</artifactId>
            <version>4.1</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.45</version>
        </dependency>        
    </dependencies>    
    
</project>

The Maven build file contains dependencies for Opencsv and MySQL driver.

OpenCSVDatabaseEx.java
package com.zetcode;

import com.opencsv.CSVWriter;
import java.io.FileWriter;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

public class OpenCSVDatabaseEx {

    public static void main(String[] args) {

        String url = "jdbc:mysql://localhost:3306/testdb?useSsl=false";
        String user = "testuser";
        String password = "test623";

        String fileName = "src/main/resources/cars.csv";
        Path myPath = Paths.get(fileName);

        try (Connection con = DriverManager.getConnection(url, user, password);
                PreparedStatement pst = con.prepareStatement("SELECT * FROM Cars");
                ResultSet rs = pst.executeQuery()) {

            try (CSVWriter writer = new CSVWriter(Files.newBufferedWriter(myPath,
                    StandardCharsets.UTF_8), CSVWriter.DEFAULT_SEPARATOR,
                    CSVWriter.NO_QUOTE_CHARACTER, CSVWriter.NO_ESCAPE_CHARACTER,
                    CSVWriter.DEFAULT_LINE_END)) {

                writer.writeAll(rs, true);
            }

        } catch (SQLException | IOException ex) {
            Logger.getLogger(OpenCSVDatabaseEx.class.getName()).log(
                    Level.SEVERE, ex.getMessage(), ex);
        }
    }
}

In the example, we connect to the MySQL database and retrieve all rows from the Cars table. The data is written to the cars.csv file.

try (Connection con = DriverManager.getConnection(url, user, password);
        PreparedStatement pst = con.prepareStatement("SELECT * FROM Cars");
        ResultSet rs = pst.executeQuery()) {

We connect to the database table with the driver manager and execute the SELECT * FROM Cars statement.

try (CSVWriter writer = new CSVWriter(Files.newBufferedWriter(myPath,
        StandardCharsets.UTF_8), CSVWriter.DEFAULT_SEPARATOR,
        CSVWriter.NO_QUOTE_CHARACTER, CSVWriter.NO_ESCAPE_CHARACTER,
        CSVWriter.DEFAULT_LINE_END)) {

We create a CSVWriter with a default separator, no quoting characters, no escape character, and a default line end.

writer.writeAll(rs, true);

The writeAll() method takes the java.sql.ResultSet as a parameter. The second parameter specifies whether the field headers should be included.

$ cat cars.csv 
ID,NAME,PRICE
1,Audi,52642
2,Mercedes,57127
3,Skoda,9000
4,Volvo,29000
5,Bentley,350000
6,Citroen,21000
7,Hummer,41400
8,Volkswagen,21600
9,Toyota,26700

The code example produces this file.

Opencsv mapping to JavaBeans

CsvToBean is used to map CSV data to JavaBeans.

Mapping by column names

With HeaderColumnNameMappingStrategy, we can map CSV data to Java objects using the column names in the first row of the CSV file

$ tree
.
├── nbactions.xml
├── pom.xml
└── src
    ├── main
    │   ├── java
    │   │   └── com
    │   │       └── zetcode
    │   │           ├── bean
    │   │           │   └── Car.java
    │   │           └── OpenCSVReadBeansEx.java
    │   └── resources
    │       └── cars.csv
    └── test
        └── java

This is the project structure.

cars.csv
ID,NAME,PRICE
1,Audi,52642
2,Mercedes,57127
3,Skoda,9000
4,Volvo,29000
5,Bentley,350000
6,Citroen,21000
7,Hummer,41400
8,Volkswagen,21600
9,Toyota,26700

This is the cars.csv file. The first record contains the column names.

Car.java
package com.zetcode.bean;

import com.opencsv.bean.CsvBindByName;

public class Car {
    
    @CsvBindByName
    private int id;
    
    @CsvBindByName
    private String name;
    
    @CsvBindByName
    private int price;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getPrice() {
        return price;
    }

    public void setPrice(int price) {
        this.price = price;
    }

    @Override
    public String toString() {

        StringBuilder builder = new StringBuilder();
        builder.append("Car{id=").append(id).append(", name=")
                .append(name).append(", price=").append(price).append("}");

        return builder.toString();
    }
}

The Car is a JavaBean. It contains the @CsvBindByName annotations to map the bean attributes to the CSV columns.

OpenCSVReadBeansEx.java
package com.zetcode;

import com.opencsv.bean.CsvToBean;
import com.opencsv.bean.CsvToBeanBuilder;
import com.opencsv.bean.HeaderColumnNameMappingStrategy;
import com.zetcode.bean.Car;
import java.io.BufferedReader;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.List;

public class OpenCSVReadBeansEx {

    public static void main(String[] args) throws IOException {

        String fileName = "src/main/resources/cars.csv";
        Path myPath = Paths.get(fileName);

        try (BufferedReader br = Files.newBufferedReader(myPath,
                StandardCharsets.UTF_8)) {

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

            CsvToBean csvToBean = new CsvToBeanBuilder(br)
                    .withType(Car.class)
                    .withMappingStrategy(strategy)
                    .withIgnoreLeadingWhiteSpace(true)
                    .build();

            List<Car> cars = csvToBean.parse();
            
            cars.forEach(System.out::println);
        }
    }
}

The example reads the data from the cars.csv file and maps them to the Car objects. It uses HeaderColumnNameMappingStrategy.

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

The HeaderColumnNameMappingStrategy maps data to objects using the column names in the first row of the CSV file. The column order does not matter.

CsvToBean csvToBean = new CsvToBeanBuilder(br)
        .withType(Car.class)
        .withMappingStrategy(strategy)
        .withIgnoreLeadingWhiteSpace(true)
        .build();

A CsvToBean is created with CsvToBeanBuilder. We specify the type and the mapping strategy.

List<Car> cars = csvToBean.parse();

With the CsvToBean's parse() method, we parse the CSV data into the list.

cars.forEach(System.out::println);

We go over the list of beans and print them to the console.

Car{id=1, name=Audi, price=52642}
Car{id=2, name=Mercedes, price=57127}
Car{id=3, name=Skoda, price=9000}
Car{id=4, name=Volvo, price=29000}
Car{id=5, name=Bentley, price=350000}
Car{id=6, name=Citroen, price=21000}
Car{id=7, name=Hummer, price=41400}
Car{id=8, name=Volkswagen, price=21600}
Car{id=9, name=Toyota, price=26700}

This is the output of the example.

Mapping by column position

The ColumnPositionMappingStrategy maps columns by their position.

cars.csv
1,Audi,52642
2,Mercedes,57127
3,Skoda,9000
4,Volvo,29000
5,Bentley,350000
6,Citroen,21000
7,Hummer,41400
8,Volkswagen,21600
9,Toyota,26700

This is the cars.csv file.

Car.java
package com.zetcode.bean;

import com.opencsv.bean.CsvBindByPosition;

public class Car {

    @CsvBindByPosition(position = 0)
    private int id;

    @CsvBindByPosition(position = 1)
    private String name;

    @CsvBindByPosition(position = 2)
    private int price;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getPrice() {
        return price;
    }

    public void setPrice(int price) {
        this.price = price;
    }

    @Override
    public String toString() {

        StringBuilder builder = new StringBuilder();
        builder.append("Car{id=").append(id).append(", name=")
                .append(name).append(", price=").append(price).append("}");

        return builder.toString();
    }
}

The @CsvBindByPosition specifies a binding between a column number of the CSV input and a field in a bean.

OpenCSVReadBeansEx2.java
package com.zetcode;

import com.opencsv.bean.ColumnPositionMappingStrategy;
import com.opencsv.bean.CsvToBean;
import com.opencsv.bean.CsvToBeanBuilder;
import com.zetcode.bean.Car;
import java.io.BufferedReader;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.List;

public class OpenCSVReadBeansEx2 {

    public static void main(String[] args) throws IOException {

        String fileName = "src/main/resources/cars.csv";
        Path myPath = Paths.get(fileName);

        try (BufferedReader br = Files.newBufferedReader(myPath,
                StandardCharsets.UTF_8)) {

            ColumnPositionMappingStrategy strategy = new ColumnPositionMappingStrategy();
            strategy.setType(Car.class);
            String[] fields = {"id", "name", "price"};
            strategy.setColumnMapping(fields);

            CsvToBean csvToBean = new CsvToBeanBuilder(br)
                    .withType(Car.class)
                    .withMappingStrategy(strategy)
                    .withIgnoreLeadingWhiteSpace(true)
                    .build();

            List<Car> cars = csvToBean.parse();

            cars.forEach(System.out::println);
        }
    }
}

The example reads the data from the cars.csv file and maps them to the Car objects. It uses ColumnPositionMappingStrategy.

ColumnPositionMappingStrategy strategy = new ColumnPositionMappingStrategy();
strategy.setType(Car.class);
String[] fields = {"id", "name", "price"};
strategy.setColumnMapping(fields);

We create a ColumnPositionMappingStrategy. With setColumnMapping() we set the column names to be mapped.

Opencsv writing JavaBeans with StatefulBeanToCsv

In the next example, we write JavaBeans to CSV with StatefulBeanToCsv.

Car.java
package com.zetcode.bean;

public class Car {

    private int id;
    private String name;
    private int price;

    public Car() {
    }

    public Car(int id, String name, int price) {
        this.id = id;
        this.name = name;
        this.price = price;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public int getPrice() {
        return price;
    }

    public void setPrice(int price) {
        this.price = price;
    }
    
    @Override
    public String toString() {

        StringBuilder builder = new StringBuilder();
        builder.append("Car{id=").append(id).append(", name=")
                .append(name).append(", price=").append(price).append("}");

        return builder.toString();
    }    
}

This is a Car bean.

OpenCSVWriteBeansEx.java
package com.zetcode;

import com.opencsv.CSVWriter;
import com.opencsv.bean.StatefulBeanToCsv;
import com.opencsv.bean.StatefulBeanToCsvBuilder;
import com.opencsv.exceptions.CsvDataTypeMismatchException;
import com.opencsv.exceptions.CsvRequiredFieldEmptyException;
import com.zetcode.bean.Car;
import java.io.BufferedWriter;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.Paths;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

public class OpenCSVWriteBeansEx {

    public static void main(String[] args) {

        String fileName = "src/main/resources/cars.csv";
        Path myPath = Paths.get(fileName);

        List<Car> cars = new ArrayList<>();
        cars.add(new Car(1, "Audi", 52642));
        cars.add(new Car(2, "Mercedes", 57127));
        cars.add(new Car(3, "Skoda", 9000));
        cars.add(new Car(4, "Volvo", 29000));

        try (BufferedWriter writer = Files.newBufferedWriter(myPath,
                StandardCharsets.UTF_8)) {

            StatefulBeanToCsv<Car> beanToCsv = new StatefulBeanToCsvBuilder(writer)
                    .withQuotechar(CSVWriter.NO_QUOTE_CHARACTER)
                    .build();
                    
            beanToCsv.write(cars);

        } catch (CsvDataTypeMismatchException | CsvRequiredFieldEmptyException |
                IOException ex) {
            Logger.getLogger(OpenCSVWriteBeansEx.class.getName()).log(
                    Level.SEVERE, ex.getMessage(), ex);
        }
    }
}

The example creates a list of car objects and writes them to a CSV file.

List<Car> cars = new ArrayList<>();
cars.add(new Car(1, "Audi", 52642));
cars.add(new Car(2, "Mercedes", 57127));
cars.add(new Car(3, "Skoda", 9000));
cars.add(new Car(4, "Volvo", 29000));

We create a list of car objects.

StatefulBeanToCsv<Car> beanToCsv = new StatefulBeanToCsvBuilder(writer)
        .withQuotechar(CSVWriter.NO_QUOTE_CHARACTER)
        .build();

A StatefulBeanToCsv is created with StatefulBeanToCsvBuilder.

beanToCsv.write(cars);

The beans are written to a file.

In this tutorial, we have worked with the Opencsv library. We have read data from a CSV file, written data to a CSV file, exported data from a database table to a CSV file, and mapped CSV data to beans.

You might also be interested in the following related tutorials: Java tutorial, Reading CSV file inside WAR, Reading text files in Java, and jQuery Autocomplete tutorial.