Follow by Email

Friday, August 14, 2015

Excel data to Sqlite3 Database table using Java

Below is the code for inserting data from excel sheet to sqlite3 database using Java code.

1.Open new eclipse Java Project and reference to the latest Java jdbc-sqlite jar file from    below url
   https://bitbucket.org/xerial/sqlite-jdbc/downloads

2. Also reference to the Apache POI - the Java API for Microsoft Documents, which we require for reading through the excel file
    http://www.apache.org/dyn/closer.cgi/poi/release/bin/poi-bin-3.12-20150511.tar.gz

3. Below is the code for the same

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;

import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Row;
import org.sqlite.core.DB;

public class run {
public static void main(String args[]) {
try {

            Class.forName("org.sqlite.JDBC");
Connection con = DriverManager.getConnection("jdbc:sqlite:test.db"); 
PreparedStatement pstm = null; Statement stmt = null; 
con.setAutoCommit(false);

pstm = (PreparedStatement) con.prepareStatement("DROP TABLE IF 
EXISTS table_name"); pstm.execute();

stmt = con.createStatement(); 
String sql = "CREATE TABLE table_name " + "(ID INTEGER PRIMARY KEY 
AUTOINCREMENT," + " NAME         TEXT)"; 
stmt.executeUpdate(sql);
stmt.close();


FileInputStream input = new FileInputStream("C:\\Emp.xls");
POIFSFileSystem fs = new POIFSFileSystem(input);
HSSFWorkbook wb = new HSSFWorkbook(fs);
HSSFSheet sheet = wb.getSheetAt(0);
Row row;
for (int i = 1; i <= sheet.getLastRowNum(); i++) {
row = sheet.getRow(i);
String col2 = row.getCell(1).getStringCellValue();
String sqlcommand = "INSERT INTO table_name(NAME) Values('" + col2 + "')";
pstm = (PreparedStatement) con.prepareStatement(sqlcommand);
pstm.execute();
System.out.println("Import rows " + i);
}
con.commit();
pstm.close();

con.close();
input.close();
System.out.println("Success import excel to sqlite Database");
} catch (ClassNotFoundException e) {
System.out.println(e);
} catch (SQLException ex) {
System.out.println(ex);
} catch (IOException ioe) {
System.out.println(ioe);
}
}
}