read text fiel query and generate output excel file

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

I was writing program as follows

class FileReadRI
 public static void main(String args[])
  // 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
    }catch (Exception e){//Catch exception if any
  System.err.println("Error: " + e.getMessage());

from link

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.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);

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
Question by:gudii9
    LVL 86

    Accepted Solution

    That's beginning to look more like it. Can you post the exact spreadsheet you tried that failed please?
    LVL 20

    Expert Comment

    by:Amitkumar Panchal
    Agree with CEHJ.

    Kindly post the sheet here.
    LVL 7

    Author Comment

    my text file looks like this. Please advise
    LVL 86

    Expert Comment

    That's the wrong file - it's not a spreadsheet file, but a text file
    LVL 7

    Author Comment

    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.
    LVL 86

    Expert Comment

    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 ...
    LVL 7

    Author Comment

    based on this link

    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.
    LVL 7

    Author Comment

    I will close here and post specific question other places
    LVL 86

    Expert Comment


    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Fix45 challenge 15 67
    sumHeights2  challenge 7 52
    factorial example challenge 10 45
    countPairs challenge 7 39
    Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
    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 will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
    This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.

    746 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

    13 Experts available now in Live!

    Get 1:1 Help Now