Read text file and query DB and generate missing record excel report

Posted on 2012-08-31
Last Modified: 2012-09-07

I am working on following java application based out of following link

I modified it as follows.
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.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

public class POIRIRaw4EEb {
      public static void main( String [] args ) {
            String fileName="C:\\File.xls";
            Vector dataHolder=read(fileName);
      public static Vector read(String fileName)    {
            Vector cellVectorHolder = new Vector();
                  FileInputStream myInput = new FileInputStream(fileName);
                  POIFSFileSystem myFileSystem = new POIFSFileSystem(myInput);
                  HSSFWorkbook myWorkBook = new HSSFWorkbook(myFileSystem);
                  HSSFSheet mySheet = myWorkBook.getSheetAt(0);
                  Iterator rowIter = mySheet.rowIterator();
                        HSSFRow myRow = (HSSFRow);
                        Iterator cellIter = myRow.cellIterator();
                        Vector cellStoreVector=new Vector();
                              HSSFCell myCell = (HSSFCell);
            }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.elementAt(i);
              HSSFCell myCell = (HSSFCell)cellStoreVector.elementAt(0);
              String stringCellValue = myCell.toString();
              username = stringCellValue;
              myCell = (HSSFCell)cellStoreVector.elementAt(1);
              stringCellValue = myCell.toString();
                   /* 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( {
                    // Found
                    Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test","root", "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();
                              boolean isExist = false;
                              if (rs1!=null) {
                                    while ({
                                            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();

                  //  pstmt.close();
              catch(Exception 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.
Question by:gudii9
    1 Comment
    LVL 7

    Accepted Solution

    Most of the other parts taken care except

       boolean isExist = false;
                                  if (rs1!=null) {
                                        while ({
                                                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();

    Here I need to send missinList data to a excel. Please advise on how to achieve it.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
    In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
    Viewers learn how to read error messages and identify possible mistakes that could cause hours of frustration. Coding is as much about debugging your code as it is about writing it. Define Error Message: Line Numbers: Type of Error: Break Down…
    This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now