Upload excel data to access database

**ravikumar.achi** · 11-12-2007, 01:52 PM

Hello

I need small help.

i need to upload the excel data to access through JAVA code.
please give me the pseudo code.
thanks
ravikumar

**mervinshe** · 01-08-2012, 02:51 PM

Hey did u get any answer for this prob....??? Rite now i need the answer for this... :) If u have found d solution means pls help m out.. :)

**quad64bit** · 01-08-2012, 11:19 PM

Connect to an access database:
Connect to Access Database using JDBC-ODBC bridge : JDBC ODBC*«*Database*«*Java Tutorial

Read an excel file with java:
Apache POI - the Java API for Microsoft Documents

Now, combine the two.

**mervinshe** · 01-09-2012, 02:41 PM

Originally Posted by mervinshe

Hey did u get any answer for this prob....??? Rite now i need the answer for this... :) If u have found d solution means pls help m out.. :)

Thank for the reply.. :) It helped m.. :)

**quad64bit** · 01-09-2012, 02:49 PM

No problem!

**mervinshe** · 01-09-2012, 05:25 PM

Hey hi... :) I jst need one more help ya... :)I just dunno how to upload files into ms access using jsp in net beans 7.0.1... :( I know that we have to create an OLE object as the design type in the table... :) But i dunno how to insert files into ms access using a jsp.. :( I need a step by step explanation... :( Its jst buggin m coz m not able to find a proper solution for this problem... :( Pls help m out.. :) Thanks in advance.. :)

**quad64bit** · 01-09-2012, 05:31 PM

But i dunno how to insert files into ms access using a jsp

Insert files? You mean data into the access database?

**mervinshe** · 01-10-2012, 01:58 PM

Originally Posted by quad64bit

Insert files? You mean data into the access database?

Insert files means i meant lk files with the extension .pdf,.doc,.docx, etc.... :):)

**quad64bit** · 01-10-2012, 03:03 PM

You generally don't want to store files like that in a database. If you must, then you can read the file as binary data (byte array) and store it as a binary type in the database. You will need to make sure you use a sufficiently large data type, as the files you mentioned can be anywhere from a few kilobytes to hundreds of megabytes!

**Tolls** · 01-10-2012, 03:55 PM

1. Use a PreparedStatement.
2. Use setBinaryStream() to stream the data to the relevant OLE field.
3. execute.

**mervinshe** · 01-14-2012, 03:36 PM

