Link to home
Start Free TrialLog in
Avatar of gudii9
gudii9Flag for United States of America

asked on

MySql insert Data issue

Hi,

I tried following example from below link to insert values into MYSQL database from EXCEL.
http://www.roseindia.net/tutorial/java/poi/insertExcelFileData.html
For some reason it is not inserting all the values properly. I am enclosing File.xls for your referenece.

Any ideas, suggestions, sample code, links, source code highly appreciated. Thanks in advance
mysqlTable.JPG
File.xls
Avatar of CEHJ
CEHJ
Flag of United Kingdom of Great Britain and Northern Ireland image

For some reason it is not inserting all the values properly.
What do you mean by that?
Avatar of gudii9

ASKER

If you see the screenshot I attached earlier,

excel has following values

username      password
aaa      a
bbb      b
ccc      c



Created table insertedas below which is not correct

username      password
p      password
a      a
b      b
c      c


Please advise where I am making mistake
it inserts nicely, except one more which is header line... find a way to skip that one, or delete after insert...
I've already answered at least one question relating to skipping rows when reading spreadsheets. Look at some of your answered questions
Avatar of gudii9

ASKER

hmm

I want it to insert values into login table like

aaa      a
bbb     b
ccc      c



some reason it is inserting only one character for username .

I changed the program top for loop to start with 1 instead of 0 still has isssue as in attachment

for (int i=1;i<dataHolder.size(); i++){
Please advise
mySql2.JPG
You're confusing me:

it inserts nicely, except one more which is header line...
some reason it is inserting only one character for username .

Which is it?
Avatar of gudii9

ASKER

>>>it inserts nicely, except one more which is header line

above is not my post.

For me it is not inserting the data properly.


my excel data is like this

username      password
ddd      d
eee      e
fff      f



inserted table looks as in the attachment.

Please let me know how shall i modify program

http://www.roseindia.net/tutorial/java/poi/insertExcelFileData.html


to insert as it is in the excel to mysql table
mySql2.JPG
SOLUTION
Avatar of HainKurt
HainKurt
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
above is not my post.
Sorry - missed that. Shall look again
I changed the program top for loop to start with 1 instead of 0 still has isssue as in attachment

Can you

a. empty the table with
truncate table login;
b. Run your code again
c. Post the result of running
d. Post the code you ran
Avatar of gudii9

ASKER

I added

if (i!=0) {
  int k=stat.executeUpdate("insert into login(username,password)   value('"+username+"','"+password+"')");
};


My excel data this time is

username      password
ww      w
xx      w
yy      w


Which is what I expected to see in mysql login table.

When i go to the table I see 2 issues

issue 1:user name first letter only displaying(instead of ww-->w, xx-->x,yy-->y displaying)

issue 2:they are inserted 2 times instead of one time.
Output is as shown in the screenshot

Please advise on how to fix these issues
mySql4.JPG
Avatar of gudii9

ASKER

I truncated login table. Ran program again.my java program looks like this



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 POIRIRaw {
      public static void main( String [] args ) {
          String fileName="C:\\File.xls";
          Vector dataHolder=read(fileName);
          saveToDatabase(dataHolder);
    }
      public static Vector read(String fileName)    {
          Vector cellVectorHolder = new Vector();
          try{
                FileInputStream myInput = new FileInputStream(fileName);
                   POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);
            HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);
            HSSFSheet mySheet = myWorkBook.getSheetAt(0);
           Iterator rowIter = mySheet.rowIterator();
           while(rowIter.hasNext()){
                HSSFRow myRow = (HSSFRow) rowIter.next();
                Iterator cellIter = myRow.cellIterator();
                Vector cellStoreVector=new Vector();
                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) {
        String username="";
            String password="";
            for (int i=1;i<dataHolder.size(); i++){
                   Vector cellStoreVector=(Vector)dataHolder.elementAt(i);
                  for (int j=1; j < cellStoreVector.size();j++){
                        HSSFCell myCell = (HSSFCell)cellStoreVector.elementAt(j);
                        String st = myCell.toString();
                         username=st.substring(0,1);
                         password=st.substring(0);
                                          }
                  try{
    Class.forName("com.mysql.jdbc.Driver").newInstance();
    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root", "root");
      Statement stat=con.createStatement();
      if (i!=0) {
              int k=stat.executeUpdate("insert into login(username,password)   value('"+username+"','"+password+"')");
            };
      //int k=stat.executeUpdate("insert into login(username,password) value('"+username+"','"+password+"')");
      System.out.println("Data is inserted");
      stat.close();
      con.close();
      }
      catch(Exception e){}
      }
      }
      }

excel looks like this

username      password
john      pass1
eric      word1


as in attachment.


My login table looks as in attachment.

password column coming good.username column is not coming correct. Please advise
mySql5.JPG
File.xls
You've possibly defined the column too narrowly. Please post output of

show create table login;

Open in new window

Avatar of gudii9

ASKER

It is as in attachment. Both username and password defined varchar50 size
mySql6.JPG
 username=st.substring(0,1);

Open in new window


Could be something to do with it ;)
Avatar of gudii9

ASKER

i tried

>> username=st.substring(-1);
also

>> username=st.substring(1);


they did not help. please advise
Why are you using substring at all if you don't want the value truncated??
Avatar of gudii9

ASKER

How do I modify the program to insert it properly. Please advise
Avatar of gudii9

ASKER

I modified the program like
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;


import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.ResourceBundle;
import java.util.Vector;
public class POIRIInsert {
      public static void main( String [] args ) {
            String fileName="C:\\File.xls";
            Vector dataHolder=read(fileName);
            saveToDatabase(dataHolder);
      }
      public static Vector read(String fileName)    {
            Vector cellVectorHolder = new Vector();
            try{
                  FileInputStream myInput = new FileInputStream(fileName);
                  POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);
                  HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);
                  HSSFSheet mySheet = myWorkBook.getSheetAt(0);
                  Iterator rowIter = mySheet.rowIterator();
                  while(rowIter.hasNext()){
                        HSSFRow myRow = (HSSFRow) rowIter.next();
                        Iterator cellIter = myRow.cellIterator();
                        Vector cellStoreVector=new Vector();
                        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) {
            String usernamefield="";
            String passwordfield="";
            List list = new ArrayList();


            for (int i=0;i<dataHolder.size(); i++){
                  Vector cellStoreVector=(Vector)dataHolder.elementAt(i);
                  //      for (int j=0; j < cellStoreVector.size();j++){
                  HSSFCell myCell = (HSSFCell)cellStoreVector.elementAt(0);
                  String stringCellValue = myCell.toString();
                  usernamefield=stringCellValue;


                  myCell = (HSSFCell)cellStoreVector.elementAt(1);
                  stringCellValue = myCell.toString();
                  passwordfield=stringCellValue;
            }

            try{
                  Class.forName("com.mysql.jdbc.Driver").newInstance();
                  Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root", "root");
                  Statement stat=con.createStatement();
                  int k=stat.executeUpdate("insert into login(username,password) value('"+usernamefield+"','"+passwordfield+"')");
                  System.out.println("Data is inserted");
                  stat.close();
                  con.close();
            }
            catch(Exception e){}

            finally{

            }




      }

}

I get rid of substrings and eliminated on for loop as it is vector of vectors(rows one vector, cells other vector)

Now it inserting only one row not all the rows. Please advise how I need to modify to insert all the rows. I am attaching the excel as well as the screenshot of the table
File.xls
mysql7.JPG
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of gudii9

ASKER

you are right I closed the for loop after try catch and it worked fine
:)