Now that we have experience in Eclipse with our Java main class and using an imported library, it is time to show you some tricks on how to capture data. In this exercise we will capture data from an Excel spreadsheet and store the data in a real free open source database. We use the JXL library for the excel part and the SQLite library for the data part. We will also be doing SQLite in Python later on for comparison sake.

 

First some ground rules about programming Excel. We iterate over columns and rows using a For loop and excel itself is just an array of objects. So in java we will use this loop structure to iterate over an array of data objects starting at coordinates (0,0). These principles apply to a spreadsheet that we are treating as a database, a bunch of data stored in columns and rows as opposed to an excel sheet with no special format, a random sheet of unorganized data. Some Excel users prefer small spreadsheets as their data source and other Excel users are really into VBA. We are not attempting to replace VBA here. We are only wanting to take you to the next level and instead of using Excel spreadsheets as data stores why not get more sophisticated and store data into a real database. Learning this technique in Java can greatly expand your knowledge base and also enhance your career. Twenty years ago I did this and it made my career in Data, I never turned back.

 

Introducing SQLlite, database is embedded in the java application, so there is no need to download others like MySQL or SQL Server. Reason to do sqlite in eclipse is, it is a great entry point into learning sql literally within minutes versus struggling with a much longer learning curve using other means. We also get to learn both SQL and Java at the same time. How cool is that?

 

Short history of Excel: Long ago before spreadsheets were even around we had a math course called matrices and vectors. Yes i am old enough to remember this. A spreadsheet representation of cells was called a matrix. And then one day someone created the actual spreadsheet we use today, I think it was lotus 123, actually it was Visicalc in 1979. Then Visi morphed into lotus 123, then by 1990 Microsoft went to town with the concept and Excel became the de facto spreadsheet in use today. But look at the skeleton of a spreadsheet. It is only columns and rows. So with a convenient for loop we can capture the data and then program how we want. Enter in a programming language like java to do this. So we can conveniently learn both sql and java just by concentrating on the columns and rows concept of a spreadsheet and then iterating over the data with a for loop in java or python if u wish

 

Before jumping in we must have JDK and Eclipse installed. See my tutorial #1 for this. Then we need to download and install both JXL and SQLite libraries (jar files) into our java build path. See tutorials 3 & 4 for this. The Excel file we are reading must be in .xls format. There are no screenshots in this tutorial. Refer back to tutorial #4 to get started with SQLite. And actually it would be good to review all the tutorials before doing this one.

 

Okay time to jump in. Go to the following links to get JXL and SQLite jars. Download and extract zip contents. Then add both jars to java build path. Go to tutorial #3 to see how.

 

sqlite jar   http://www.java2s.com/Code/Jar/s/Downloadsqlitejdbc372jar.htm

 

And BE CAREFUL not to press the green ‘start now’ button but press the actual jar.zip link and the zip file will appear in your downloads area.

 

Jxl jar http://www.java2s.com/Code/Jar/j/Downloadjxl10jar.htm

 

Now create a java project and main class like we learned in tutorial #2. Reference the 2 jar files in java build path. Name your class and a template class structure appears in workspace. Delete and replace class contents with the following code:            (don’t forget to file save after replacing)

​

 

/**

*this application pulls data from an excel spreadsheet

*and inserts into sqlite db

*be sure to add jxl and sqlite jar file libraries to java buildpath

*also format for excel sheet MUST be .xls (if xlsx then save file as xls first)

*

*/

 

//package test;      for this example we left package blank when creating class inside project

 

import java.sql.*;

import java.io.*;  //used for File object...needs try and catch block

import jxl.*;             //enable use of all objects from JXL library

 

 

    // be sure the class name u choose is here. I used Create_sqlite_table as class my name

 

public class Create_sqlite_table {

 

public static Connection cn = null;

 

public static void main(String[] args) {

   // put class variables inside of main method so as not to need static syntax like above

Workbook wrk1;

Sheet sheet=null;

 int rows=0;

 int cols=0;

 

loadDriver();    //go to load driver method below and start sqlite

                //then resume program with below code

 

try {

      //use whatever path to your local excel file in xls format

   // for simplicity sake, keep the sheet contents short for now with no empty cells

 

wrk1 =  Workbook.getWorkbook(new File("C:/users/rickd/desktop/files/book1.xls"));

                 // store excel file in your own path and fix above line accordingly to match your path

 

sheet = wrk1.getSheet(0);  //gets the default sheet1 in a workbook

       

       rows=sheet.getRows();  //get number of rows on sheet

       cols=sheet.getColumns();  //get number of columns on sheet

       

       System.out.println("number of rows= "+rows);

       System.out.println("number of columns= "+cols);

       

       

                 //see tutorial 4 on how to create table in SQLite

                 // sqlite table columns must match excel columns

                      //my excel sheet has 4 cols and dbase has 4 cols

 

PreparedStatement stmt=cn.prepareStatement("insert into test_table (column1,column2,column3,column4) values(?,?,?,?)");

 

               //use double for loop

                                                    //outer loop, for each row up to total number of rows

 

                   for (int i=0; i<rows; i++) {   

                            

       

                        //for each col in the current row up to tot number of cols

​

            for(int j = 0; j<cols; j++){

                      //j++ means on next pass j=1 then j=2 etc

            Cell row_a = sheet.getCell(j,i);  //while i= row 0 get cols 1 thru 4

                String c = row_a.getContents();

                  // System.out.println(c);      //if u want to see contents in java console      

 

                stmt.setString(j+1, c); //setString always begins with 1, j starts with 0 so add 1 or just start j with 1 instead

                     //for each column set a string 1-4

 

            }  //end inner for loop

           

            stmt.executeUpdate(); //execute each row after getting all 4 col vals

           

            //then loop thru row 2 and get col values and insert row contents

           

            

       

  } //end outer for loop

           

 

} //end of try block

 

 

catch (Exception e) {

         //can just use generic exception to catch all error types

    System.out.println(" Exceptions : " + e.toString());

 

       } //end catch

 

​

​

}   //end of main method

​

                                    //put other class methods here

​

public static void loadDriver() {

try {

 Class.forName("org.sqlite.JDBC");

 System.out.println("The Driver has been loaded successfully!");

}

catch (Exception E1) {

         System.out.println("Unable to load the Driver!");

 System.out.println("Exceptions:" + E1.toString());

 System.exit(1);

}

 

System.out.println("Establishing connection to Server");

 

 

//Establishing the connection with the database

 

try {

cn = DriverManager.getConnection("jdbc:sqlite:test.db"); //this path creates test.db in workspace2

System.out.println("Connection to Server was Established");

       }

   catch (Exception E2) {

System.out.println(" Exceptions : " + E2.toString());

       }

​

​

} //end load driver method

​

}    // end of class

​
 

​

Read all the comments  in the code for certain instructions

​

Also, it is really important to understand the loop structure in Java