Urvashi Thakkar
Read Excel data using JSP and update MySQL databse
1 Answer(s)      4 years and a month ago
Posted in : JSP-Servlet

HOw to read excel data using JSP and update MySQl database ?

Advertisement
View Answers

April 26, 2011 at 11:08 AM


Follow these steps:

1)Go to the start>>Control Panel>>Administrative Tools>> data sources.

2)Click Add button and select the Driver do Microsoft Excel(*.xls).

3)After selecting the driver, click finish button.

4)Then give Data Source Name,select drive, directory and excel file and click ok button.

5)Your DSN will get created.

6) Then run your jsp code:

<%@page import="java.sql.*"%>
<%
try{
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
         Connection con = DriverManager.getConnection("jdbc:odbc:excel");
         Statement stmt = con.createStatement();
         ResultSet rs = stmt.executeQuery("select  * from [sheet$]");
         Class.forName("com.mysql.jdbc.Driver");
           Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");
           Statement st=conn.createStatement();
         while (rs.next()){
        st.executeUpdate("insert into student(firstname,lastname,email,address) values('"+rs.getString(1)+"','"+rs.getString(2)+"','"+rs.getString(3)+"','"+rs.getString(4)+"')");
        }

        out.println("Data has been read from the excel file and inserted into the database.");  
}
catch(Exception e){
System.out.println(e);
}
        %>

In the above code, we have used sun.jdbc.odbc.JdbcOdbcDriver to read the excel file and excel is our dsn,sheet is our sheet name in the selected excel file. The excel file which you want to read should be selected when creating dsn.










