Insert Data into MS Access from Excel

Posted by

I already posted the code for Inserting data from Excel to MySQL. This is the most liked post of Java2Career,thank you all for liking my efforts. On your request I am posting this.For inserting data into MS Access first you need to create DSN.Steps for creating DSN in given this post Creating JDBC application to query SQLUsing this create a DSN named ExcelDSN. Further steps are given below:

Second Step:
For reading Excel file,we will use Apache POI. Download the POI library from following link:
http://www.apache.org/dyn/closer.cgi/poi/release/bin/poi-bin-3.8-20120326.zip
Extract zip files from this and use following library in your project:

  1. poi-ooxml-schemas-3.8-20120326
  2. poi-3.8-20120326
  3. dom4j-1.6.

Third Step:
Create a project in netbeans.Project heirechy is following:

MS Access


code  For DB_Connection.java
package DB;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
public class DB_Connection {
private Connection con;
public DB_Connection()
{
try
{
String conUrl="jdbc:odbc:ExcelDSN";
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
con=DriverManager.getConnection(conUrl);
}
catch(SQLException s)
{
System.out.println(s);
}
catch(ClassNotFoundException c)
{
System.out.println(c);
}
}
public Connection getConn() {
return con;
}
public void setConn(Connection con) {
this.con = con;
}
}


Now Code for reading excel file and inserting data of  excel file into MS Access table i.e. Student

index.jsp

<%--
Document   : index
Created on : Jun 17, 2013, 5:21:34 PM
Author     : My
--%>
<%@ page language="java" import="java.sql.*" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<%@ page import ="java.util.Date" %>
<%@ page import ="java.io.*" %>
<%@ page import ="java.io.FileNotFoundException" %>
<%@ page import ="java.io.IOException" %>
<%@ page import ="java.util.Iterator" %>
<%@ page import ="java.util.ArrayList" %>
<%@ page import ="org.apache.poi.hssf.usermodel.HSSFCell" %>
<%@ page import ="org.apache.poi.hssf.usermodel.HSSFRow" %>
<%@ page import ="org.apache.poi.hssf.usermodel.HSSFSheet" %>
<%@ page import ="org.apache.poi.hssf.usermodel.HSSFWorkbook" %>
<%@ page import ="org.apache.poi.poifs.filesystem.POIFSFileSystem" %>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
<jsp:useBean id="connection" class="DB.DB_Connection" scope="page">
<jsp:setProperty name="connection" property="*"/>
</jsp:useBean>
<%!
Connection con;
PreparedStatement ps=null;
public static ArrayList readExcelFile(String fileName)
{
/** --Define a ArrayList
--Holds ArrayList Of Cells
*/
ArrayList cellArrayLisstHolder = new ArrayList();
try{
/** Creating Input Stream**/
FileInputStream myInput = new FileInputStream(fileName);
/** Create a POIFSFileSystem object**/
POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);
/** Create a workbook using the File System**/
HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);
/** Get the first sheet from workbook**/
HSSFSheet mySheet = myWorkBook.getSheetAt(0);
/** We now need something to iterate through the cells.**/
Iterator rowIter = mySheet.rowIterator();
while(rowIter.hasNext()){
HSSFRow myRow = (HSSFRow) rowIter.next();
Iterator cellIter = myRow.cellIterator();
ArrayList cellStoreArrayList=new ArrayList();
while(cellIter.hasNext()){
HSSFCell myCell = (HSSFCell) cellIter.next();
cellStoreArrayList.add(myCell);
}
cellArrayLisstHolder.add(cellStoreArrayList);
}
}catch (Exception e){e.printStackTrace(); }
return cellArrayLisstHolder;
}%>
<%
String fileName="d:/testExcel.xls"; //testExcel.xls Excel File name
//Read an Excel File and Store in a ArrayList
ArrayList dataHolder=readExcelFile(fileName);
//Print the data read
//printCellDataToConsole(dataHolder);
con=connection.getConn();
String query="insert into Student values(?,?,?)";
ps=con.prepareStatement(query);
int count=0;
ArrayList cellStoreArrayList=null;
//For inserting into database
for (int i=0;i < dataHolder.size(); i++) {
cellStoreArrayList=(ArrayList)dataHolder.get(i);
ps.setString(1,((HSSFCell)cellStoreArrayList.get(0)).toString());
ps.setString(2,((HSSFCell)cellStoreArrayList.get(1)).toString());
ps.setString(3,((HSSFCell)cellStoreArrayList.get(2)).toString());
count= ps.executeUpdate();
System.out.print(((HSSFCell)cellStoreArrayList.get(2)).toString() + "t");
}
//For checking data is inserted or not?
if(count>0)
{ %>
Following deatils from Excel file have been inserted in student table of database
<table>
<tr>
<th>Student's Name</th>
<th>Class</th>
<th>Age</th>
</tr>
<% for (int i=0;i < dataHolder.size(); i++) {
cellStoreArrayList=(ArrayList)dataHolder.get(i);%>
<tr>
<td><%=((HSSFCell)cellStoreArrayList.get(0)).toString() %></td>
<td><%=((HSSFCell)cellStoreArrayList.get(1)).toString() %></td>
<td><%=((HSSFCell)cellStoreArrayList.get(2)).toString() %></td>
</tr>
<%}
}
else
{%>
<center> Details have not been inserted!!!!!!!!!</center>
<%    }    %>
</table>
</body>
</html>

