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 (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.

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

Ads









Related Tutorials/Questions & Answers:
read data from Excel sheet
read data from Excel sheet  Hi, How to read data from an excel sheet using JSP. Thank you
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
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
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 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 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 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
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 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
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
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 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 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
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
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
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
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
Store data in HTML forms from Excel sheet
Store data in HTML forms from Excel sheet  I have a excel file having multiple coloums, and having one Html page having forms corrospoding to excel coloums. I want to auto fill up the excel data into html forms, Any one please
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
How to insert rows from Excel spreadsheet into database by browsing the excel file?
How to insert rows from Excel spreadsheet into database by browsing the excel file?  I want to insert rows from excel sheet to database.for this i... excel file and insert rows into MSSQL database in JSP???   Have a look
reading data from excel file and plotting graph
reading data from excel file and plotting graph  I am doing a project... the data in excel file, i have to plot graphs based on CELL ID selected. please help... that reads an excel file using POI api and using the data of excel file
how to display the data from excel to webpage
how to display the data from excel to webpage  Hi, I need help... that reads the excel file and store data into table. <%@page import="java.io.*"%>... search for the value 4024 in a excel file and to display the remaining values
How to Get The Data from Excel sheet into out jsp page???
How to Get The Data from Excel sheet into out jsp page???  How to Get The Data from excel sheet to out jsp page in webApp
insert values from excel file into database
insert values from excel file into database   hi i want to insert values from Excel file into database.Whatever field and contents are there in excel... the following link: Insert values from excel file to database
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
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
How to Retrieve Data from the database and write into excel file using Java
How to Retrieve Data from the database and write into excel file using Java  Hi, I am trying to develop an small application where i trying to retrieve Data from the database and store the details in excel file. Please can
How to import data from sql server table into an excel file by creating the rows dynamically in the excel according to the dataabase??
How to import data from sql server table into an excel file by creating the rows dynamically in the excel according to the dataabase??  How to import data from sql server table into an excel file by creating the rows dynamically

Ads