Home Answers Viewqa JDBC insert excel value in to oracle data base

 
 


Yogi
insert excel value in to oracle data base
3 Answer(s)      2 years and 7 months ago
Posted in : JDBC

Hi All I am using this code for saving excel value to oracle database table. code is working properly but in table i am geting some diffrent type value like... o org.apache.poi.hssf.usermodel.HSSFCell@19360e2

i am not getting right value...

my code is...

import java.io.*;
import java.sql.*;
import java.util.*;
import java.util.regex.*;
import org.apache.poi.hssf.usermodel.*; 
import org.apache.poi.poifs.filesystem.POIFSFileSystem; 
public class Insert { 

ConnectionToCustomDatabase dba=new ConnectionToCustomDatabase();

public static void main( String [] args ) { 
        String fileName="c:/hhh.xls"; 
        Vector dataHolder=read(fileName); 
        saveToDatabase(dataHolder);    } 
    public static Vector read(String fileName) 
       {        Vector cellVectorHolder = new Vector(); 
       try{ 
           System.out.println("try is executing");
           FileInputStream myInput = new FileInputStream(fileName); 
           System.out.println("try is executing1");
           POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput); 
           System.out.println("try is executing2");
           HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem); 
           System.out.println("try is executing3");
           HSSFSheet mySheet = myWorkBook.getSheetAt(0); 
           System.out.println("try is executing4");
           Iterator rowIter = mySheet.rowIterator(); 
           while(rowIter.hasNext()){ 
               HSSFRow myRow = (HSSFRow) rowIter.next(); 
               Iterator cellIter = myRow.cellIterator(); 
               Vector cellStoreVector=new Vector(); 
               System.out.println("try is executing5");
               while(cellIter.hasNext()){ 
                   HSSFCell myCell = (HSSFCell) cellIter.next(); 
                   cellStoreVector.addElement(myCell); 
                   } 
               cellVectorHolder.addElement(cellStoreVector); 
                 } 
            }
               catch (Exception e){
                   e.printStackTrace(); 
                                   } 
                    return cellVectorHolder; 
                 } 
                     private static void saveToDatabase(Vector dataHolder) { 
                         System.out.println("try is executing6"); 
                    String username=""; 
                     String password=""; 
                     for (int i=0;i<dataHolder.size(); i++){ 
                         Vector cellStoreVector=(Vector)dataHolder.elementAt(i); 
                         for (int j=0; j &lt; cellStoreVector.size();j++){ 
                             HSSFCell myCell = (HSSFCell)cellStoreVector.elementAt(j);
                             String st = myCell.toString(); 
                             username=st.substring(0,1); 
                             password=st.substring(0); 
                             } 
                         try{ 
                             System.out.println("try is executing7");
                             ConnectionToCustomDatabase dba;
                             Connection con = null;
                             PreparedStatement pstmt = null;
                             ResultSet rs = null;
                             //String sqlquery ="";
                             dba = new ConnectionToCustomDatabase();
                             con = dba.getVIS2Connection();

PreparedStatement stat=con.prepareStatement("insert into login(username,password1) values('"+username+"','"+password+"')");       

                         System.out.println(username);
                         System.out.println(password);
                         stat.execute();   
                                                     pstmt = con.prepareStatement(qry);
                            rs = pstmt.executeQuery();
                         System.out.println("try is executing8");
                         // System.out.println(k);
                         System.out.println("Data is inserted");
                         // stat.executeQuery()
                    if(rs.next())
                    {
                        String uu=rs.getString("username");
                        String pp1=rs.getString("password1");
                        System.out.println(uu);
                        System.out.println(pp1);

                    }
                          rs.close();
                         con.close();
                     }     
                     catch(Exception e)
                     {
                         System.out.print(e);
                     } 
                     }
                 }     
             }

plz find the bug on this code...and reply

View Answers

December 3, 2010 at 3:16 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);
    }
       }
        }

Here excel is the data source name and Sheet1 is the excel sheet name.

Thanks


December 3, 2010 at 5:17 PM


thanks dear But If i upload this Excel file then how excel data will mapp to oracle database table. My project requrement is when somebuddy fill a excel form then all form value will save on database table..is it any solution of this problem. Thanks dear Yogesh


December 21, 2011 at 1:26 PM


hi i need to test ur code to check bugs so please send complete code









