Latest Tutorials| Questions and Answers|Ask Questions?|Site Map



Home Answers Viewqa Java-Beginners Read data from Excel and insert in to DB and export data from DB to Excel
Login         

View Questions and Answers by Category

Advertisements


 
Have Programming Question? Ask it here!
 
 
 


saranya
Read data from Excel and insert in to DB and export data from DB to Excel
1 Answer(s)      3 years and 11 months ago
Posted in : Java Beginners


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 (vice versa). I have an Excel sheet contains 10 Telephone numbers, I have customer database in which I have stored all customer information. Now I need the customer status (customer status is stored in customer database.. I need whether the customer is "Active" or "cance" status....I need that alone for the corresponding Telephone numbers whcih is in Excel) for the corresponding telephone numbers which is in Excel. Then I need that customer status + customer Telephone number from DB to Excel format.


Advertisement
View Answers

December 10, 2010 at 5:57 PM


Hi Friend,

You can read the data from the excel file using JDBC. For this you need to create dsn connection.

Follow these steps to create a dsn connection:

1) Go to the control Panel>>Administrative Tools>>Data Sources.

2)Click Add button and select Driver do Microsoft Excel driver. Click finish button

3)Add Data Source Name and click select workbook button to select the workbook whose data is to be retrieved.

4)Click ok and compile the following code:

import java.sql.*;
import java.util.*;

public class InsertIExcelValueToDatabase{
    public static void main(String[] args)throws Exception{
    ArrayList list1=new ArrayList();
    ArrayList list2=new ArrayList();
    try{
    Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
         Connection con = DriverManager.getConnection("jdbc:odbc:excel");
         Statement stmt = con.createStatement();
         ResultSet rs = stmt.executeQuery("select  name, address from [Sheet1$]");
         while (rs.next()) {
            String name = rs.getString("name");
            String address = rs.getString("address");
            list1.add(name);
            list2.add(address);
            System.out.println(name + "  "+address);
        }
         con.close();

         Class.forName("com.mysql.jdbc.Driver");
          Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");
         Statement st = conn.createStatement();
         for(int i=0;i<list2.size();i++){
             String nn=list1.get(i).toString();
             String add=list2.get(i).toString();

             st.executeUpdate("insert into data1(name,address) values('"+nn+"','"+add+"')");
              System.out.println(nn+" "+add);
         }
    }
    catch(Exception e){
        System.out.println(e);
    }
       }
        }

Thanks



Related Tutorials/Questions & Answers:
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
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
Advertisements
connection of java file to excel file and put data from excel file into oracle db
as wellas to oracle db to insert excel data into database...connection of java file to excel file and put data from excel file into oracle db  how to create button on excel sheet using java swing and how we
read data from Excel sheet
read data from Excel sheet  Hi, How to read data from an excel sheet using JSP. Thank you
export data from database to excel sheet - JDBC
export data from database to excel sheet  I am facing a problem about exporting required data from database table to ms-excel sheet.i mean whenever I...("Data is saved in excel file."); rs.close(); connection.close(); } catch
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
How to export data from html to excel sheet by using java
How to export data from html to excel sheet by using java   How to export data from html to excel sheet by using java
how to insert, retrieve data from,to db(code)....
how to insert, retrieve data from,to db(code)....  Hi..... i ve... that data will b stored in db(m using sybase). 2.also hw to retrieve the data from db. cn u plz tel me the code in detail.n i thk it shud b written in jdbc
How to export data from html file to excel sheet by using java
How to export data from html file to excel sheet by using java    How to export data from html file to excel sheet by using java
how to insert, retrieve data from,to db(code)....
how to insert, retrieve data from,to db(code)....  Hi, i have created... register his details,(for ex,username,password,dob,gender) 1.how that data will b stored in db(m using sybase). 2.also hw to retrieve the data from db. cn u
Export html data to excel
Export html data to excel  I am trying to export data from an html page to an excel sheet.Any advise
How to export data from html file to excel sheet by using java
How to export data from html file to excel sheet by using java   reading the data from Html file
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
How to insert data from a combobox and textbox values into DB using JSP?
How to insert data from a combobox and textbox values into DB using JSP?  hi, How to insert a comb-box and a text box values in to DB using JSP? @DB... and textbox2? into MYSQL DB
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... a browse button which can upload a excelfile and after uploading the data should
How to export data from database to excel sheet by using java in standalone project
How to export data from database to excel sheet by using java in standalone project  How to export data from database to excel sheet by using java in standalone project
Java swing: Export excel sheet data to JTable
Java swing: Export excel sheet data to JTable In this tutorial, you will learn how to read data from excel file and export it to JTable. In swing... and POI-HSSF api. Here we are using JExcel api to fetch the data from excel sheet
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 for scientific research analysis. My team members sends research data in excel file
php import data from excel to mysql
php import data from excel to mysql  php import data from excel to mysql
How to access data yearly from DB in C# ?
How to access data yearly from DB in C# ?  how to access data yearly from database in C#. I have code but i m not able to retrieve data yearly from DB table. string date1 = dateTimePicker1.Value.Date.ToString("dd/MMM/yyyy
Retrieving specific data from excel
Retrieving specific data from excel  Hello everyone, i have written a simple code to retrieve data from excel sheet and working fine, the excel file... first sheet from the workbook HSSFSheet sheet = workbook.getSheetAt(0
How to export data from database to excel sheet by using java swing in standalone project - Java Beginners
How to export data from database to excel sheet by using java swing... from u to solve my problem. my problem is i don know how to export data from database to excel sheet by using java swing in standalone project.I get solution from
How to get data from Excel sheet - Struts
How to get data from Excel sheet  Hi, I have an excel sheet with some data(including characters and numbers). Now i want read the data from excel sheet and display in console first then later insert this data into database
Export Extjs Gridview data to excel in jsp
Export Extjs Gridview data to excel in jsp  i need to export the extjs girdview data to excel can you please help me thanks in advance
extract data from excel sheet to mysql
extract data from excel sheet to mysql  sir, i want to extract data from excel sheet and save the data in mysql5.0 database in the form of table
export data to excel sheet - JSP-Servlet
export data to excel sheet  Hi.. how to export data to excel sheet from jsp? and how to update the excel sheet from jsp? and how to get the data from excel sheet? and how to make calculations in excel sheet like total avg
Export data in excel sheet in java in struts - Struts
Export data in excel sheet in java in struts  Hello, All how can i export data in excel file i java  Hi friend, For solving the problem visit to : http://www.roseindia.net/jsp/excelfile.shtml Thanks
Insert data in Excel File from Database using JSP
Insert data in Excel File from Database  using JSP ... the data from database, create an excel file and data insert into newly... developed a application to insert data  in excel file from database in JSP. We
Function data from web in MS excel
Function data from web in MS excel  Hello, I would like to import data from one webpage to Excel using function Data - From Web. The problem is that this web page has at the end .jsp . When I open it via Excel, click to data
displaying data from ms excel in form.
displaying data from ms excel in form.  Hi all, I have a requirement as mentioned below: Requirement: I have stored some data in the ms excel... in the column of the excel and also the mechanism to get this data and display
Export data to Excel Sheet in STRUTS 1.3
Export data to Excel Sheet in STRUTS 1.3  hi. how can i export content present on the jsp to excel sheet on button click..... and challenging part is i have to merge few rows and columns for a particular field(Value
Advertisements
 

 

 

DMCA.com