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
Solved

how to compare csv file data and DB data

Posted on 2013-12-25
5
1,127 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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
web services creation SOAP vs REST 5 56
swing controls 2 16
running on tomcat not jboss eap 7.0 3 18
java mysql insert application 14 28
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 …
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
Viewers learn about the “while” loop and how to utilize it correctly in Java. Additionally, viewers begin exploring how to include conditional statements within a while loop and avoid an endless loop. Define While Loop: Basic Example: Explanatio…
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…

839 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