Related Pages:
insert excel value in to oracle data base
insert excel value in to oracle data base  Hi All I am using this code for saving excel value to oracle database table. code is working properly... this Excel file then how excel data will mapp to oracle database table. My project
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
Data base - JDBC
Data base  I want to do this Inserting a record with Object type...( "jdbc:oracle:thin:@mysys:1521:sandb",p); PreparedStatement ps= con.prepareStatement( "insert into personaldetails(empno,photo,permanent_address) values
JSP data base validation
JSP data base validation  please explain how to validate form input string with database n also how its notify that entered data exists already...;td><input type="submit" value="submit"/></td> </tr> <
excel
excel  how to save excel sheet data into the database using poi api...=stat.executeUpdate("insert into person(Name,Address) value('"+name+"','"+address..."); Statement stat=con.createStatement(); int k=stat.executeUpdate("insert
getting int values from form and insert it in data base in jsp
getting int values from form and insert it in data base in jsp  how... = Integer.parseInt(s); Then you can write JDBC code to insert data...=st.executeUpdate("insert into data(name,age, address) values('"+name+"',"+age
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
value is inserted into the sql table through jsp-jdbc but not getting stored into the data base,only row is increasing.
value is inserted into the sql table through jsp-jdbc but not getting stored into the data base,only row is increasing.  <html> <...="Submit" value="Submit"></td> </tr> </table>
Data base Connectivity
Data base Connectivity  How to configure JDBC connection for Oracle data base.In configuring it is asking select Data server name and TNS.I typed Oracle for Data Server Name and IN TNS what to type? I pulled the list for TNS
data (image ,audio) inserting to data base (oracle 10g) and retriving the same data from jsp
data (image ,audio) inserting to data base (oracle 10g) and retriving the same data from jsp   data (image ,audio) inserting to data base (oracle 10g) and retriving the same data from jsp with script of data base plz help me
Insert excel file data into database Using Java Programming
Insert excel file data into database in Java Program In this PHP tutorial section, you will learn how to insert excel file data into the database. We have used Jakarta POI api for inserting excel file data into database in this purpose
Convert the excel sheet data into oracle table through java or jsp
Convert the excel sheet data into oracle table through java or jsp  Hi Friends, Let me help this issue i am phasing Convert the excel sheet data into oracle table through java or jsp
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... and insert the data into excel file. <%@ page import
insert data
insert data  i've got a problem to insert my data to database.i can...; value="<%=dateFormat.format(date)%>"> </tr> <...;input type="submit" name="Submit" value="Submit">
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
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 insert the bulk data into the data base from the table of jsp page to another jsp page
how to insert the bulk data into the data base from the table of jsp page... to insert the marks details into the data base i have retrive the rollno and name and i have to insert the mark for n number student in the table i don't how
how to insert checkbox value into database using jsp
how to insert checkbox value into database using jsp  How to insert check box value to the oracle database using jsp? I want to create hotel's package. where the admin will select the activities to insert in the PACKAGE table. I
data base
data base  how to connect coding in data base ?   The given code connects JSP page to database. You need to import the mysql connection jar file to jdk as well as to the lib of tomcat. Moreover you need to set classpath
java script unable to connect to oracle database and insert data
is to insert data from jsp to oracle database 8, so i create a table in oracle...java script unable to connect to oracle database and insert data  ... jsp coding in rose india for sql and i changed the drivers for oracle.. when i try
DATA BASE
DATA BASE  Create a program to establish a connection to the PCTBC... the data using a J Table.   Here is a code that connects to MySql... main(String[] args) { Vector columnNames = new Vector(); Vector data = new Vector
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
jsp with excel sheet data uploading into database
jsp with excel sheet data uploading into database  how to upload data in excel sheet with jsp into oracle 10g database
what is the jsp coding to insert a data in database tables
what is the jsp coding to insert a data in database tables  Dear Sir, I Want to know the coding for insert the data from jsp to oracle database.. my...;td></td><td><input type="submit" value="Submit"></td><
JDBC to EXcel
JDBC to EXcel  How to insert data in excel sheet from java
how to insert data in database using html+jsp
how to insert data in database using html+jsp  anyone know what... is student. */ String URL = "jdbc:oracle:thin:@10.2.160.244:1521:prodasdb... = null; // declare a resultset that uses as a table for output data from
error oracle database connection?
error oracle database connection?  hi now i am trying to connect oracle database and also insert my data into table, but it's not working.. I created one user registration form. when i give values in the form, i want stored data
How to insert clob data??
How to insert clob data??  Can any one tell me in details how... pl/sql blocks and statements.I want to insert value of a textarea into clob... to insert more than 4000 characters.I have heard that clob fields can hold 4 gb
Insert Image into Excel Sheet
Insert Image into Excel Sheet In this section, you will learn how to insert image into excel sheet using Apache POI. In the given below example, am image is insert into excel sheet at row 1 and at column 1. you can specify the top
retrive data from oracle to jsp
retrive data from oracle to jsp  i am a beginer in jsp so please help... cost and manager name and storing it in the data base. in search proj fiel... connected to data base
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
Inserting data in Excel File - Java Beginners
Inserting data in Excel File  Dear Sir, How can we fill the excel with fetched data from the database like Oracle, DB2? Is it possible to create an excel with filled data? Kindly give me the solutions to questions
oracle - JDBC
oracle  HI i am using ORACLE 10g. How to i find "TNSNAMES.ORA" file in my system after installation of ORACLE 10g succussfully. please send to me answer. i know my port number is 8080 and some other data but i want
insert rows from Excel sheet into a database by browsing the file
insert rows from Excel sheet into a database by browsing the file  hello, I am trying to insert rows from Excel sheet into SQL database by browsing Excel file in java(JSP). I can insert rows using ODBC connetion. But using odbc
retrieving of value from excel file - JSP-Servlet
retrieving of value from excel file  Dear sir, Thanks for sending... this message to all the employees when i upload a file then it fetch a data from that excel sheet i.e this matter will take a para meter values from the excel
retrieving of value from excel file - JSP-Servlet
retrieving of value from excel file  Dear sir, Thanks for sending... this message to all the employees when i upload a file then it fetch a data from that excel sheet i.e this matter will take a para meter values from the excel
java + excel data +graph - JDBC
java + excel data +graph  i am doin' a project in which i need to take up data from excel sheets and work upon them mathematically and finally draw...(a); if (cell1 != null){ String value = null; switch
Data base related question
Data base related question  sir my table has only one element(that is pno),i am using ms-access as backend. i put only one element i want to retrieve that element .how can i retrieve that element,using jdbc technology. please
inserting same data to different tables in oracle
inserting same data to different tables in oracle  Hi there ...! i have to insert un,pwd, to three oracle tables, like admin, user, stud, but i want pickup these table names from a drop down list in html...is there any chance can
inserting same data to different tables in oracle
inserting same data to different tables in oracle  Hi there ...! i have to insert un,pwd, to three oracle tables, like admin, user, stud, but i want pickup these table names from a drop down list in html...is there any chance can
inserting same data to different tables in oracle
inserting same data to different tables in oracle  Hi there ...! i have to insert un,pwd, to three oracle tables, like admin, user, stud, but i want pickup these table names from a drop down list in html...is there any chance can
inserting same data to different tables in oracle
inserting same data to different tables in oracle  Hi there ...! i have to insert un,pwd, to three oracle tables, like admin, user, stud, but i want pickup these table names from a drop down list in html...is there any chance can
inserting same data to different tables in oracle
inserting same data to different tables in oracle  Hi there ...! i have to insert un,pwd, to three oracle tables, like admin, user, stud, but i want pickup these table names from a drop down list in html...is there any chance can
inserting same data to different tables in oracle
inserting same data to different tables in oracle  Hi there ...! i have to insert un,pwd, to three oracle tables, like admin, user, stud, but i want pickup these table names from a drop down list in html...is there any chance can
write excel file into the oracle database
write excel file into the oracle database  dear sir, i need the jsp code that reads the excel file and stores it into the oracle database table..and also i need the code to connect oracle database? thank u in advance
JavaScript get excel file data
JavaScript get excel file data...; By the use of JavaScript we can get the excel file data as well. Here is the example which gets the data from the excel file with the cell and row index
PHP Insert Value
Insertion of data into table: We will study in this tutorial how to insert data into a table using MySQL console and phpMyAdmin. After creating table we need... default value earlier, Type: insert into student values ('name', 21, 'emp_id
java to excel connectivity - Swing AWT
java to excel connectivity  give me source code to stored the value... database on the excel.  Hi Friend, Follow these steps: 1. Open Data... = "Insert into [Excel Sheet$] (Name,Address) values ('"+v1+"', '"+v2
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
How to Upload a file directly to Oracle database using JSP or Servlets?
How to Upload a file directly to Oracle database using JSP or Servlets?  Hi I want to upload a file(csv or excel) to Oracle 10g Database using JSP...;Upload File to Oracle Database</h2> <form id="form1" enctype