Final Step:
Debug the project and get the output.

Excel Table View

 

2 comments

  1. <%@ page language="java" import="java.sql.*" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
    <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"&gt;
    <%@ page import ="java.util.Date" %>
    <%@ page import ="java.io.*" %>
    <%@ page import ="java.io.FileNotFoundException" %>
    <%@ page import ="java.io.IOException" %>
    <%@ page import ="java.util.Iterator" %>
    <%@ page import ="java.util.ArrayList" %>
    <%@ page import ="org.apache.poi.hssf.usermodel.HSSFCell" %>
    <%@ page import ="org.apache.poi.hssf.usermodel.HSSFRow" %>
    <%@ page import ="org.apache.poi.hssf.usermodel.HSSFSheet" %>
    <%@ page import ="org.apache.poi.hssf.usermodel.HSSFWorkbook" %>
    <%@ page import ="org.apache.poi.poifs.filesystem.POIFSFileSystem" %>
    <html>
    <head>
    <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
    <title>Insert title here</title>
    </head>
    <body>
    <jsp:useBean id="connection" class="DB.DB_Connection" scope="page">
    <jsp:setProperty name="connection" property="*"/>
    </jsp:useBean>
    <%!
    Connection con;
    PreparedStatement ps=null;
    public static ArrayList readExcelFile(String fileName)
    {
    /** –Define a ArrayList
    –Holds ArrayList Of Cells
    */
    ArrayList cellArrayLisstHolder = new ArrayList();
    try{
    /** Creating Input Stream**/
    FileInputStream myInput = new FileInputStream(fileName);
    /** Create a POIFSFileSystem object**/
    POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);
    /** Create a workbook using the File System**/
    HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);
    /** Get the first sheet from workbook**/
    HSSFSheet mySheet = myWorkBook.getSheetAt(0);
    /** We now need something to iterate through the cells.**/
    Iterator rowIter = mySheet.rowIterator();
    while(rowIter.hasNext()){
    HSSFRow myRow = (HSSFRow) rowIter.next();
    Iterator cellIter = myRow.cellIterator();
    ArrayList cellStoreArrayList=new ArrayList();
    while(cellIter.hasNext()){
    HSSFCell myCell = (HSSFCell) cellIter.next();
    cellStoreArrayList.add(myCell);
    }
    cellArrayLisstHolder.add(cellStoreArrayList);
    }
    }catch (Exception e){e.printStackTrace(); }
    return cellArrayLisstHolder;
    }%>
    <%
    String fileName="d:/testExcel.xls"; //testExcel.xls Excel File name
    //Read an Excel File and Store in a ArrayList
    ArrayList dataHolder=readExcelFile(fileName);
    //Print the data read
    //printCellDataToConsole(dataHolder);
    con=connection.getConn();
    String query="insert into Student values(?,?,?)";
    ps=con.prepareStatement(query);
    int count=0;
    ArrayList cellStoreArrayList=null;
    //For inserting into database
    for (int i=0;i < dataHolder.size(); i++) {
    cellStoreArrayList=(ArrayList)dataHolder.get(i);
    ps.setString(1,((HSSFCell)cellStoreArrayList.get(0)).toString());
    ps.setString(2,((HSSFCell)cellStoreArrayList.get(1)).toString());
    ps.setString(3,((HSSFCell)cellStoreArrayList.get(2)).toString());
    count= ps.executeUpdate();
    System.out.print(((HSSFCell)cellStoreArrayList.get(2)).toString() + "t");
    }
    //For checking data is inserted or not?
    if(count>0)
    { %>
    Following deatils from Excel file have been inserted in student table of database
    <table>
    <tr>
    <th>Student's Name</th>
    <th>Class</th>
    <th>Age</th>
    </tr>
    <% for (int i=0;i < dataHolder.size(); i++) {
    cellStoreArrayList=(ArrayList)dataHolder.get(i);%>
    <tr>
    <td><%=((HSSFCell)cellStoreArrayList.get(0)).toString() %></td>
    <td><%=((HSSFCell)cellStoreArrayList.get(1)).toString() %></td>
    <td><%=((HSSFCell)cellStoreArrayList.get(2)).toString() %></td>
    </tr>
    <%}
    }
    else
    {%>
    <center> Details have not been inserted!!!!!!!!!</center>
    <% } %>
    </table>
    </body>
    </html>

    in this code i want to upload file by user,where we have to need change or implement pls help me i have require.any body have ideas about this pls help.

Leave a Reply