roy_sanu
asked on
how to compare csv file data and DB data
Good Day.............
I have a merge.csv file which contain
startid, endid
10 200
20 100
and i have oracle database table called merge table which contain
startid endid
10 300
30 1000
i need to compare startid of the csv file with startid of the database column
if they are same let us say 10 it should move to history table...
Any reference or piece of code is greatly appreciated.......
Thanks
I have a merge.csv file which contain
startid, endid
10 200
20 100
and i have oracle database table called merge table which contain
startid endid
10 300
30 1000
i need to compare startid of the csv file with startid of the database column
if they are same let us say 10 it should move to history table...
Any reference or piece of code is greatly appreciated.......
Thanks
Why going the long way round?!
Just take advantage of the EXTERNAL TABLE concepts ;-)
With the help of that, you can directly query from flat files as if you were accessing a normal DB table.
Here are the official docs:
http://docs.oracle.com/cd/B19306_01/server.102/b14215/et_concepts.htm
Here are some refs & examples:
http://www.oracle-base.com/articles/9i/external-tables-9i.php
http://psoug.org/reference/externaltab.html
Just take advantage of the EXTERNAL TABLE concepts ;-)
With the help of that, you can directly query from flat files as if you were accessing a normal DB table.
Here are the official docs:
http://docs.oracle.com/cd/B19306_01/server.102/b14215/et_concepts.htm
Here are some refs & examples:
http://www.oracle-base.com/articles/9i/external-tables-9i.php
http://psoug.org/reference/externaltab.html
ASKER
I want do it java application.... I have written a sample java application ..
Before updating a record....
How i can compare excel value "10" if it exist in the database table identity.
then it should change the value..... if it donot find a similar value.. it should not update
in the database.........
let us say
src/main/resources/ merge.csv
startid, endid
10 200
20 100
src/main/resources/schema- all.sql
DROP TABLE identity IF EXISTS;
Before updating a record....
How i can compare excel value "10" if it exist in the database table identity.
then it should change the value..... if it donot find a similar value.. it should not update
in the database.........
let us say
src/main/resources/ merge.csv
startid, endid
10 200
20 100
src/main/resources/schema-
DROP TABLE identity IF EXISTS;
CREATE TABLE identity(
startid VARCHAR(10) IDENTITY NOT NULL PRIMARY KEY,
endid VARCHAR(10),
);
package hello;
public class identity {
private String endid;
private String firstid;
public identity() {
}
public Identity(String startid, String endid) {
this.startid = startid;
this.endid = endid;
}
public void setStartid(String startid) {
this.firstName = firstName;
}
public String getStartid() {
return firstid;
}
public String getLastid() {
return lastid;
}
public void setLastid(String lastid) {
this.lastid = lastid;
}
@Override
public String toString() {
return "startid: " + startid + ", lastid: " + lastid;
}
}
@Configuration
@EnableBatchProcessing
public class BatchConfiguration {
// tag::readerwriterprocessor[]
@Bean
public ItemReader<Identity> reader() {
FlatFileItemReader<Identity> reader = new FlatFileItemReader<Identity>();
reader.setResource(new ClassPathResource("merge.csv"));
reader.setLineMapper(new DefaultLineMapper<Identity>() {{
setLineTokenizer(new DelimitedLineTokenizer() {{
setNames(new String[] { "startid", "lastid" });
}});
setFieldSetMapper(new BeanWrapperFieldSetMapper<Identity>() {{
setTargetType(Identity.class);
}});
}});
return reader;
}
@Bean
public ItemWriter<Identity> writer(DataSource dataSource) {
JdbcBatchItemWriter<Identity> writer = new JdbcBatchItemWriter<Identity>();
writer.setItemSqlParameterSourceProvider(new BeanPropertyItemSqlParameterSourceProvider<Person>());
writer.setSql(""UPDATE Identiy SET endid= ? WHERE startid = ?";");
writer.setDataSource(dataSource);
return writer;
}
// end::readerwriterprocessor[]
// tag::jobstep[]
@Bean
public Job importUserJob(JobBuilderFactory jobs, Step s1) {
return jobs.get("importUserJob")
.incrementer(new RunIdIncrementer())
.flow(s1)
.end()
.build();
}
@Bean
public Step step1(StepBuilderFactory stepBuilderFactory, ItemReader<Person> reader,
ItemWriter<Person> writer, ItemProcessor<Person, Person> processor) {
return stepBuilderFactory.get("step1")
.<Person, Person> chunk(10)
.reader(reader)
.processor(processor)
.writer(writer)
.build();
}
// end::jobstep[]
@Bean
public JdbcTemplate jdbcTemplate(DataSource dataSource) {
return new JdbcTemplate(dataSource);
}
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
i did not got any solution on spring batch this from the experts
as show in this doc.
http://stackoverflow.com/questions/10839856/using-sql-loader-in-oracle-to-import-csv-file
http://bytes.com/topic/oracle/answers/949173-excel-csv-data-oracle-tables-compare-data