Insert CSV into SQLite DB in Java

Question

Hey there. Thus, I have a collection of csv files that I would like to insert into the sqlite database in my Java program as tables. I googled around and also searched for SO, but I cannot find any tutorial on how to insert these CSV files into sqlite database. I use this sqlite: Zentus SQLiteJDBC shell .

So let's say I have a csv file with the following inside it:

1, John, Doe, 5.0
2, Jane, Smith, 5.0

How do I create a table and paste these values ​​into it?

Can someone point me in the right direction? Thank!

Update: OK, so I found this guide: http://www.sqlite.org/cvstrac/wiki?p=ImportingFiles , but the syntax is a little confusing to me. In particular:

sqlite> create table test (id integer, datatype_id integer, level integer, meaning text);    
sqlite> .separator ","    
sqlite> .import no_yes.csv test

I understand what this does, it says the delimiter will be a comma and imports the no_yes.csv file into the test table. However, this is how I do sql statements according to the JDBC manual:

Statement stat = conn.createStatement();
stat.executeUpdate("drop table if exists test;");
stat.executeUpdate("create table test (id, name, value);");

I tried to do this to represent a separator string:

stat.executeUpdate("separator ','");

and

stat.executeUpdate(".separator ','");

But both give me an error. How should I do it? Thank!

Answer 1

First you need to create a table. Then you can read the csv files with something like this:

BufferedReader br = new BufferedReader(new FileReader("your file"));
String line;
while ( (line=br.readLine()) != null)
{
         String[] values = line.split(",");    //your seperator

         //Convert String to right type. Integer, double, date etc.
         stat.executeUpdate("INSERT INTO yourTable VALUES('"+value[0]+"','"+value[1]...
         //Use a PeparedStatemant, it´s easier and safer 
}
br.close();