Coding Tips (JavaScript/CSS/VBA/Win32)
Useful code snippets, tips and some Windows applications
Transfer data from Excel to Access through Java
This is sample code on how to use Java to transfer data from Excel spreadsheets to Access.
The basic principle is to create an ODBC data source both for Excel and Access. Then use the JDBC API to retrieve records
from the Excel ODBC source and insert them to Access.
The Excel ODBC source is represented by "jdbc:odbc:BulkMail", and the Access ODBC source is
represented by "jdbc:odbc:Whisky" in the code.
import java.sql.*;
public class ExcelToAccess {
public static void main (String[] args)
{
try
{
Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");
String sourceURL = "jdbc:odbc:BulkMail";
String sourceAccess = "jdbc:odbc:Whisky";
Connection dbConnection = DriverManager.getConnection(sourceURL);
Connection dbAccess = DriverManager.getConnection(sourceAccess);
String comp;
String fax;
Statement st = dbConnection.createStatement();
Statement stAccess = dbAccess.createStatement();
ResultSet rs = st.executeQuery("Select LastName, FirstName, Company, FaxNumber from Customers");
while (rs.next()){
comp = rs.getString ("Company");
fax = rs.getString ("FaxNumber");
stAccess.executeUpdate("INSERT INTO Company (CompanyName, FaxNumber) VALUES('" +
comp + "','" + fax + "')");
}
rs.close();
dbConnection.close();
dbConnection =null;
dbAccess.close();
dbAccess = null;
}
catch(SQLException sqle)
{ System.err.println(sqle); }
catch(ClassNotFoundException cnfe)
{ System.err.println(cnfe); }
}
}
public class ExcelToAccess {
public static void main (String[] args)
{
try
{
Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");
String sourceURL = "jdbc:odbc:BulkMail";
String sourceAccess = "jdbc:odbc:Whisky";
Connection dbConnection = DriverManager.getConnection(sourceURL);
Connection dbAccess = DriverManager.getConnection(sourceAccess);
String comp;
String fax;
Statement st = dbConnection.createStatement();
Statement stAccess = dbAccess.createStatement();
ResultSet rs = st.executeQuery("Select LastName, FirstName, Company, FaxNumber from Customers");
while (rs.next()){
}
rs.close();
dbConnection.close();
dbConnection =null;
dbAccess.close();
dbAccess = null;
}
catch(SQLException sqle)
{ System.err.println(sqle); }
catch(ClassNotFoundException cnfe)
{ System.err.println(cnfe); }
}
}