Hi der.. :) I need a small help.. :) In one of my jsp pages i keep on getting the "GENERAL ERROR"... ;( I just dounno wat to do... ;( Can u pls help m wid it.. ;( My coding is... :) M doin this in net beans and it gets connected to MS ACCESS... :)

<!DOCTYPE HTML>

<%@page contentType="text/html" pageEncoding="UTF-8"%>

<%@ page import= "java.sql.*" %>
<%@ page import= "java.io.*" %>
<%@ page import= "java.lang.*" %>
<%@ page import= "java.util.*" %>

<%
try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url = "jdbc:odbc:Driver={Microsoft Access Driver " +"(*.mdb, *.accdb)};DBQ=C:\\Users\\DELL\\Documents\\more.acc db";
Connection con = DriverManager.getConnection(url);
System.out.println("Connected!");
Statement stmt=null,stmt1=null,stmt2=null;
stmt=con.createStatement();
stmt1=con.createStatement();
stmt2=con.createStatement();

String StudentName=request.getParameter("firstname");
String lastName=request.getParameter("lastname");
String passWord=request.getParameter("password");
String dateOfbirth=request.getParameter("dob");
String Email=request.getParameter("email");
String mobileNumber=request.getParameter("mobilenumber");
double mnum=Double.parseDouble(mobileNumber);
String Address=request.getParameter("add");

String fatherName=request.getParameter("fn");
String motherName=request.getParameter("mn");
String fatherOccupation=request.getParameter("fo");
String motherOccupation=request.getParameter("mo");
String Email1=request.getParameter("email1");
String mobileNumber1=request.getParameter("mobilenumber1" );
double mnum1=Double.parseDouble(mobileNumber1);

int sid=1000;
String cname=request.getParameter("course");

Random rand = new Random();
int randnum = rand.nextInt(sid+1);
String aw=request.getParameter("aware");

String s1="Artificial Intelligence";
String s2="Java";
String s3="Data Structures";
String s4="Software Testing";
String s5="Mobile Communications";

stmt.executeUpdate("INSERT INTO parentInfo VALUES("+randnum+",'"+fatherName+"','"+motherName+ "','"+fatherOccupation+"','"+motherOccupation+"',' "+Email1+"',"+mnum1+")");
stmt1.executeUpdate("INSERT INTO studentInfo(StudentID,StudentName,lastName,passWor d,dateOfBirth,Email,mobileNumber,Address,Awareness ) VALUES ("+randnum+",'"+StudentName+"','"+lastName+"','"+p assWord+"','"+dateOfbirth+"','"+Email+"',"+mnum+", '"+Address+"','"+aw+"')");

if(cname.equals(s1))
{
int cid=1;

stmt2.executeUpdate("UPDATE studentInfo SET CourseID="+cid+",CourseName='"+s1+"' WHERE StudentID="+randnum+" and StudentName='"+StudentName+"'");
out.println("<html><body>");
out.println("<h3>Hello "+StudentName+"...!</h3>");
out.println("<h4>Welcome To MoRE-Learning</h4>");
out.println("<br>Your details are:");
out.println("<table>");
out.println("<tr><td>STUDENT ID:</td><td>"+randnum+"</td></tr>");
out.println("<tr><td>COURSE ID:</td><td>"+cid+"</td></tr>");
out.println("<tr><td>COURSE NAME:</td><td>"+cname+"</td></tr>");
out.println("</table><br><br><br>");
out.println("<b>Click the link to take up the test:</b><a href='patternTest.jsp'>PATTERN TEST</a>");
out.println("</body></html>");

}
else if(cname.equals(s2))
{
int cid=2;

stmt2.executeUpdate("UPDATE studentInfo SET CourseID="+cid+",CourseName='"+s2+"' WHERE StudentID="+randnum+" and StudentName='"+StudentName+"'");
out.println("<html><body>");
out.println("<h3>Hello "+StudentName+"...!</h3>");
out.println("<h4>Welcome To MoRE-Learning</h4>");
out.println("<br>Your details are:");
out.println("<table>");
out.println("<tr><td>STUDENT ID:</td><td>"+randnum+"</td></tr>");
out.println("<tr><td>COURSE ID:</td><td>"+cid+"</td></tr>");
out.println("<tr><td>COURSE NAME:</td><td>"+cname+"</td></tr>");
out.println("</table><br><br><br>");
out.println("<b>Click the link to take up the test:</b><a href='patternTest.jsp'>PATTERN TEST</a>");
out.println("</body></html>");

}
else if(cname.equals(s3))
{
int cid=3;

stmt2.executeUpdate("UPDATE studentInfo SET CourseID="+cid+",CourseName='"+s3+"' WHERE StudentID="+randnum+" and StudentName='"+StudentName+"'");
out.println("<html><body>");
out.println("<h3>Hello "+StudentName+"...!</h3>");
out.println("<h4>Welcome To MoRE-Learning</h4>");
out.println("<br>Your details are:");
out.println("<table>");
out.println("<tr><td>STUDENT ID:</td><td>"+randnum+"</td></tr>");
out.println("<tr><td>COURSE ID:</td><td>"+cid+"</td></tr>");
out.println("<tr><td>COURSE NAME:</td><td>"+cname+"</td></tr>");
out.println("</table><br><br><br>");
out.println("<b>Click the link to take up the test:</b><a href='patternTest.jsp'>PATTERN TEST</a>");
out.println("</body></html>");

}
else if(cname.equals(s4))
{
int cid=4;

stmt2.executeUpdate("UPDATE studentInfo SET CourseID="+cid+",CourseName='"+s4+"' WHERE StudentID="+randnum+" and StudentName='"+StudentName+"'");
out.println("<html><body>");
out.println("<h3>Hello "+StudentName+"...!</h3>");
out.println("<h4>Welcome To MoRE-Learning</h4>");
out.println("<br>Your details are:");
out.println("<table>");
out.println("<tr><td>STUDENT ID:</td><td>"+randnum+"</td></tr>");
out.println("<tr><td>COURSE ID:</td><td>"+cid+"</td></tr>");
out.println("<tr><td>COURSE NAME:</td><td>"+cname+"</td></tr>");
out.println("</table><br><br><br>");
out.println("<b>Click the link to take up the test:</b><a href='patternTest.jsp'>PATTERN TEST</a>");
out.println("</body></html>");
}
else if(cname.equals(s5))
{
int cid=5;

stmt2.executeUpdate("UPDATE studentInfo SET CourseID="+cid+",CourseName='"+s5+"' WHERE StudentID="+randnum+" and StudentName='"+StudentName+"'");
out.println("<html><body>");
out.println("<h3>Hello "+StudentName+"...!</h3>");
out.println("<h4>Welcome To MoRE-Learning</h4>");
out.println("<br>Your details are:");
out.println("<table>");
out.println("<tr><td>STUDENT ID:</td><td>"+randnum+"</td></tr>");
out.println("<tr><td>COURSE ID:</td><td>"+cid+"</td></tr>");
out.println("<tr><td>COURSE NAME:</td><td>"+cname+"</td></tr>");
out.println("</table><br><br><br>");
out.println("<b>Click the link to take up the test:</b><a href='patternTest.jsp'>PATTERN TEST</a>");
out.println("</body></html>");
}
else
{
out.println("Please contact your Administrator");
}

con.close();
}
catch (SQLException e)
{
System.out.println("SQL Exception: "+ e.toString());
}
catch (ClassNotFoundException cE)
{
System.out.println("Class Not Found Exception: "+cE.toString());
}
%>

