Solved

how to compare csv file data and DB data

Posted on 2013-12-25
5
1,335 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 12

Expert Comment

by:Praveen Kumar Chandrashekatr
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

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Are you developing a Java application and want to create Excel Spreadsheets? You have come to the right place, this article will describe how you can create Excel Spreadsheets from a Java Application. For the purposes of this article, I will be u…
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
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…
This video teaches viewers about errors in exception handling.

691 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