Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

MySql insert Data issue

Posted on 2012-08-29
22
Medium Priority
?
813 Views
Last Modified: 2012-08-30
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
0
Comment
Question by:gudii9
  • 10
  • 10
  • 2
22 Comments
 
LVL 86

Expert Comment

by:CEHJ
ID: 38346415
For some reason it is not inserting all the values properly.
What do you mean by that?
0
 
LVL 7

Author Comment

by:gudii9
ID: 38346745
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
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 38346839
it inserts nicely, except one more which is header line... find a way to skip that one, or delete after insert...
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 86

Expert Comment

by:CEHJ
ID: 38346877
I've already answered at least one question relating to skipping rows when reading spreadsheets. Look at some of your answered questions
0
 
LVL 7

Author Comment

by:gudii9
ID: 38346895
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
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 38346916
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?
0
 
LVL 7

Author Comment

by:gudii9
ID: 38346948
>>>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
0
 
LVL 61

Assisted Solution

by:HainKurt
HainKurt earned 800 total points
ID: 38346975
just add an if

int k=stat.executeUpdate("insert into login(username,password) value('"+username+"','"+password+"')");

>>>>

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

Open in new window


or add if just before try

try{

>>>

if (i!=0) try {

Open in new window

0
 
LVL 86

Expert Comment

by:CEHJ
ID: 38346984
above is not my post.
Sorry - missed that. Shall look again
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 38347007
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
0
 
LVL 7

Author Comment

by:gudii9
ID: 38347033
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
0
 
LVL 7

Author Comment

by:gudii9
ID: 38347076
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
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 38347109
You've possibly defined the column too narrowly. Please post output of

show create table login;

Open in new window

0
 
LVL 7

Author Comment

by:gudii9
ID: 38347141
It is as in attachment. Both username and password defined varchar50 size
mySql6.JPG
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 38347247
 username=st.substring(0,1);

Open in new window


Could be something to do with it ;)
0
 
LVL 7

Author Comment

by:gudii9
ID: 38347384
i tried

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

>> username=st.substring(1);


they did not help. please advise
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 38347414
Why are you using substring at all if you don't want the value truncated??
0
 
LVL 7

Author Comment

by:gudii9
ID: 38350513
How do I modify the program to insert it properly. Please advise
0
 
LVL 7

Author Comment

by:gudii9
ID: 38350631
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
0
 
LVL 86

Accepted Solution

by:
CEHJ earned 1200 total points
ID: 38351185
You've got your loop in the wrong place

You should be using a PreparedStatement, preferably with addBatch in the loop. No time to code that for you now
0
 
LVL 7

Author Comment

by:gudii9
ID: 38351327
you are right I closed the for loop after try catch and it worked fine
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 38351367
:)
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question