**Tolls** · 01-16-2012, 02:13 PM

Originally Posted by mervinshe

Hi der.. :) I need a small help.. :) In one of my jsp pages i keep on getting the "GENERAL ERROR"... ;( I just dounno wat to do... ;( Can u pls help m wid it.. ;( My coding is... :) M doin this in net beans and it gets connected to MS ACCESS... :)

Java Code:

<!DOCTYPE HTML>

<%@page contentType="text/html" pageEncoding="UTF-8"%>

<%@ page import= "java.sql.*" %>
<%@ page import= "java.io.*" %>
<%@ page import= "java.lang.*" %>
<%@ page import= "java.util.*" %>

<%
   try{
       Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
       String url = "jdbc:odbc:Driver={Microsoft Access Driver " +"(*.mdb, *.accdb)};DBQ=C:\\Users\\DELL\\Documents\\more.accdb";
       Connection con = DriverManager.getConnection(url);
       System.out.println("Connected!");
       Statement stmt=null,stmt1=null,stmt2=null;
       stmt=con.createStatement();
       stmt1=con.createStatement();
       stmt2=con.createStatement();
       
       String StudentName=request.getParameter("firstname");
       String lastName=request.getParameter("lastname");
       String passWord=request.getParameter("password");
       String dateOfbirth=request.getParameter("dob");
       String Email=request.getParameter("email");
       String mobileNumber=request.getParameter("mobilenumber");
       double mnum=Double.parseDouble(mobileNumber);
       String Address=request.getParameter("add");
      
       String fatherName=request.getParameter("fn");
       String motherName=request.getParameter("mn");
       String fatherOccupation=request.getParameter("fo");
       String motherOccupation=request.getParameter("mo");
       String Email1=request.getParameter("email1");
       String mobileNumber1=request.getParameter("mobilenumber1");
       double mnum1=Double.parseDouble(mobileNumber1);
       
       int sid=1000;
       String cname=request.getParameter("course");

       Random rand = new Random();
       int randnum = rand.nextInt(sid+1);
       String aw=request.getParameter("aware");
       
       String s1="Artificial Intelligence";
       String s2="Java";
       String s3="Data Structures";
       String s4="Software Testing";
       String s5="Mobile Communications";
       
       stmt.executeUpdate("INSERT INTO parentInfo VALUES("+randnum+",'"+fatherName+"','"+motherName+"','"+fatherOccupation+"','"+motherOccupation+"','"+Email1+"',"+mnum1+")");
       stmt1.executeUpdate("INSERT INTO studentInfo(StudentID,StudentName,lastName,passWord,dateOfBirth,Email,mobileNumber,Address,Awareness) VALUES ("+randnum+",'"+StudentName+"','"+lastName+"','"+passWord+"','"+dateOfbirth+"','"+Email+"',"+mnum+",'"+Address+"','"+aw+"')");
       
       if(cname.equals(s1))
       {
           int cid=1;
           
           stmt2.executeUpdate("UPDATE studentInfo SET CourseID="+cid+",CourseName='"+s1+"' WHERE StudentID="+randnum+" and StudentName='"+StudentName+"'");
           out.println("<html><body>");
           out.println("<h3>Hello "+StudentName+"...!</h3>");
           out.println("<h4>Welcome To MoRE-Learning</h4>");
           out.println("<br>Your details are:");
           out.println("<table>");
           out.println("<tr><td>STUDENT ID:</td><td>"+randnum+"</td></tr>");
           out.println("<tr><td>COURSE ID:</td><td>"+cid+"</td></tr>");
           out.println("<tr><td>COURSE NAME:</td><td>"+cname+"</td></tr>");
           out.println("</table><br><br><br>");
           out.println("<b>Click the link to take up the test:</b><a href='patternTest.jsp'>PATTERN TEST</a>");
           out.println("</body></html>");
           
       }
       else if(cname.equals(s2))
       {
           int cid=2;
         
           stmt2.executeUpdate("UPDATE studentInfo SET CourseID="+cid+",CourseName='"+s2+"' WHERE StudentID="+randnum+" and StudentName='"+StudentName+"'");
           out.println("<html><body>");
           out.println("<h3>Hello "+StudentName+"...!</h3>");
           out.println("<h4>Welcome To MoRE-Learning</h4>");
           out.println("<br>Your details are:");
           out.println("<table>");
           out.println("<tr><td>STUDENT ID:</td><td>"+randnum+"</td></tr>");
           out.println("<tr><td>COURSE ID:</td><td>"+cid+"</td></tr>");
           out.println("<tr><td>COURSE NAME:</td><td>"+cname+"</td></tr>");
           out.println("</table><br><br><br>");
           out.println("<b>Click the link to take up the test:</b><a href='patternTest.jsp'>PATTERN TEST</a>");
           out.println("</body></html>");
           
       }
       else if(cname.equals(s3))
       {
            int cid=3;
            
            stmt2.executeUpdate("UPDATE studentInfo SET CourseID="+cid+",CourseName='"+s3+"' WHERE StudentID="+randnum+" and StudentName='"+StudentName+"'");
            out.println("<html><body>");
            out.println("<h3>Hello "+StudentName+"...!</h3>");
            out.println("<h4>Welcome To MoRE-Learning</h4>");
            out.println("<br>Your details are:");
            out.println("<table>");
            out.println("<tr><td>STUDENT ID:</td><td>"+randnum+"</td></tr>");
            out.println("<tr><td>COURSE ID:</td><td>"+cid+"</td></tr>");
            out.println("<tr><td>COURSE NAME:</td><td>"+cname+"</td></tr>");
            out.println("</table><br><br><br>");
            out.println("<b>Click the link to take up the test:</b><a href='patternTest.jsp'>PATTERN TEST</a>");
            out.println("</body></html>");
          
        }
        else if(cname.equals(s4))
        {
            int cid=4;
            
            stmt2.executeUpdate("UPDATE studentInfo SET CourseID="+cid+",CourseName='"+s4+"' WHERE StudentID="+randnum+" and StudentName='"+StudentName+"'");
            out.println("<html><body>");
            out.println("<h3>Hello "+StudentName+"...!</h3>");
            out.println("<h4>Welcome To MoRE-Learning</h4>");
            out.println("<br>Your details are:");
            out.println("<table>");
            out.println("<tr><td>STUDENT ID:</td><td>"+randnum+"</td></tr>");
            out.println("<tr><td>COURSE ID:</td><td>"+cid+"</td></tr>");
            out.println("<tr><td>COURSE NAME:</td><td>"+cname+"</td></tr>");
            out.println("</table><br><br><br>");
            out.println("<b>Click the link to take up the test:</b><a href='patternTest.jsp'>PATTERN TEST</a>");
            out.println("</body></html>");
        }
        else if(cname.equals(s5))
        {
            int cid=5;
            
            stmt2.executeUpdate("UPDATE studentInfo SET CourseID="+cid+",CourseName='"+s5+"' WHERE StudentID="+randnum+" and StudentName='"+StudentName+"'");
            out.println("<html><body>");
            out.println("<h3>Hello "+StudentName+"...!</h3>");
            out.println("<h4>Welcome To MoRE-Learning</h4>");
            out.println("<br>Your details are:");
            out.println("<table>");
            out.println("<tr><td>STUDENT ID:</td><td>"+randnum+"</td></tr>");
            out.println("<tr><td>COURSE ID:</td><td>"+cid+"</td></tr>");
            out.println("<tr><td>COURSE NAME:</td><td>"+cname+"</td></tr>");
            out.println("</table><br><br><br>");
            out.println("<b>Click the link to take up the test:</b><a href='patternTest.jsp'>PATTERN TEST</a>");
            out.println("</body></html>");
         }
       else
       {
           out.println("Please contact your Administrator");
       }

       con.close();
       }
   catch (SQLException e) 
     {
              System.out.println("SQL Exception: "+ e.toString());
     } 
     catch (ClassNotFoundException cE) 
     {
              System.out.println("Class Not Found Exception: "+cE.toString());
     }
%>

1. Use code tags when posting code.
2. Don't do database stuff in a JSP page.
3. AT least do printStackTrace() for exceptions, so you know where your errors are coming from.
4. Use a PreparedStatement instead of concatenating your SQL, then bind (setXXX()) the variables into the PreparedStatement and then execute it.

I think that should give you enough to be going on with.

ETA:
5. Close your resources (ie your connection) in a finally block.

**mervinshe** · 01-17-2012, 12:01 PM

Thanku for the help... :):)

