Welcome to the Java Programming Forums


The professional, friendly Java community. 21,500 members and growing!


The Java Programming Forums are a community of Java programmers from all around the World. Our members have a wide range of skills and they all have one thing in common: A passion to learn and code Java. We invite beginner Java programmers right through to Java professionals to post here and share your knowledge. Become a part of the community, help others, expand your knowledge of Java and enjoy talking with like minded people. Registration is quick and best of all free. We look forward to meeting you.


>> REGISTER NOW TO START POSTING


Members have full access to the forums. Advertisements are removed for registered users.

Results 1 to 2 of 2

Thread: Error by export Data from Excel File to MySql database

  1. #1
    Member
    Join Date
    Jul 2013
    Posts
    46
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Default Error by export Data from Excel File to MySql database

    Hi,
    since yesterday i tried hard to write code to export the elements from Excel file and store them in Database(Mysql)

    I used POI Library to read from Excel and then store the attributes of Excel cells in Arraylist to add it later to data base

    i read to much about this topic and saw a lot of examples but still get error

    code to create Table:
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.SQLException;
    import java.sql.Statement;
     
    /**
     *
     * @author Mirage
     */
    public class CreateTable {
    public static void main(String args[]) {
       String url = "jdbc:mysql://localhost:3306/mkyongdb";
       Connection con;
       String createTableBooks = "CREATE TABLE  movies.filme " 
              + "(CD_ID double UNSIGNED  NOT NULL," 
                +"Albumtitel VARCHAR(45) NOT NULL," 
                +"Interpret VARCHAR(25) NOT NULL,"
                + "CREATED_DATE  double NOT NULL,"
                + "Track  double NOT NULL,"
                + "Titel VARCHAR(255) NOT NULL)";
     
        Statement stmt;
       try {
              Class.forName("com.mysql.jdbc.Driver");
       } catch(java.lang.ClassNotFoundException e){
        System.err.print("ClassNotFoundException: ");
              System.err.println(e.getMessage());
       }
        try {
              con = DriverManager.getConnection(url, "root","root");
              stmt = con.createStatement();
              stmt.executeUpdate(createTableBooks);
              stmt.close();
              con.close();
        } catch(SQLException ex) {
              System.err.println("SQLException: " + 
    ex.getMessage());
        }
     }
    }

    code to read from excel and insert in database
    package Mysql;
     
    import java.io.File;
    import java.io.FileInputStream;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.util.ArrayList;
    import java.util.Iterator;
     
    import org.apache.poi.hssf.usermodel.HSSFCell;
    import org.apache.poi.hssf.usermodel.HSSFSheet;
    import org.apache.poi.hssf.usermodel.HSSFWorkbook;
    import org.apache.poi.ss.usermodel.Cell;
    import org.apache.poi.ss.usermodel.Row;
     
    public class test {
     
     
    	//static ArrayList cellArrayLisstHolder = new ArrayList();
     
    	public static void main(String[] args) throws Exception{
     
    		ArrayList dataHolder = readExcelFile(); 
    		saveToDatabase(dataHolder);
    	}
     
    		public static ArrayList readExcelFile(){
     
    		ArrayList medium = new ArrayList();
     
    	try {
     
    	    FileInputStream file = new FileInputStream(new File("d:\\Filme.xls"));
     
    	    //Get the workbook instance for XLS file 
    	    HSSFWorkbook workbook = new HSSFWorkbook(file);
     
    	    //Get first sheet from the workbook
    	    HSSFSheet sheet = workbook.getSheetAt(0);
     
    	    //Iterate through each rows from first sheet
    	    Iterator<Row> rowIterator = sheet.iterator();
    	    while(rowIterator.hasNext()) {
    	        Row row = rowIterator.next();
     
    	        //display from the third row until 5th
    	        if(row.getRowNum() > 0)
    	        {
     
    	        //For each row, iterate through each columns
    	        Iterator<Cell> cellIterator = row.cellIterator();
     
    	        ArrayList small = new ArrayList();
     
    	        while(cellIterator.hasNext()) {
     
    	            //Getting the cell contents
    	            Cell cell = cellIterator.next();
     
    	          small.add(cell);
     
    	          medium.add(small);
    		      }
    	        }
    	        }
    		    }catch (Exception e){e.printStackTrace(); 
    		    }
    		    return medium;
    		    }
     
    		private static void saveToDatabase(ArrayList dataHolder) {
    	    	   String url = "jdbc:mysql://localhost:3306/movies";
    	    	   String username = "root";
    	    	   String password = "root";
    	       Connection con;
    	       String query = "insert into filme values(?,?,?,?,?,?)";
    	       PreparedStatement ps = null;
    	       try {
    	////////////////////////make connection withthe database ///////////////////////////////
    	    	   Class.forName("com.mysql.jdbc.Driver");
    	           con = DriverManager.getConnection(url, username, password);
     
    	////////////////////////////////// Excute SQL statment:  ///////////////////////////////////////
     
    	           ps = con.prepareStatement(query);
     
    	           ArrayList cellStoreArrayList = null;
     
    	       	//For inserting into database
    	       	for (int i = 0; i < dataHolder.size(); i++) {
     
    	       	    cellStoreArrayList = (ArrayList)dataHolder.get(i);
     
    	       	       ps.setString(1,((HSSFCell)cellStoreArrayList.get(0)).toString());
    	       	       ps.setString(2,((HSSFCell)cellStoreArrayList.get(1)).toString());
    	       	       ps.setString(3,((HSSFCell)cellStoreArrayList.get(2)).toString());
    	       	       ps.setString(4,((HSSFCell)cellStoreArrayList.get(3)).toString());
    	       	       ps.setString(5,((HSSFCell)cellStoreArrayList.get(4)).toString());
    	       	       ps.setString(6,((HSSFCell)cellStoreArrayList.get(5)).toString());
     
    	       	      ps.executeUpdate();
     
    	       	       }
     
    	           ResultSet rs = ps.executeQuery(query);
    	           System.out.println(" Filme :");
    	           System.out.println(" ============== ");
     
    	/////////////////////////////handle the results: ///////////////////////////////////
     
    	               while (rs.next()) {
    	           		double s = rs.getDouble("CD_ID");
    	           		String f = rs.getString("Albumtitel");
    	           		String i = rs.getString("Interpret");
    	           		double d = rs.getDouble("CREATED_DATE");
    	           		double n = rs.getDouble("Track");
    	           		String t = rs.getString("Titel"); 
    	           		System.out.println(s + "   " + f + "             " + i + "        " + d + "      " + n + "   " + t);
    	           }
    	           ps.close();
    	           con.close();
     
    	           } catch(Exception ex) {
    	                   System.err.print("Exception: ");
    	                   System.err.println(ex.getMessage());
    	           }
    	}
    }

    The error message is :
    HTML Code:
    Exception: Index: 5, Size: 5
    and when i check out my data base it will show that it inserted the attributes more 5 times in the data base not just one and couldn't continue to insert it more the i get the error message

    I don't know why i got this error, and i don't know why insert the attributes more that once!!!

    my excel file
    HTML Code:
    CD_ID	Albumtitel	  Interpret   created Track   Titel
    --------------------------------------------------------------------------
    4711	Not That Kind	Anastacia	1999	1	Not That Kind
    4710	Not That Kind	Anastacia	1999	2	I’m Outta Love
    4712	Not That Kind	Anastacia	1999	3	Cowboys & Kisses
    4722	Wish You Her    Pink Floyd	1964	1	Shine On You Crazy Diamond
    4713	Freak of Nature	Anastacia	1999	1	Paid my Dues
    ps: the first row will not be inserted in the database so you can ignore it


  2. #2
    Administrator copeg's Avatar
    Join Date
    Oct 2009
    Location
    US
    Posts
    5,320
    Thanks
    181
    Thanked 833 Times in 772 Posts
    Blog Entries
    5

    Default Re: Error by export Data from Excel File to MySql database

    This thread has been cross posted here:

    http://www.java-forums.org/apache-poi/80513-error-export-data-excel-file-mysql-database.html

    Although cross posting is allowed, for everyone's benefit, please read:

    Java Programming Forums Cross Posting Rules

    The Problems With Cross Posting


Similar Threads

  1. exprot data from excel sheet to DataBase
    By chiranjeevireddy in forum What's Wrong With My Code?
    Replies: 0
    Last Post: May 20th, 2013, 07:58 AM
  2. Re: Import Data from Excel to Mysql Database
    By piya in forum JDBC & Databases
    Replies: 3
    Last Post: May 10th, 2013, 06:06 AM
  3. how to export data from servlet to excel file through poi lib
    By nicholas.omosa in forum Java Servlet
    Replies: 0
    Last Post: November 10th, 2012, 09:13 PM
  4. Import Data from Excel to Mysql Database
    By StarRocks in forum JDBC & Databases
    Replies: 2
    Last Post: August 29th, 2012, 11:40 PM
  5. Replies: 1
    Last Post: April 7th, 2011, 07:01 AM