Solved

how to compare csv file data and DB data

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
splitOdd10 challenge 5 109
Java Timer (static) 9 45
Java DateChooser? 3 36
iterator/ListIterator approach 17 28
INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:

831 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