**mervinshe** · 01-18-2012, 11:16 AM

Originally Posted by Tolls

1. Use a PreparedStatement.
2. Use setBinaryStream() to stream the data to the relevant OLE field.
3. execute.

Can you please provid m with a sample code coz m new to all these suffs... :(:(

**arielb** · 01-18-2012, 03:00 PM

Here's an example that can help you
Insert File

**Tolls** · 01-18-2012, 03:25 PM

Not a bad example (and a lot better than most on the net), though there is a bug in the reading of the Blob's binary stream.

Java Code:

while ((byteLeidos = salida.read(arreglo)) > 0) {
    archivoSalida.write(arreglo, 0, byteLeidos);
}

That should be '> -1' not '> 0', since it is quite possible for the read() call to return nothing into the byte[], but for the stream to have not reached the end.
'-1' means "I have reached the end of the stream".

**arielb** · 01-18-2012, 03:39 PM

Tolls thank you very much, I'll take into account to correct and to my knowledge:)

**mervinshe** · 01-18-2012, 09:09 PM

Originally Posted by arielb

Here's an example that can help you
Insert File

Hi der Mr.Tolls and Mr.Arielb :)
Thank you for helping m... :) I have got an idea and i have done this... :) I'll explain it in a step by step process... :):)

Step 1: I am using MS-ACCESS 2010 as my database and these are the details regarding it... :)
Database name=upl
Table name=up
No. of fields=1
Field name=fil
Data type for that field name=attachment

