Results 1 to 7 of 7
  1. #1
    vector_ever is offline Senior Member
    Join Date
    Jul 2013
    Posts
    187
    Rep Power
    2

    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:
    Java Code:
    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
    Java Code:
    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 :
    XML 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
    XML 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知 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
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,224
    Rep Power
    20

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

    In your catch blocks you need to get in the habit of using printStackTrace() instead of getMessage.
    You are losing all the details of the exception by not getting the stack trace, which will tell you exactly where in your code the error is occuring.

    When you have the full exception text and stack trace then post it here, highlighting the line in your code which causes the problem.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  3. #3
    vector_ever is offline Senior Member
    Join Date
    Jul 2013
    Posts
    187
    Rep Power
    2

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

    ok Thank you for your hint i modified it as the following

    Java Code:
    		private static void saveToDatabase(ArrayList dataHolder) {
    	    	   String url = "jdbc:mysql://localhost:3306/movies";
    	    	   String username = "root";
    	    	   String password = "root";
    	       Connection con = null;
    	       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();
    
    	       	       }
    	       } catch(Exception e) {
    	    	   e.printStackTrace();
    	       }
    	       finally{
    	    	   try{
    
    	/////////////////////////////handle the results: ///////////////////////////////////
    	          
    	       	   ResultSet rs = ps.executeQuery("SELECT * from filme");
    	           System.out.println(" Filme :");
    	           System.out.println(" ============== ");
    	           
    	               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 is as the following:
    XML Code:
    java.lang.IndexOutOfBoundsException: Index: 5, Size: 5
    	at java.util.ArrayList.rangeCheck(Unknown Source)
    	at java.util.ArrayList.get(Unknown Source)
    	at Mysql.test.saveToDatabase(test.java:103) ---> here indicate to: ps.setString(6,((HSSFCell)cellStoreArrayList.get(5)).toString());
    	at Mysql.test.main(test.java:26) ---> here indicate to: saveToDatabase(dataHolder); method
    Filme :
     ============== 
    4711.0   Not That Kind             Anastacia        1999.0      1.0   Not That Kind
    4711.0   Not That Kind             Anastacia        1999.0      1.0   Not That Kind
    4711.0   Not That Kind             Anastacia        1999.0      1.0   Not That Kind
    4711.0   Not That Kind             Anastacia        1999.0      1.0   Not That Kind
    4711.0   Not That Kind             Anastacia        1999.0      1.0   Not That Kind
    4711.0   Not That Kind             Anastacia        1999.0      1.0   Not That Kind
    4710.0   Not That Kind             Anastacia        1999.0      2.0   I知 Outta Love
    4710.0   Not That Kind             Anastacia        1999.0      2.0   I知 Outta Love
    4710.0   Not That Kind             Anastacia        1999.0      2.0   I知 Outta Love
    4710.0   Not That Kind             Anastacia        1999.0      2.0   I知 Outta Love
    4710.0   Not That Kind             Anastacia        1999.0      2.0   I知 Outta Love
    4710.0   Not That Kind             Anastacia        1999.0      2.0   I知 Outta Love
    4712.0   Not That Kind             Anastacia        1999.0      3.0   Cowboys & Kisses
    4712.0   Not That Kind             Anastacia        1999.0      3.0   Cowboys & Kisses
    4712.0   Not That Kind             Anastacia        1999.0      3.0   Cowboys & Kisses
    4712.0   Not That Kind             Anastacia        1999.0      3.0   Cowboys & Kisses
    4712.0   Not That Kind             Anastacia        1999.0      3.0   Cowboys & Kisses
    4712.0   Not That Kind             Anastacia        1999.0      3.0   Cowboys & Kisses
    4722.0   Wish You Were Her             Pink Floyd        1964.0      1.0   Shine On You Crazy Diamond
    4722.0   Wish You Were Her             Pink Floyd        1964.0      1.0   Shine On You Crazy Diamond
    4722.0   Wish You Were Her             Pink Floyd        1964.0      1.0   Shine On You Crazy Diamond
    4722.0   Wish You Were Her             Pink Floyd        1964.0      1.0   Shine On You Crazy Diamond
    4722.0   Wish You Were Her             Pink Floyd        1964.0      1.0   Shine On You Crazy Diamond
    4722.0   Wish You Were Her             Pink Floyd        1964.0      1.0   Shine On You Crazy Diamond
    4713.0   Freak of Nature             Anastacia        1999.0      1.0   Paid my Dues
    4713.0   Freak of Nature             Anastacia        1999.0      1.0   Paid my Dues
    4713.0   Freak of Nature             Anastacia        1999.0      1.0   Paid my Dues
    4713.0   Freak of Nature             Anastacia        1999.0      1.0   Paid my Dues
    4713.0   Freak of Nature             Anastacia        1999.0      1.0   Paid my Dues
    4713.0   Freak of Nature             Anastacia        1999.0      1.0   Paid my Dues
    as you can see it store it multiple times more than 5 time and filed by sixth time cause the size of the array

    Any Ideas?

  4. #4
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,224
    Rep Power
    20

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

    Well, there you go.
    Not all your cellStoreArrayLists have 6 entries in them.
    The one causing the problem only has 5.

    You'll need to print out some debug statements to see exactly what is stored in that List.
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  5. #5
    vector_ever is offline Senior Member
    Join Date
    Jul 2013
    Posts
    187
    Rep Power
    2

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

    now i got the cause of multiple entries,
    Java Code:
    while(cellIterator.hasNext()) {
                    
                    //Getting the cell contents
                    Cell cell = cellIterator.next();
                    
                  small.add(cell);
                  }
    medium.add(small);
    medium.add was by mistake in the loop but now it works normally and i get the correct result in my DB

    But still get error

    XML Code:
    java.lang.IndexOutOfBoundsException: Index: 5, Size: 5
     Filme :
     ============== 
        at java.util.ArrayList.rangeCheck(Unknown Source)
        at java.util.ArrayList.get(Unknown Source)
        at Mysql.test.saveToDatabase(test.java:103) --->    
    refer to: ps.setString(6,((HSSFCell)cellStoreArrayList.get(5)).toString());
        at Mysql.test.main(test.java:26) --->   
    refer to: saveToDatabase(dataHolder); method
    4711.0   Not That Kind             Anastacia        1999.0      1.0   Not That Kind
    4710.0   Not That Kind             Anastacia        1999.0      2.0   I知 Outta Love
    4712.0   Not That Kind             Anastacia        1999.0      3.0   Cowboys & Kisses
    4722.0   Wish You Were Her             Pink Floyd        1964.0      1.0   Shine On You Crazy Diamond
    4713.0   Freak of Nature             Anastacia        1999.0      1.0   Paid my Dues
    i do'nt know why bur it store it correctly in my DB and it contains just 6 entries

  6. #6
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    12,224
    Rep Power
    20

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

    Have you debugged it at the point that exceptionis thrown?
    So you can see what it is failing on?
    Please do not ask for code as refusal often offends.

    ** This space for rent **

  7. #7
    doWhile is offline Moderator
    Join Date
    Jul 2010
    Location
    California
    Posts
    1,641
    Rep Power
    7

Similar Threads

  1. Replies: 9
    Last Post: 04-18-2013, 01:16 PM
  2. export access database to excel file
    By Yama12 in forum JDBC
    Replies: 5
    Last Post: 09-14-2011, 12:21 PM
  3. Saving time export data to excel
    By nhoc_excel in forum JavaServer Pages (JSP) and JSTL
    Replies: 1
    Last Post: 06-23-2011, 10:54 AM
  4. Export records to Excel from MySQL
    By ansariazam in forum New To Java
    Replies: 3
    Last Post: 08-06-2008, 02:14 PM
  5. Replies: 3
    Last Post: 08-05-2008, 04:19 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •