How to read CSV file into H2 database

H2 database is a pure Java SQL database and it has a nice feature CSVREAD which allows you import data from CSV file. Then you can do anything you want to the data in the SQL database.

In this post will create a Java project and load data from CSV file into H2 database.

Step 1. Create Gradle project

Select File - New - Gradle to create a new Gradle project.

Step 2. Add H2 database dependency

H2 database is a lightweight SQL database. There is only one Jar package we need, only it's only 1.18MB.

apply plugin: 'java'
apply plugin: 'eclipse'
sourceCompatibility = 1.5
version = '1.0'
jar {
    manifest {
        attributes 'Implementation-Title': 'Gradle Quickstart', 'Implementation-Version': version
repositories {
dependencies {
    compile group: 'commons-collections', name: 'commons-collections', version: '3.2'
    testCompile group: 'junit', name: 'junit', version: '4.+'
    compile group: 'com.h2database', name: 'h2', version: '1.3.148'
test {
    systemProperties 'property': 'value'
uploadArchives {
    repositories {
       flatDir {
           dirs 'repos'

Step 3. Connect to H2 database

Suppose we have a CSV file at c:\tmp\sample.csv, here is the code to load it into H2 database. We will connect to database and create a table then import.

package org.gradle;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class H2CSVExample {
    public static void main (String [] args) throws Exception {
        Connection conn = null;
        Statement stmt = null;
        conn = DriverManager.getConnection("jdbc:h2:~/test", "", "");
        stmt = conn.createStatement();
        stmt.execute("drop table if exists csvdata");
        stmt.execute("create table csvdata (id int primary key, name varchar(100), age int)");
        stmt.execute("insert into csvdata ( id, name, age )     select convert( \"id\",int ), \"name\", convert( \"age\", int)   from CSVREAD( 'c:\\tmp\\sample.csv', 'id,name,age', null ) ");
        ResultSet rs = stmt.executeQuery("select * from csvdata");
        while ( {
            System.out.println("id " + rs.getInt("id") + " name " + rs.getString("name") + " age " + rs.getInt("age") );

The console: