Richards
asked on
using some values from a txt file and comparing info from DB and updating the DB
Hi
'' I only have these many points left ''
I want to
read a property file
read an input txt file
connect to the DB and load values into a collection
compare the value with the txt file and if OK update a table and if not Ok update another table.
I need to run it on Unix with Oracle DB
Say
I would like to know how to load these property file say p.prt which has the below values
JDBC=true
JDBC_URL=jdbc:oracle:peter
JDBC_DRIVER=oracle.jdbc.dr
UNAME=peter
PWD=peter
DB_UNAME=peter
DB_PWD=peter
LKL=true
LOGPATH=/logtest/log/
INPUTDIR=/input/
STDFILE =/std/
then read a file say test.txt from an input dir compare it with a std .xls file from the std dir for spaces and types
say test.txt file
John 9999 22 USA 100.00
Kim 8888 21 UK 800.00
say std.xls file is as follows
description size type
Name 30 A
id 4 A
age 3 A
country 3 A
amount 10 N
'A' for alphanumeric and 'N' for Numeric
and if the entire file pattern is not OK it should write to a log file and if OK for spaces and types then the log file should be updated as the file is OK then
read each record from the text.txt file
and
connect to the ORACLE DB using the above property file
and read some values into a collection say
'select m.id , m.age , a.country from member m , address a where m.id = a.id'
and then compare the collection DB values with the test.txt record values for id, age and country and if the test.txt has a record which matches then
update a table uptblMatch with all the values which are matching from the test.txt file
and the records which don't match I have to update in another table uptblMismatch in DB.
'' I only have these many points left ''
I want to
read a property file
read an input txt file
connect to the DB and load values into a collection
compare the value with the txt file and if OK update a table and if not Ok update another table.
I need to run it on Unix with Oracle DB
Say
I would like to know how to load these property file say p.prt which has the below values
JDBC=true
JDBC_URL=jdbc:oracle:peter
JDBC_DRIVER=oracle.jdbc.dr
UNAME=peter
PWD=peter
DB_UNAME=peter
DB_PWD=peter
LKL=true
LOGPATH=/logtest/log/
INPUTDIR=/input/
STDFILE =/std/
then read a file say test.txt from an input dir compare it with a std .xls file from the std dir for spaces and types
say test.txt file
John 9999 22 USA 100.00
Kim 8888 21 UK 800.00
say std.xls file is as follows
description size type
Name 30 A
id 4 A
age 3 A
country 3 A
amount 10 N
'A' for alphanumeric and 'N' for Numeric
and if the entire file pattern is not OK it should write to a log file and if OK for spaces and types then the log file should be updated as the file is OK then
read each record from the text.txt file
and
connect to the ORACLE DB using the above property file
and read some values into a collection say
'select m.id , m.age , a.country from member m , address a where m.id = a.id'
and then compare the collection DB values with the test.txt record values for id, age and country and if the test.txt has a record which matches then
update a table uptblMatch with all the values which are matching from the test.txt file
and the records which don't match I have to update in another table uptblMismatch in DB.
CEHJ
And your question is ..?
To read property file for easy way, you can use java.util.Properties class.
// Create Properties object
java.util.Properties prop = new java.util.Properties();
// Load property list from file into the Properties
prop.load(new FileInputStream("p.prt"));
// Get properties
String inputDir = prop.getProperty("INPUTDIR
String stdDir = prop.getProperty("STDFILE"
To read a text file
String filePath = inputDir + "test.txt";
BufferedReader br;
try
{
br = new BufferedReader( new FileReader( filePath ) );
String line;
while( ( line=br.readLine() ) != null )
{
// Process current line
}
br.close();
}
catch(IOException e)
{
try{ br.close(); }catch(Exception ex){}
e.printStackTrace();
}
To read excel file, you need to use Third Party package because it's not included in standard java package.
One of them is POI-HSSF project, you can download it for free here http://jakarta.apache.org/poi/hssf/index.html.
And here is where you can get tutorial about how to connect to Oracle, and do select, insert, update delete
http://www.javaalmanac.com/cgi-bin/search/find.pl?words=java.sql
// Create Properties object
java.util.Properties prop = new java.util.Properties();
// Load property list from file into the Properties
prop.load(new FileInputStream("p.prt"));
// Get properties
String inputDir = prop.getProperty("INPUTDIR
String stdDir = prop.getProperty("STDFILE"
To read a text file
String filePath = inputDir + "test.txt";
BufferedReader br;
try
{
br = new BufferedReader( new FileReader( filePath ) );
String line;
while( ( line=br.readLine() ) != null )
{
// Process current line
}
br.close();
}
catch(IOException e)
{
try{ br.close(); }catch(Exception ex){}
e.printStackTrace();
}
To read excel file, you need to use Third Party package because it's not included in standard java package.
One of them is POI-HSSF project, you can download it for free here http://jakarta.apache.org/poi/hssf/index.html.
And here is where you can get tutorial about how to connect to Oracle, and do select, insert, update delete
http://www.javaalmanac.com/cgi-bin/search/find.pl?words=java.sql
ASKER
Thanks !
How do we go around using the record set values stored in say result and then compare it with the entire input file data and if found and OK I need to update a table say uptblMatch and if the input file record does not match any of the result then I need to update another table say uptblMismatch
and read some values into a collection say
sqlquery = 'select m.id , m.age , a.country from member m , address a where m.id = a.id',
result = executeSQL(sqlquery);
How do we go around using the record set values stored in say result and then compare it with the entire input file data and if found and OK I need to update a table say uptblMatch and if the input file record does not match any of the result then I need to update another table say uptblMismatch
and read some values into a collection say
sqlquery = 'select m.id , m.age , a.country from member m , address a where m.id = a.id',
result = executeSQL(sqlquery);
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.