How to use CSVREAD import csv file into H2 database Clojure example

Suppose you have a CSV file contains the following content

 
1,hello,13
2,world,14
3,today,26

And you want to load it into H2 database. This post shows how to do it in Clojure code

Step 1. Create memory database in H2

H2 database support in memory database, if you want to do rapid prototyping or testing, it's an ideal option.

 
(require '[clojure.java.jdbc :as j])
 
(def demo-settings
   {
    :classname   "org.h2.Driver"
    :subprotocol "h2:mem"
    :subname     "demo;DB_CLOSE_DELAY=-1"
    :user        "sa"
    :password    ""
   }
)
 

Step 2. Create table

We have three columns, let's create a table with name csvdata.

 
(j/execute! demo-settings ["drop table csvdata"])
(j/execute! demo-settings ["create table csvdata (id int primary key, name varchar(100), age int)"])
 

Step 3. Import data from CSV file

According to the manual, the SQL statement to import data from CSV file into H2 database table as follows

 
INSERT INTO my_table( id, name, age )  
SELECT CONVERT( "id",INT ), "name", CONVERT( "age", INT)
  FROM CSVREAD( 'c:\\tmp\\sample.csv', 'id,name,age', NULL );
 

In Clojure it will be

 
(j/execute! demo-settings ["insert into csvdata ( id, name, age )     select convert( \"id\",int ), \"name\", convert( \"age\", int)   from CSVREAD( 'c:\\tmp\\sample.csv', 'id,name,age', null ) "])
 
(j/query demo-settings ["select * from csvdata"])