Opencsv tutorial

In this 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.

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.

Opencsv reading data

The following example reads numbers from a CSV file.

NetBeans project structure
Figure: NetBeans project structure

The figure shows the project structure in NetBeans.

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>ReadOpenCSV</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>3.8</version>
        </dependency>
    </dependencies>
    
</project>

In the Maven pom.xml file, we specify the dependency for the Opencsv library.

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.

ReadNumbersEx.java
package com.zetcode.readopencsv;

import com.opencsv.CSVReader;
import java.io.FileReader;
import java.io.IOException;

public class ReadNumbersEx {

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

        String fileName = "src/main/resources/numbers.csv";
        
        try (CSVReader reader = new CSVReader(new FileReader(fileName))) {
            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 (CSVReader reader = new CSVReader(new FileReader(fileName))) {

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.

Reading with a 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
└── src
    └── main
        ├── java
        │   └── com
        │       └── zetcode
        │           └── readopencsv
        │               └── ReadNumbersEx2.java
        └── resources
            └── numbers.csv

7 directories, 3 files

We show the project structure.

build.gradle
apply plugin: 'java'
apply plugin: 'application'

archivesBaseName = "readnumbers2"
version = '1.0'
mainClassName = "com.zetcode.readopencsv.ReadNumbersEx2"

repositories {
  mavenCentral()
}

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

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.

ReadNumbers2.java
package com.zetcode.readopencsv;

import com.opencsv.CSVReader;
import java.io.FileReader;
import java.io.IOException;
import java.util.List;

public class ReadNumbersEx2 {

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

        String fileName = "src/main/resources/numbers.csv";
        
        try (CSVReader reader = new CSVReader(new FileReader(fileName), '|')) {
            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.

CSVReader reader = new CSVReader(new FileReader(fileName), '|');

The second parameter of the CSVReader specifies the separator character used in the CSV file.

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

We read all the elements into a list in one shot with the readAll() method.

$ 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.

WriteOpenCSV.java
package com.zetcode.writeopencsv;

import com.opencsv.CSVWriter;
import java.io.FileWriter;
import java.io.IOException;

public class WriteOpenCSV {

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

        String[] entries = { "book", "coin", "pencil", "cup" }; 
        String fileName = "items.csv";
        
        try (CSVWriter writer = new CSVWriter(new FileWriter(fileName))) {
            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.

WriteOpenCSV2.java
package com.zetcode.writeopencsv2;

import com.opencsv.CSVWriter;
import java.io.FileWriter;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class WriteOpenCSV2 {

    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 = "items.csv";

        try (CSVWriter writer = new CSVWriter(new FileWriter(fileName))) {
            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.sql
CREATE TABLE IF NOT EXISTS Cars(Id INTEGER PRIMARY KEY, Name VARCHAR(50), 
        Price INTEGER);
INSERT INTO Cars VALUES(1,'Audi',52642);
INSERT INTO Cars VALUES(2,'Mercedes',57127);
INSERT INTO Cars VALUES(3,'Skoda',9000);
INSERT INTO Cars VALUES(4,'Volvo',29000);
INSERT INTO Cars VALUES(5,'Bentley',350000);
INSERT INTO Cars VALUES(6,'Citroen',21000);
INSERT INTO Cars VALUES(7,'Hummer',41400);
INSERT INTO Cars VALUES(8,'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>DatabaseEx</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>3.8</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.39</version>
        </dependency>        
    </dependencies>    
    
</project>

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

DatabaseEx.java
package com.zetcode.databaseex;

import com.opencsv.CSVWriter;
import static com.opencsv.CSVWriter.DEFAULT_SEPARATOR;
import static com.opencsv.CSVWriter.NO_QUOTE_CHARACTER;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class DatabaseEx {

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

        Connection con = null;
        PreparedStatement pst = null;
        ResultSet rs = null;

        CSVWriter writer = null;

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

        try {

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

            writer = new CSVWriter(new FileWriter("cars.csv"), 
                    DEFAULT_SEPARATOR, NO_QUOTE_CHARACTER);
            writer.writeAll(rs, true);

        } finally {

            if (writer != null) {
                writer.close();
            }

            if (rs != null) {
                rs.close();
            }

            if (pst != null) {
                pst.close();
            }

            if (con != null) {
                con.close();
            }
        }
    }
}

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.

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

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

writer = new CSVWriter(new FileWriter("cars.csv"), 
        DEFAULT_SEPARATOR, NO_QUOTE_CHARACTER);

We create a CSVWriter with a default separator and no quoting characters.

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.

Mapping to JavaBeans

Opencsv has annotations to map data to JavaBeans.

NetBeans project structure of the BeansEx example
Figure: NetBeans project structure of the BeansEx example

The figure shows how the project structure looks in NetBeans.

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.

CarBean.java
package com.zetcode.beansex;

import com.opencsv.bean.CsvBindByName;

public class CarBean {
    
    @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() {
        return "CarBean{" + "Id=" + Id + ", Name=" + 
                Name + ", Price=" + Price + '}';
    }    
}

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

BeansEx.java
package com.zetcode.beansex;

import com.opencsv.CSVReader;
import com.opencsv.bean.CsvToBean;
import com.opencsv.bean.HeaderColumnNameMappingStrategy;
import java.io.FileReader;
import java.io.IOException;
import java.util.List;

public class BeansEx {

    public static void main(String[] args) throws IOException {
        
        String fileName = "src/main/resources/cars.csv";
        
        try (CSVReader reader = new CSVReader(new FileReader(fileName))) {
        
            HeaderColumnNameMappingStrategy<CarBean> strategy =
                    new HeaderColumnNameMappingStrategy<>();
            strategy.setType(CarBean.class);
            
            CsvToBean<CarBean> csvToBean = new CsvToBean<>();
            List<CarBean> beanList = csvToBean.parse(strategy, reader);
            
            for (CarBean bean: beanList) {
                System.out.println(bean);
            }
        }
    }
}

The example reads the data from the cars.csv file and maps them to the CarBean objects.

HeaderColumnNameMappingStrategy<CarBean> strategy = 
        new HeaderColumnNameMappingStrategy<>();
strategy.setType(CarBean.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<CarBean> csvToBean = new CsvToBean<>();
List<CarBean> beanList = csvToBean.parse(strategy, reader);

The CsvToBean converts CSV data to objects. The parse() method parses the values from the CSVReader.

for (CarBean bean: beanList) {
    System.out.println(bean);
}

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

CarBean{Id=1, Name=Audi, Price=52642}
CarBean{Id=2, Name=Mercedes, Price=57127}
CarBean{Id=3, Name=Skoda, Price=9000}
CarBean{Id=4, Name=Volvo, Price=29000}
CarBean{Id=5, Name=Bentley, Price=350000}
CarBean{Id=6, Name=Citroen, Price=21000}
CarBean{Id=7, Name=Hummer, Price=41400}
CarBean{Id=8, Name=Volkswagen, Price=21600}
CarBean{Id=9, Name=Toyota, Price=26700}

This is the output of the example.

In this tutorial, we have worked with the Opencsv library. We have read data from a CSV file, written data to a CSV file, export 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.