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



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

View Questions and Answers by Category

Advertisements


 
Have Programming Question? Ask it here!
 
 
 


Yogi
insert excel value in to oracle data base
3 Answer(s)      4 years and a month 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


Advertisement
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 Tutorials/Questions & Answers:
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
Advertisements
Struts2 and Hibernate Fetch Data Base Value
Struts2 and Hibernate Fetch Data Base Value  Hello Sir, I am facing the problem while fetching the database value on jsp page while using... me what should i do for fetch the database value and shown on jsp page
Struts2 and Hibernate Fetch Data Base Value
Struts2 and Hibernate Fetch Data Base Value  Hello Sir, I am facing the problem while fetching the database value on jsp page while using... me what should i do for fetch the database value and shown on jsp page
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
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
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
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
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
using ajax i want to insert data into oracle database
using ajax i want to insert data into oracle database  i would like to insert/update/select/delete from jsp to oracle database using ajax? please send a code for this iam using oracl database thank you
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
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
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
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
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
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
callable statement,stored procedure for insert data and also for update data into oracle database using jsp
callable statement,stored procedure for insert data and also for update data into oracle database using jsp  iam a fresher newbie to softparsing i... procedure for inserting data into oracle database as bind variable and same
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>
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
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
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> <
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
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
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
how insert data from databse in array and compare new data to already stored value in array
how insert data from databse in array and compare new data to already stored value in array   hello all i tried to fetch data from databse and try to stored it in an array and i want to compare new value that come from database
Oracle Database - Insert System Time
Oracle Database - Insert System Time  Hi I want to insert only system time dynamically using a statement but not a prepared statement into Oracle Database please suggest me any code
insert excel sheet into mysql as a table
insert excel sheet into mysql as a table  sir, i want to import an excel sheet into mysql5.0 database as in the table format using tomcat 6.0 by jsp
oracle insert statement error - JDBC
oracle insert statement error  hi.. please help me to insert a image into oracle 11g database and please say me how can i insert image from directory like G:\imagefolder\ultrasoundimage\02us02.jpeg please help me
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">
oracle data backup
oracle data backup  how to take oracle tables data backup and use in another system
 

 

 

DMCA.com