gudii9
asked on
read text fiel query and generate output excel file
Hi,
I was writing program as follows
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
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
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.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);
}
}
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
my text file looks like this. Please advise
textfile.txt
textfile.txt
That's the wrong file - it's not a spreadsheet file, but a text file
ASKER
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.
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.
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 ...
ASKER
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.
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.
ASKER
I will close here and post specific question other places
:)
Kindly post the sheet here.