Inserting .csvs into an SQLite DB in Java

Question

Hey all. So I have a collection of csv files which I would like to insert into an sqlite DB in my Java program as tables. I googled around and searched SO as well, but I can't find any tutorial on how to insert these csv files into the sqlite DB. I am using this sqlite wrapper: Zentus SQLiteJDBC.

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

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

How could I create a table and insert these values into it?

Can anyone point me in the right direction? Thank you!

Update: OK so I found this guide: http://www.sqlite.org/cvstrac/wiki?p=ImportingFiles but the syntax is confusing me a bit. Specifically:

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 is doing, it says the separator will be a comma, and to import the file no_yes.csv into the table test. However, this is how I have been doing sql statements as per the JDBC guide:

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

I tried doing this to represent the separator line:

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

AND

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

But both give me an error. How would I go about doing this? Thanks!

Answer 1

You need to create the table first. Then you can read the csv files with something like that:

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();