gudii9
asked on
Read text file and query DB and generate missing record excel report
Hi,
I am working on following java application based out of following link
http://www.roseindia.net/tutorial/java/poi/insertExcelFileData.html
I modified it as follows.
I have query formed out of excel and checks database and if it finds the corresponding record displays to console. I am checking if rs1 is null/0, if it is null/0 then create exception loop to send that particular missing username and password information as a excel report and email that excel attachment. Here rs1 is in for loop so checks one after other record. I need to modify my program to handle text input file say Test.txt(delimitted by | as in attachemnt) as well as input file apart from excel file.
Now How to modify my program to handle text file as well along with excel.Any ideas, suggestions, sample code, links, source code highly appreciated. Thanks in advance.
I am working on following java application based out of following link
http://www.roseindia.net/tutorial/java/poi/insertExcelFileData.html
I modified it as follows.
import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Vector;
import org.apache.poi.hssf.usermodel.HSSFCe ll;
import org.apache.poi.hssf.usermodel.HSSFRo w;
import org.apache.poi.hssf.usermodel.HSSFSh eet;
import org.apache.poi.hssf.usermodel.HSSFWo rkbook;
import org.apache.poi.poifs.filesystem.POIF SFileSyste m;
public class POIRIRaw4EEb {
public static void main( String [] args ) {
String fileName="C:\\File.xls";
Vector dataHolder=read(fileName);
checkDatabase(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 checkDatabase(Vector dataHolder) {
String username="";
String password="";
List missingList = new ArrayList();
for (int i=1;i<dataHolder.size(); i++){
Vector cellStoreVector=(Vector)dataHolder.e lementAt(i );
HSSFCell myCell = (HSSFCell)cellStoreVector.elementAt( 0);
String stringCellValue = myCell.toString();
username = stringCellValue;
myCell = (HSSFCell)cellStoreVector.elementAt( 1);
stringCellValue = myCell.toString();
password=stringCellValue;
try{
/* PreparedStatement ps = con.prepareStatement("select null from login where username=? and password=?");
//(in the loop)
ps.setString(1, username);
ps.setString(2, password);
ResultSet rs = ps.executeQuery();
if(rs.next()) {
// Found
}*/
Class.forName("com.mysql.jdbc.Driver ").newInst ance();
Connection con = DriverManager.getConnection("jdbc:my sql://loca lhost:3306 /test","ro ot", "root");
// Statement stmt = con.createStatement();
// ResultSet rs1 = stmt.executeQuery("select * from login where username='"+username+"' and password='"+password+"'");
//PreparedStatement psmt=null;
PreparedStatement psmt = con.prepareStatement("select null from login where username=? and password=?");
psmt.setString(1, username);
psmt.setString(2, password);
ResultSet rs1 = psmt.executeQuery();
System.out.println("rs1-->"+rs1);
boolean isExist = false;
if (rs1!=null) {
while (rs1.next()){
isExist = true;
String rec1 = rs1.getString(1);
String rec2 = rs1.getString(2);
System.out.println("rec1 is--"+rec1+"---rec2 is---"+rec2);
}
}
if (!isExist) {
Map mapDetail = new HashMap();
mapDetail.put("username",username);
mapDetail.put("password",password);
missingList.add(mapDetail);
}
// pstmt.close();
con.close();
}
catch(Exception e){
System.out.println("e-->"+e);
}
}
System.out.println("Missing List : "+missingList);
}
}
I have query formed out of excel and checks database and if it finds the corresponding record displays to console. I am checking if rs1 is null/0, if it is null/0 then create exception loop to send that particular missing username and password information as a excel report and email that excel attachment. Here rs1 is in for loop so checks one after other record. I need to modify my program to handle text input file say Test.txt(delimitted by | as in attachemnt) as well as input file apart from excel file.
Now How to modify my program to handle text file as well along with excel.Any ideas, suggestions, sample code, links, source code highly appreciated. Thanks in advance.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.