[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1039
  • Last Modified:

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
0
gudii9
Asked:
gudii9
  • 4
  • 4
1 Solution
 
CEHJCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now