gudii9
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
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
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
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
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
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:
Which is it?
it inserts nicely, except one more which is header line...
some reason it is inserting only one character for username .
Which is it?
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
ASKER
I added
if (i!=0) {
int k=stat.executeUpdate("inse rt into login(username,password) value('"+username+"','"+pa ssword+"') ");
};
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
if (i!=0) {
int k=stat.executeUpdate("inse
};
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
ASKER
I truncated login table. Ran program again.my java program looks like this
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
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.POIF SFileSyste m;
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(cellStor eVector);
}
}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.e lementAt(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 ").newInst ance();
Connection con = DriverManager.getConnection("jdbc:my sql://loca lhost:3306 /test","ro ot", "root");
Statement stat=con.createStatement();
if (i!=0) {
int k=stat.executeUpdate("insert into login(username,password) value('"+username+"','"+pa ssword+"') ");
};
//int k=stat.executeUpdate("insert into login(username,password) value('"+username+"','"+pa ssword+"') ");
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;
ASKER
It is as in attachment. Both username and password defined varchar50 size
mySql6.JPG
mySql6.JPG
username=st.substring(0,1);
Could be something to do with it ;)
ASKER
i tried
>> username=st.substring(-1);
also
>> username=st.substring(1);
they did not help. please advise
>> 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??
ASKER
How do I modify the program to insert it properly. Please advise
ASKER
I modified the program like
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
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.POIF SFileSyste m;
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(cellStor eVector);
}
}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.e lementAt(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 ").newInst ance();
Connection con = DriverManager.getConnection("jdbc:my sql://loca lhost:3306 /test","ro ot", "root");
Statement stat=con.createStatement();
int k=stat.executeUpdate("insert into login(username,password) value('"+usernamefield+"', '"+passwor dfield+"') ");
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
you are right I closed the for loop after try catch and it worked fine
:)