read text fiel query and generate output excel file

Hi,

I was writing program as follows


import java.io.*;
class FileReadRI
{
 public static void main(String args[])
  {
  try{
  // Open the file that is the first
  // command line parameter
  FileInputStream fstream = new FileInputStream("C:\\textfile.txt");
  // Get the object of DataInputStream
  DataInputStream in = new DataInputStream(fstream);
  BufferedReader br = new BufferedReader(new InputStreamReader(in));
  String strLine;
  //Read File Line By Line
  while ((strLine = br.readLine()) != null)   {
  // Print the content on the console
  System.out.println (strLine);
  }
  //Close the input stream
  in.close();
    }catch (Exception e){//Catch exception if any
  System.err.println("Error: " + e.getMessage());
  }
  }
}

from link

http://www.roseindia.net/java/beginners/java-read-file-line-by-line.shtml

the text file i am reading is as attached.

I need to make a query and search on database similar to following program i did with excel as below

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.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);
            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(cellStoreVector);
                  }
            }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();
              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").newInstance();
                    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();
                      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);
  }
}


my code is not separating at pipe delimitted character and sending parameters to query into prepared statement .
Any ideas, suggestions, sample code, links, source code highly appreciated. Thanks in advance
LVL 7
gudii9Asked:
Who is Participating?
 
CEHJConnect With a Mentor Commented:
That's beginning to look more like it. Can you post the exact spreadsheet you tried that failed please?
0
 
Amitkumar PSr. ConsultantCommented:
Agree with CEHJ.

Kindly post the sheet here.
0
 
gudii9Author Commented:
my text file looks like this. Please advise
textfile.txt
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
CEHJCommented:
That's the wrong file - it's not a spreadsheet file, but a text file
0
 
gudii9Author Commented:
I am good with spreadsheet excel file reading using above program posted in this question.

 Now I need to read text file as input file along with spreadsheet excel file.

Input file could be either excel or text.

If input file is excel program should go throguh excel loop and read excel data and compare againest data base table and generate mising record report and email user.

If input file is text file it should go throguh text file loop and read excel data and compare againest data base table and generate mising record report and email user.

I need to modify my program accordingly to read from text file as well along with spreadsheet excel file. Please advise on how to achieve this task. How I need to modify my program to do that.
0
 
CEHJCommented:
Your question is getting confused. There is nothing in your posted code that's even attempting to read a text file. You should clarify the interaction required between text file, sheet and excel. 'Missing', between three different entities, is beginning to sound complex ...
0
 
gudii9Author Commented:
based on this link
http://apache-poi.1045710.n5.nabble.com/read-a-txt-file-td2281404.html

looks like


POI doesn't handle text files. You could read the text file with normal
Java means (instead of reading an Excel file) and do POI handling on the
write side.
0
 
gudii9Author Commented:
I will close here and post specific question other places
0
 
CEHJCommented:
:)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.