Related Tutorials/Questions & Answers:
Read Excel data using JSP and update MySQL databse
Read Excel data using JSP and update MySQL databse  HOw to read excel data using JSP and update MySQl database
Read data from excel file and update database using jsp
Read data from excel file and update database using jsp  read data from excel file and update database using jsp Hi, I am using a MySQL database... upload excel file and update database using JSP ? Thanks in Advance
Advertisements
JSP/Servlet to read and update Excel
JSP/Servlet to read and update Excel  Hi Team, My requirement is based upon my input(name) value via one jsp, the program(jsp/servlet) should fetch... access to edit the values and update back to the Excel. So when the user give update
update excel sheet using jsp::
update excel sheet using jsp::   Hi Sir,... I have a excel... given excel sheet and display it into another excel sheet using jsp" i am using 'session' to get the empid from one page to another jsp
insert data into mysql databse using swing
insert data into mysql databse using swing   Blockquote hi, here is my code, i want code for store data into mysql database when click on submit button from access data from this form, please provide code, import java.awt.
read excel data from jsp - JSP-Servlet
read excel data from jsp  Hi how to read excel file from jsp? Excel file is created manually entered data having many sheets? and read the entire...)excel.jsp: Read And Edit Excel File  continue
Retrieve data from databse using where in JSP
Retrieve data from databse using where in JSP  Hi, can somebody help me? I have a jsp page. in that i want to get data from the database where username is matched. i tried it. but can't get the output. here is the code. please
how to read data from excel file through browse and insert into oracle database using jsp or oracle???
how to read data from excel file through browse and insert into oracle database using jsp or oracle???  sir.. i have number of excel sheets which...://www.roseindia.net/answers/viewqa/JSP-Servlet/28123-write-excel-file-into-the-oracle
read data from Excel sheet
read data from Excel sheet  Hi, How to read data from an excel sheet using JSP. Thank you
how read excel data into database using struts2 with hibernate
how read excel data into database using struts2 with hibernate   hi friends, i am venkat i am started learning struts 2, please help me how to read excel data into database using struts2 with hibernate, please show me
Uploading Excel sheet record in JSP to insert data in MySql
Uploading Excel sheet record in JSP to insert data in MySql  Need Help how to upload Excel (.xls) file and insert data in Mysql using JSP it wil be wonder for me if any help me
About jsp
About jsp  Read Excel data using JSP and update MySQL databse
excel sheet reading and using that data - JSP-Servlet
excel sheet reading and using that data  i have to do a read a excel sheet file of a employee record and then i have to use a employee details to send mail to those employees how to do in jsp sir please help me sir.. Thanks
how to print data(From databse Oracle10g) in preformatted A4 and A3 paper using jsp
how to print data(From databse Oracle10g) in preformatted A4 and A3 paper using jsp  how to print data(From databse Oracle10g) in preformatted A4 and A3 paper using jsp. I am inserting analysis report into a database using
how to read values from excel sheet and compare with database using jsp
how to read values from excel sheet and compare with database using jsp  hi sir i am arun how to read values from excel sheet and compare with database using jsp coding i.e, if i have 6(assetid,assetname,serialno,cubical
How to export data from jsp to excel sheet by using java
How to export data from jsp to excel sheet by using java   How to export data from jsp to excel sheet by using java
Error in reading Excel data using jsp
Error in reading Excel data using jsp  ERROR while executing bellow code:java.io.IOException: Invalid header signature; read 576460838270094160.... ---------- java.io.IOException: Invalid header signature; read 576460838270094160, expected
How to read every cell of an excel sheet using Apache POI and insert those data into a DB?
How to read every cell of an excel sheet using Apache POI and insert those data into a DB?   i have an excel sheet, whose data in each cell has to be read and those data has to be inserted in the DB. This has to happen for all
excel report fro jsp mysql
excel report fro jsp mysql  Dear Sir, I am facing some problem while generating excel report form mysql database using jsp code. With the help from... data. But I need your help for generating a excel file from mysql DB for blob
How to Retrieve Excel data into mysql using type 2 JDBC-ODBC driver
How to Retrieve Excel data into mysql using type 2 JDBC-ODBC driver  Can any please help it is very urgent. I tried to read excel sheet using ODBC.... so, can any one help me without creating DNS we should retrieve data from excel
jsp data in excel - JSP-Servlet
in jsp using table. and getting this data from servlet which has query and this query data has come from beans and using beans i put it in excel jsp in table...jsp data in excel  i have create jsp page which has button
Upload Exce Data into MySql Using Jsp and Servlet - JSP-Servlet
Upload Exce Data into MySql Using Jsp and Servlet  now i am doing a project my concept is to insert the Excel File Data is uploaded and inserted into Mysql Database table so please give the coding to me, it's very urgent for me
Exporting data from mysql into csv using jsp
Exporting data from mysql into csv using jsp  Hi friends.... I want to export the data from mysql to csv file using... i am having 30 columns in my database.. Eg- text1,text2,text3,....,upto text30... i want to export this data
php import data from excel to mysql
php import data from excel to mysql  php import data from excel to mysql
How to read a rows which have a values in a excel file using apache poi - JSP-Servlet
How to read a rows which have a values in a excel file using apache poi  Dear sir, How to read excel file in that only a rows which are having some values using apache poi...please help me sir. Thanks and Regards
ID using get the databse latest value and displayed in jsp
ID using get the databse latest value and displayed in jsp   I have... ... once click the submit button the data will be stored in mysql database... in the form (it means latest values) ... how to retrive that values using ID
To read & write a excel file using the core java
To read & write a excel file using the core java  Hai, I'm new to JavaProgram.But now i need java program to read & write a excel file so, can anyone help me to learn the above mentioned topic(link for the portion
ID using get the databse latest value and displayed in jsp
ID using get the databse latest value and displayed in jsp  function... conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/TEst", "root", "root"); //String query = "select * from Data where id='"+no+"'"; Statement st
How to insert data into databse by JSP form registration page in netbeans
How to insert data into databse by JSP form registration page in netbeans  ... of data for login purpose. I have created 1 form in JSP netbeans with both username.... & how to connect tht form to my databse , so it can store data?   1
Read data from Excel and insert in to DB and export data from DB to Excel
Read data from Excel and insert in to DB and export data from DB to Excel  Read data from Excel and insert in to DB and export data from DB to Excel Hi, I need to read the data from excel and I need to insert the same in to DB
update data to mysql database through JTextField
update data to mysql database through JTextField  I am getting an error, when i am updating a data to mysql database through JTextField. Send me the code used to solve my error. public void update(){ try