• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1758
  • Last Modified:

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
0
roy_sanu
Asked:
roy_sanu
  • 3
1 Solution
 
Praveen Kumar ChandrashekatrDatabase Analysist Senior Commented:
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
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
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
 
roy_sanuAuthor Commented:
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
 
roy_sanuAuthor Commented:
added a validator it is working fine............

thanks
0
 
roy_sanuAuthor Commented:
i did not got any solution on spring batch  this from the experts
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

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