I am not sure of the data type to be used :( There is no predefined BLOB or CLOB in MS-ACCESS :(

Step 2: I am using netbeans 7.0.1 and i have created two jsp pages... :)

-->The first jsp page is "index.jsp" which is a form, from where i browse the file and click on the upload button.. :)

The coding of "index.jsp" is:

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<html>
<head><title>UPLOAD</title></head>
<body>
<form method="post" action="plsworkout.jsp" name="upform" enctype="multipart/form-data">
<table width="60%" border="0" cellspacing="1" cellpadding="1" align="center" class="style1">
<tr>
<td align="left"><b>Select a file to upload :</b></td>
</tr>
<tr>
<td align="left">
<input type="file" name="uploadfile" size="50">
</td>
</tr>
<tr>
<td align="left">
<input type="hidden" name="todo" value="upload">
<input type="submit" name="Submit" value="Upload">
<input type="reset" name="Reset" value="Cancel">
</td>
</tr>
</table>
</form>
</body>
</html>

--> My second jsp page is "plsworkout.jsp":):) In this page i give the connection and i upload the files into the database :)
I have the "System.out.println("Connected!")" statements just to know till where the code gets executed coz i am a newbie :( I get the
Connected statements upto "Connected3" but after that i get an error lk "File not found" :( I found out that the file name given dynamically is not being considered as the filename :( I dunno how to insert file name dynamically :(
Pls help m out with all these problems and if i have done anything wrong means pls do correct m :(:(

The coding for "plsworkout.jsp" is,

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE html>
<%@ page import= "java.sql.*"%>
<%@ page import= "java.io.*" %>
<%@ page import= "java.lang.*" %>
<%@ page import= "java.util.*" %>
<%

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url = "jdbc:odbc:Driver={Microsoft Access Driver " +"(*.mdb, *.accdb)};DBQ=C:\\Users\\DELL\\Documents\\upl.accd b";
Connection con = DriverManager.getConnection(url);
System.out.println("Connected!");

InputStream input = null;
int entered=0;

PreparedStatement pst = null;

try
{
File file;
String insert;
String name=request.getParameter("uploadfile");

con.setAutoCommit(false);

insert = "INSERT INTO up VALUES(?)";
System.out.println("Connected1!");

pst = con.prepareStatement(insert);
System.out.println("Connected2!");

file = new File("name");
System.out.println(file);
System.out.println("Connected3!");

input = new FileInputStream("file");
System.out.println(input);
System.out.println("Connected4!");

pst.setBinaryStream(1, input, (int) file.length());
System.out.println("Connected5!");

entered = pst.executeUpdate();
con.commit();

}
catch (SQLException e)
{
System.out.println("SQL Exception: "+ e.toString());
}

finally
{

con.close();

}
%>

**Tolls** · 01-19-2012, 10:07 AM

See point 2 in my post above. That second JSP should be a servlet.

Also that first JSP should simply be an html file, as there is no Java in there at all. By making it a JSP you are forcing the container to parse this and turn it into a servlet, which is a lot more overhead than simply having it serve up a plain html page.

And your upload code in the JSP is wrong.
You need to handle this as a Multi-part request.
Apache has a FileUpload package that is quite useful for this sort of thing.

**mervinshe** · 01-19-2012, 11:06 AM

Originally Posted by Tolls

See point 2 in my post above. That second JSP should be a servlet.

Also that first JSP should simply be an html file, as there is no Java in there at all. By making it a JSP you are forcing the container to parse this and turn it into a servlet, which is a lot more overhead than simply having it serve up a plain html page.

And your upload code in the JSP is wrong.
You need to handle this as a Multi-part request.
Apache has a FileUpload package that is quite useful for this sort of thing.

What FileUpload package? Can you please elaborate it :)