Solved

how to compare csv file data and DB data

Posted on 2013-12-25
5
935 Views
Last Modified: 2014-01-09
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
0
Comment
Question by:roy_sanu
  • 3
5 Comments
 
LVL 12

Expert Comment

by:praveencpk
ID: 39739257
i suggest you create and temporary table in the database and export the data from CSV file to that temp table using SQL* loader and then compare.

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
0
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39739371
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
0
 

Author Comment

by:roy_sanu
ID: 39739645
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;

CREATE TABLE identity(
    startid VARCHAR(10) IDENTITY NOT NULL PRIMARY KEY,
    endid  VARCHAR(10),
    
);

Open in new window



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;
    }

}

Open in new window


@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);
    }

}

Open in new window

0
 

Accepted Solution

by:
roy_sanu earned 0 total points
ID: 39756939
added a validator it is working fine............

thanks
0
 

Author Closing Comment

by:roy_sanu
ID: 39767534
i did not got any solution on spring batch  this from the experts
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

An old method to applying the Singleton pattern in your Java code is to check if a static instance, defined in the same class that needs to be instantiated once and only once, is null and then create a new instance; otherwise, the pre-existing insta…
By the end of 1980s, object oriented programming using languages like C++, Simula69 and ObjectPascal gained momentum. It looked like programmers finally found the perfect language. C++ successfully combined the object oriented principles of Simula w…
Viewers learn how to read error messages and identify possible mistakes that could cause hours of frustration. Coding is as much about debugging your code as it is about writing it. Define Error Message: Line Numbers: Type of Error: Break Down…
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:

760 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

24 Experts available now in Live!

Get 1:1 Help Now