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 {
    mavenLocal()
    mavenCentral()
}
 
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;
 
        Class.forName("org.h2.Driver");
        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 (rs.next()) {
            System.out.println("id " + rs.getInt("id") + " name " + rs.getString("name") + " age " + rs.getInt("age") );
        }
        stmt.close();
    }
}
 
 

The console: