Comparing 2 files and filtering records in one file based on a match in another file in Java

Hi Folks,

I have a requirement where in I have to compare 2 flat file , one a .dat and the other .csv. PFA are the doc samples. What I need to do is compare File A to FIle B to find any match of the field value customer and then if there is no match, we should drop the record in FIle A and output filtered FILE A as output FILE C which is .CSV. I know it has to be done in Java and I am not a pro in java, so please suggest the design.

Regards
kalyan.
FILE-A
FILE-B.csv
FILE-C.csv
kalyangkmAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gurpsbassiCommented:
is this a student assignment?
0
kalyangkmAuthor Commented:
Nope, it is not, it is a client rek. I am a middleware guy and I Use a tool which does what is required using minimal java mapping. But was looking for an all in all java solution for some obvious reasons.
0
gurpsbassiCommented:
I would read File A into a POJO e.g
e.g.
public class Sale
{
String organisation,
String salesRep,
String customer,
String price,

String getCustomer(){
....
}

Use something like OpenCSV to read it into a pojo and after creating each pojo, put it into a Map (LinkedHashMap to preserve original order) keyed by customer.

Then read then file B into another pojo e.g. or maybe just String.class since all you need is the customer no. Store these in a Set.

Then just remove entries from the first LinkedHashmap where the key exists in the second Set.

Then simply marshall to output file using openCSV.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

kalyangkmAuthor Commented:
Could you please elaborate on reading the csv using opencsv into the POJO you have defined using the Hashmap. Just an exmaple if you can please based on the below openCSV

import java.io.FileReader;
import java.util.Arrays;
import java.util.List;
 
import au.com.bytecode.opencsv.CSVReader;
 
public class ParseFullCSVExample
{
   @SuppressWarnings("resource")
   public static void main(String[] args) throws Exception
   {
      //Build reader instance
      CSVReader reader = new CSVReader(new FileReader("FILEA.csv"), ',', '"', 1);
       
      //Read all rows at once
      List<String[]> allRows = reader.readAll();
       
      //Read CSV line by line and use the string array as you want
     for(String[] row : allRows){
        System.out.println(Arrays.toString(row));
     }
   }
}

Open in new window

0
gurpsbassiCommented:
what version of java are you using?
0
gurpsbassiCommented:
Also, this is the opencsv project to use:

http://opencsv.sourceforge.net
0
kalyangkmAuthor Commented:
I am using Java version 1.6. Thanks for the opencsv link. But I was specifically looking for how to implement the Hashmap in this case. A simple example would be appreciated. I would assume we have to do the following way. But was wondering if you could suggest on how to pass the CSV files dynamically into the Hashmap.

http://www.tutorialspoint.com/java/java_linkedhashmap_class.htm
0
gurpsbassiCommented:
Look at the examples in the opencsv home page.
The use of the CsvToBean class.


CsvToBean csv = new CsvToBean();
 List list = csv.parse(strat, yourReader);

Look at the API docs for CSVToBean.
It has a method processLine(MappingStrategy<T> mapper, String[] line)

You should be able to process each line into a pojo and then put it into a map within a loop.

Sorry, for me to write all the code will take some time especially since I'm doing paid work at the moment.
0
kalyangkmAuthor Commented:
Hi gurpsbassi, Thanks for the response, something came up and I am very busy to get to this. Will be soon in a weeks time.
0
kalyangkmAuthor Commented:
Hi Gurpsbassi,

I started working on this and created a Sales class structure with getters and setters. Now my question how should I use the opencsv to read it into the POJO. Should I use the CSVReader to read the content into POJO. If so, how should I read the contents other than using the List as below, I suppose I need to use Hasmaps or something else. Could you briefly elaborate on how I can achieve this?
0
kalyangkmAuthor Commented:
Gurpbassi, are you busy? Just need to know what exactly I need to put into the hashmap?
0
gurpsbassiCommented:
Its been a while since this question began. You need to show me what code you have produce so I know where you are.
0
kalyangkmAuthor Commented:
So far all I have is the structure POJO for the Sales as below, so based on your statement
"You should be able to process each line into a pojo and then put it into a map within a loop"
 I need some clarity on what exactly I need to process into this pojo. So if I have to process all the line of the File A into POJO using CsvToBean, then should I use BufferedReader for "Reader" and after that how should I use the Hashmap? is the customer the key and the values corresponding to it the values?

"ORG";"SALES REP";"Customer";"Price"
"DS01";"DAK0010003";"1602586";"10"
"DL01";"DAK0010003";"51310317";"20"
"DM01";"DSK0010004";"51622067";"40"
0
gurpsbassiCommented:
I'll try and take a look when I get time. But it may be next week.
I'm on holiday this week.
0
kalyangkmAuthor Commented:
sure, np, I really would like to know how a simple parsing like this works so that I can solve complex problems. I need the approach for such problems and i would appreciate if you can help me with sample code as I haven't used hasmaps (though I know how that works) as I did use ArrayLists at my work most of the time.
0
kalyangkmAuthor Commented:
Hi gupsbassi, I suppose you are back from Holidays. If so could you please look into this if you can help?
0
gurpsbassiCommented:
then should I use BufferedReader for "Reader"

Yes

is the customer the key and the values corresponding to it the values?

Yes
0
kalyangkmAuthor Commented:
I have read the contents of the FILE A.csv using the inputStream/BufferReader combination and I am able to populate all the rows of the contents using ArrayList. But I am not sure how I should be reading the customer and its corresponding values into Hashmap, should i be using hasmap along with substring. Can you please provide a sample?

public class CompareCsvFiles {
	
	public static void main(String[] args){
		try {
		String RemoteFile1 = "C:/Users/xxxxxx/Desktop/CSV/Record match Java/FILE A.csv";
		File file1 = new File(RemoteFile1);
		InputStream in = new FileInputStream(file1);
		OutputStream out = new FileOutputStream(new              File("C:/Users/xxxxx/Desktop/CSV/Record match Java/FILE D.csv")); 
		InputStreamReader Reader = new InputStreamReader(in);
		BufferedReader buffer = new BufferedReader(Reader);
		
		//To Read all Rows
		ArrayList<String> allrows = new ArrayList<String>();
		String read;
		while((read = buffer.readLine())!= null){
			allrows.add(read);
		}
		
		for(String rows: allrows){
			System.out.println(rows);
		}
		
		//reading contents into hashmap
		Map<String, String> testMap = new HashMap<String, String>();
		
		
		
		
        }catch (FileNotFoundException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
        } 
        catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
        
	} 
}

Open in new window

0
gurpsbassiCommented:
I would read a line at a time and create the pojo.

use bufferedreader readLine() method that returns a string.
Split on the delimeter e.g. comma. that will give you String[].

Then you can use csvToBean.processLine(MappingStrategy<T> mapper, String[] line) as I indicated in a previous post.
This will give you a single pojo per single line.
0
kalyangkmAuthor Commented:
I have created a a POJO like this and I need some help on using this to read the contents.


public class CustomerSale {
	private String Organisation;
	private String Sales_Rep;
	private String Customer;
	private String Price;	


  public void setOrganisation(String org){
	this.Organisation = org;
  }
  
  public void setSales_Rep(String salesrep){
		this.Sales_Rep = salesrep;
	  }
  
  public void setCustomer(String cust){
		this.Customer = cust;
	  }
  
  public void setPrice(String price){
		this.Price = price;
	  }
  
  public String getOrganisation(){
	  return Organisation;
  }
  
  public String getSales_Rep(){
	  return Sales_Rep;
  }
  
  public String getCustomer(){
	  return Customer;
  }
  
  public String getPrice(){
	  return Price;
  }
}

Open in new window

0
gurpsbassiCommented:
please rename your variables and methods using convention:

private String organisation;
private String salesRep;
private String customer;
private String price;
0
gurpsbassiCommented:
follow my instructions on using the bufferedreader and the correct methods
0
kalyangkmAuthor Commented:
I am really not sure how to implement it. Once I split the lines based on delimitter ";" Is the following something helpful for me to implement. The problem I have is that like I said earlier I am very poor at using Hasmaps and that is the whole purpose starting this question, so that I can improvize with the example. I don't want you to write the code, but please explain with a sample if possible.

http://grepcode.com/file/repo1.maven.org/maven2/net.sf.opencsv/opencsv/2.1/au/com/bytecode/opencsv/bean/CsvToBean.java
0
gurpsbassiCommented:
The link you have posted is not useful. We do not need to see the source code of opencsv.


can you at least write the code up to the point where you are doing the split on ';'?
In my previous posts, I asked you to correct the variable names? Have you done it? I can't see it!
In my previous posts, I asked you to use readLine method of BufferedReader.  Have you done it? I can't see it!

I need to see what your code looks like at the moment in order to help you. do you understand?
0
kalyangkmAuthor Commented:
Thanks, I I understand and I already did it till splitting, but wasnt getting the approach to move further.

Here is the code with split using buffer.readline()

public class CompareCsvFiles {
	
	public static void main(String[] args){
		try {
		String RemoteFile1 = "C:/Users/exde2b8f/Desktop/CSV/Record match Java/FILE A.csv";
		File file1 = new File(RemoteFile1);
		InputStream in = new FileInputStream(file1);
		OutputStream out = new FileOutputStream(new File("C:/Users/exde2b8f/Desktop/CSV/Record match Java/FILE D.csv")); 
		InputStreamReader Reader = new InputStreamReader(in);
		BufferedReader buffer = new BufferedReader(Reader);
		
		//To Read all Rows
		ArrayList<String> allrows = new ArrayList<String>();
		String read;
		String[] temp;
		while((read = buffer.readLine())!= null){
			temp=read.split(";");
			
			for(int i=0;i<temp.length;i++)
				allrows.add(temp[i]);
				
		}
		
		System.out.println(allrows);
		
		//reading contents into hashmap
		Map<String, String> testMap = new HashMap<String, String>();
		
		
		
		
        }catch (FileNotFoundException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
        } 
        catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
        
	} 
}

Open in new window



Here is the updated code for the POJO

public class CustomerSale {
	private String organisation;
	private String salesRep;
	private String customer;
	private String price;	


  public void setOrganisation(String org){
	this.organisation = org;
  }
  
  public void setSales_Rep(String salesrep){
		this.salesRep = salesrep;
	  }
  
  public void setCustomer(String cust){
		this.customer = cust;
	  }
  
  public void setPrice(String price){
		this.price = price;
	  }
  
  public String getOrganisation(){
	  return organisation;
  }
  
  public String getSales_Rep(){
	  return salesRep;
  }
  
  public String getCustomer(){
	  return customer;
  }
  
  public String getPrice(){
	  return price;
  }
}

Open in new window

0
gurpsbassiCommented:
your setSales_Rep method needs to be renamed to setSalesRep
0
kalyangkmAuthor Commented:
Thanks, here it is now

public class CustomerSale {
	private String organisation;
	private String salesRep;
	private String customer;
	private String price;	


  public void setOrganisation(String org){
	this.organisation = org;
  }
  
  public void setSales_Rep(String salesrep){
		this.salesRep = salesrep;
	  }
  
  public void setCustomer(String cust){
		this.customer = cust;
	  }
  
  public void setPrice(String price){
		this.price = price;
	  }
  
  public String getOrganisation(){
	  return organisation;
  }
  
  public String getSalesRep(){
	  return salesRep;
  }
  
  public String getCustomer(){
	  return customer;
  }
  
  public String getPrice(){
	  return price;
  }
}

Open in new window

0
gurpsbassiCommented:
Thanks, here it is now
no, its still wrong.
0
gurpsbassiCommented:
Before the loop declare the map:
Map<String, CustomerSale> customerSaleMap = new LinkedHashMap<String, CustomerSale>();

After doing the split, inside the loop you need to do :

CsvToBean csv = new CsvToBean();
ColumnPositionMappingStrategy strat = new ColumnPositionMappingStrategy();
strat.setType(CustomerSale.class);
String[] columns = new String[] {"organisation", "salesRep", "customer", "price"};
strat.setColumnMapping(columns);
CustomerSale customerSale = csv.processLine(strat, temp);

//having got the customer sale object you can now store in a map
customerSaleMap.put(customerSale.getCustomer(), customerSale);


You should now have each of the lines in file A in a map.
0
kalyangkmAuthor Commented:
I think I have an issue with CustomerSale customerSale = csv.processLine(strat, temp);

The method "processLine" is not a part of "au.com.bytecode.opencsv.bean.CsvToBean", but looks to be a part of "com.bytecode.opencsv.CSVReader". But unfortunately I have to use Java 1.6 in my system due to an old middle ware system which is only compatible with 1.6. and as "com.bytecode.opencsv.CSVReader" is not compatible with java >1.6 I am forced to use "au.com.bytecode.opencsv.bean.CsvToBean".  Do I have any other option here? If not I have to do it in a different laptop. Let me know please?
0
gurpsbassiCommented:
I wasn't referring to either of those libraries.

You need to use com.opencsv.bean.CsvToBean
The library you need to use is in this dependency:
 <dependency>
     <groupId>com.opencsv</groupId>
     <artifactId>opencsv</artifactId>
     <version>3.4</version>
  </dependency>
0
kalyangkmAuthor Commented:
You are right. But still com.opencsv.bean.CsvToBean requires com.opencsv.CSVReader which is not compatible with 1.6.
0
kalyangkmAuthor Commented:
I got it, I have the ProcessLine method in the package au.com.bytecode.opencsv.bean. I will get back once I have the file A in the hashmap. I appreciate your help so far.

http://nwb.cns.iu.edu/svn/nwb/tags/dec21_before_sci2/scipolicy/plugins/libs/opencsv/src/au/com/bytecode/opencsv/bean/CsvToBean.java
0
kalyangkmAuthor Commented:
I have lines of the File A in Hashmap, but not sure how to extract the values of the corresponding keys, in my case the values of "Customer".

I am trying to check the key value pairs using the following code, but I am getting all the values as keys and "fileCompareCSV.CustomerSale@1bf73fa" as value. I definetely something wrong, but not sure what it is.

for example with the iterator I am getting the output as the following.


"DS01":
fileCompareCSV.CustomerSale@e89b94
"DAK0010003":
fileCompareCSV.CustomerSale@13e205f
....
....

      CustomerSale customerSale = (CustomerSale) csv.processLine(strat, temp);
                  customerSaleMap.put(customerSale.getOrganisation(),customerSale);
                  customerSaleMap.put(customerSale.getSalesRep(),customerSale);
                  customerSaleMap.put(customerSale.getCustomer(),customerSale);
                  customerSaleMap.put(customerSale.getPrice(),customerSale);                  
            }
            // Get a set of the entries
          Set set = customerSaleMap.entrySet();
          // Get an iterator
          Iterator i = set.iterator();
          
          while(i.hasNext()){
              Map.Entry me = (Map.Entry) i.next();
              System.out.println(me.getKey() + ":");
              System.out.println(me.getValue());
        }            
            

Here is the updated code

import java.beans.IntrospectionException;
//import java.io.FileReader;
import java.io.IOException;
//import java.util.ArrayList;
import java.util.Iterator;
import java.util.Set;
//import java.util.Arrays;
//import java.util.HashMap;
import java.util.LinkedHashMap;
//import java.util.List;
import java.util.Map;
//import java.util.Map.Entry;
import java.io.BufferedReader;
//import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;

import fileCompareCSV.CustomerSale;

import com.opencsv.bean.CsvToBean;
//import au.com.bytecode.opencsv.CSVReader;
import au.com.bytecode.opencsv.bean.ColumnPositionMappingStrategy;
//import au.com.bytecode.opencsv.bean.CsvToBean;

public class CompareCsvFiles {
	
	public static void main(String[] args){
		try {
		String RemoteFile1 = "C:/Users/exde2b8f/Desktop/CSV/Record match Java/FILE A.csv";
		File file1 = new File(RemoteFile1);
		InputStream in = new FileInputStream(file1);
		OutputStream out = new FileOutputStream(new File("C:/Users/exde2b8f/Desktop/CSV/Record match Java/FILE D.csv")); 
		InputStreamReader Reader = new InputStreamReader(in);
		BufferedReader buffer = new BufferedReader(Reader);
		

		String read;
		String[] temp;
		Map<String,CustomerSale> customerSaleMap = new LinkedHashMap<String,CustomerSale>();
		while((read = buffer.readLine())!= null){
			temp=read.split(";");
			

			CsvToBean<CustomerSale> csv = new CsvToBean<CustomerSale>();
			ColumnPositionMappingStrategy<CustomerSale> strat = new ColumnPositionMappingStrategy<CustomerSale>();
			strat.setType(CustomerSale.class);
			String[] columns = new String[]{"organisation","salesRep","customer","price"};
			strat.setColumnMapping(columns);
			CustomerSale customerSale = (CustomerSale) csv.processLine(strat, temp);
			customerSaleMap.put(customerSale.getOrganisation(),customerSale);
			customerSaleMap.put(customerSale.getSalesRep(),customerSale);
			customerSaleMap.put(customerSale.getCustomer(),customerSale);
			customerSaleMap.put(customerSale.getPrice(),customerSale);			
		}
		// Get a set of the entries
	    Set set = customerSaleMap.entrySet();
	    // Get an iterator
	    Iterator i = set.iterator();
	    
	    while(i.hasNext()){
		  Map.Entry me = (Map.Entry) i.next();
		  System.out.println(me.getKey() + ":");
		  System.out.println(me.getValue());
	  }		
		
		    

		

		
		
        }catch (FileNotFoundException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
        } 
        catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IntrospectionException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
        
	} 
}

Open in new window

0
gurpsbassiCommented:
CustomerSale customerSale = (CustomerSale) csv.processLine(strat, temp);
                  customerSaleMap.put(customerSale.getOrganisation(),customerSale);
                  customerSaleMap.put(customerSale.getSalesRep(),customerSale);
                  customerSaleMap.put(customerSale.getCustomer(),customerSale);
                  customerSaleMap.put(customerSale.getPrice(),customerSale);                  
            }

This is wrong.

The key to your map should only be customerSale.getCustomer().

The only line of code that puts something in the map should be
customerSaleMap.put(customerSale.getCustomer(), customerSale);

NOTE: this assumes that the customer numbers in file A are unique. If they are not, then we need a different solution.
0
kalyangkmAuthor Commented:
Sorry for the delay. Yes, the customer number in File A are unique. So now when I execute the Key value pairs for the map, this is what I am getting. Also, I would like to mention, so far you have been very patient with me and I understand you have already spent lot of time on this question. If you want I can alot full points to this and I can start  a new question from where this one ends and we can discuss the rest of the solution in that one. Its your call, either way is fine for me.

"Customer":
fileCompareCSV.CustomerSale@e89b94
"1602586":
fileCompareCSV.CustomerSale@13e205f
"51310317":
fileCompareCSV.CustomerSale@1bf73fa
"51622067":
fileCompareCSV.CustomerSale@5740bb
0
gurpsbassiCommented:
. If you want I can alot full points to this and I can start  a new question from where this one ends

No lets carry on until we get the solution.

You will need to skip past the first line of the file because it contains a header. before the while loop, just call buffer.readLine() to make it skip past the first line.
0
kalyangkmAuthor Commented:
Yes, it is skipping the header after putting the buffere.ReadLine();. So are these the key values that needs to be in the map?

"1602586":
fileCompareCSV.CustomerSale@e89b94
"51310317":
fileCompareCSV.CustomerSale@13e205f
"51622067":
fileCompareCSV.CustomerSale@1bf73fa
0
gurpsbassiCommented:
I'm a bit confused why your CustomerSale object is coming up as :
fileCompareCSV.CustomerSale

can you show me your CustomerSale class?
0
kalyangkmAuthor Commented:
Here it is

public class CustomerSale {
	private String organisation;
	private String salesRep;
	private String customer;
	private String price;	


  public void setOrganisation(String org){
	this.organisation = org;
  }
  
  public void setSalesRep(String salesrep){
		this.salesRep = salesrep;
	  }
  
  public void setCustomer(String cust){
		this.customer = cust;
	  }
  
  public void setPrice(String price){
		this.price = price;
	  }
  
  public String getOrganisation(){
	  return organisation;
  }
  
  public String getSalesRep(){
	  return salesRep;
  }
  
  public String getCustomer(){
	  return customer;
  }
  
  public String getPrice(){
	  return price;
  }
}

Open in new window

0
gurpsbassiCommented:
can you paste the entire file. I want to see the package declaration.
I'm confused why it is showing fileCompareCSV.CustomerSale. what is fileCompareCSV?
0
kalyangkmAuthor Commented:
I am putting everything that I have coded so far. Please also check the attachment for the  screenshot showing the project and packages

CustomerSale Class

package fileCompareCSV;

public class CustomerSale {
	private String organisation;
	private String salesRep;
	private String customer;
	private String price;	


  public void setOrganisation(String org){
	this.organisation = org;
  }
  
  public void setSalesRep(String salesrep){
		this.salesRep = salesrep;
	  }
  
  public void setCustomer(String cust){
		this.customer = cust;
	  }
  
  public void setPrice(String price){
		this.price = price;
	  }
  
  public String getOrganisation(){
	  return organisation;
  }
  
  public String getSalesRep(){
	  return salesRep;
  }
  
  public String getCustomer(){
	  return customer;
  }
  
  public String getPrice(){
	  return price;
  }
}

Open in new window



CsvToBean Class

package com.opencsv.bean;

/**
 Copyright 2007 Kyle Miller.

 Licensed under the Apache License, Version 2.0 (the "License");
 you may not use this file except in compliance with the License.
 You may obtain a copy of the License at

 http://www.apache.org/licenses/LICENSE-2.0

 Unless required by applicable law or agreed to in writing, software
 distributed under the License is distributed on an "AS IS" BASIS,
 WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 See the License for the specific language governing permissions and
 limitations under the License.
 */

import au.com.bytecode.opencsv.CSVReader;
import au.com.bytecode.opencsv.bean.MappingStrategy;

import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.beans.PropertyEditor;
import java.beans.PropertyEditorManager;
import java.io.Reader;
import java.lang.reflect.InvocationTargetException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class CsvToBean<T> {
	private Map <Class<?>, PropertyEditor> editorMap = null;
    public CsvToBean() {
    }

    public List<T> parse(MappingStrategy<T> mapper, Reader reader) {

    	return parse(mapper, new CSVReader(reader));
    }
    
    public List<T> parse(MappingStrategy<T> mapper, CSVReader csv) {
        try {
            mapper.captureHeader(csv);
            String[] line;
            List<T> list = new ArrayList<T>();
            while(null != (line = csv.readNext())) {
                T obj = processLine(mapper, line);
                list.add(obj); // TODO: (Kyle) null check object
            }
            return list;
        } catch (Exception e) {
            throw new RuntimeException("Error parsing CSV!", e);
        }
    }

    public T processLine(MappingStrategy<T> mapper, String[] line) throws IllegalAccessException, InvocationTargetException, InstantiationException, IntrospectionException {
    	T bean = mapper.createBean();
        for(int col = 0; col < line.length; col++) {
            String value = line[col];
            PropertyDescriptor prop = mapper.findDescriptor(col);
            if (null != prop) {
                Object obj = convertValue(value, prop);
                prop.getWriteMethod().invoke(bean, obj);
            }
        }
        return bean;
    }

    protected Object convertValue(String value, PropertyDescriptor prop) throws InstantiationException, IllegalAccessException {
        PropertyEditor editor = getPropertyEditor(prop);
        Object obj = value;
        if (null != editor) {
            editor.setAsText(value.trim());
            obj = editor.getValue();
        }
        return obj;
    }
    
    private PropertyEditor getPropertyEditorValue(Class<?> cls)
    {
       if (editorMap == null)
       {
          editorMap = new HashMap<Class<?>, PropertyEditor>();
       }
       
       PropertyEditor editor = editorMap.get(cls);
       
       if (editor == null)
       {
          editor = PropertyEditorManager.findEditor(cls);
          addEditorToMap(cls, editor);
       }
       
       return editor;
    }

   private void addEditorToMap(Class<?> cls, PropertyEditor editor)
   {
      if (editor != null)
       {   
          editorMap.put(cls, editor);
       }
   }


    /*
     * Attempt to find custom property editor on descriptor first, else try the propery editor manager.
     */
    protected PropertyEditor getPropertyEditor(PropertyDescriptor desc) throws InstantiationException, IllegalAccessException {
        Class<?> cls = desc.getPropertyEditorClass();
        if (null != cls) return (PropertyEditor) cls.newInstance();
        return getPropertyEditorValue(desc.getPropertyType());
    }

}

Open in new window



CompareCsvFiles class

package fileCompareCSV;

import java.beans.IntrospectionException;
import java.io.IOException;
import java.util.Iterator;
import java.util.Set;
import java.util.LinkedHashMap;
import java.util.Map;
import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;
import fileCompareCSV.CustomerSale;
import com.opencsv.bean.CsvToBean;
import au.com.bytecode.opencsv.bean.ColumnPositionMappingStrategy;


public class CompareCsvFiles {
	
	public static void main(String[] args){
		try {
		String RemoteFile1 = "C:/Users/exde2b8f/Desktop/CSV/Record match Java/FILE A.csv";
		File file1 = new File(RemoteFile1);
		InputStream in = new FileInputStream(file1);
		OutputStream out = new FileOutputStream(new File("C:/Users/exde2b8f/Desktop/CSV/Record match Java/FILE D.csv")); 
		InputStreamReader Reader = new InputStreamReader(in);
		BufferedReader buffer = new BufferedReader(Reader);
		
		//To Read all Rows
	//	ArrayList<String> allrows = new ArrayList<String>();
		String read;
		String[] temp;
		Map<String,CustomerSale> customerSaleMap = new LinkedHashMap<String,CustomerSale>();
		buffer.readLine();
		while((read = buffer.readLine())!= null){
			temp=read.split(";");
			
			//for(int i=0;i<temp.length;i++){
			//	allrows.add(temp[i]);
			CsvToBean<CustomerSale> csv = new CsvToBean<CustomerSale>();
			ColumnPositionMappingStrategy<CustomerSale> strat = new ColumnPositionMappingStrategy<CustomerSale>();
			strat.setType(CustomerSale.class);
			String[] columns = new String[]{"organisation","salesRep","customer","price"};
			strat.setColumnMapping(columns);
			CustomerSale customerSale = (CustomerSale) csv.processLine(strat, temp);
			
	
			customerSaleMap.put(customerSale.getCustomer(),customerSale);
	
		}
		// Get a set of the entries
	    Set set = customerSaleMap.entrySet();
	    // Get an iterator
	    Iterator i = set.iterator();
	    
	    while(i.hasNext()){
		  Map.Entry me = (Map.Entry) i.next();
		  System.out.println(me.getKey() + ":");
		  System.out.println(me.getValue());
	  }		
		
		    
		//System.out.println(allrows);
		

		
		
        }catch (FileNotFoundException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
        } 
        catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IntrospectionException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
        
	} 
}

Open in new window

Capture.PNG
0
gurpsbassiCommented:
ok i understand. You have called your package fileCompareCSV.
The is not a suitable name.

packages are normally of the form com.<mycompany>.filecompare
e.g. com.ibm.filecompare
       com.amazon.filecompare

and there always lower case.


second thing: Why have you posted CsvToBean? Surely you're not making use of the source code of this class are you? you should be using the jar file.
0
kalyangkmAuthor Commented:
Thanks for the catch. I changed the Package name now. But still its having the same issue. Also I didnt get the jar file, I only got the source code which I just picked it up and modified very slightly to make the method "List<T> parse(MappingStrategy<T> mapper, CSVReader csv)" public from private. Does it matter if I make a .jar out of it and then import it into library instead of calling this way?

"1602586":
com.csv.filecompare.CustomerSale@32c41a
"51310317":
com.csv.filecompare.CustomerSale@e89b94
"51622067":
com.csv.filecompare.CustomerSale@13e205
0
gurpsbassiCommented:
It does matter.
You are using a third party library but you,ve modified it. But if that's what you want to do then fine. Extremley bad practice.

Anyway at this point you should be at the point where you have read your fileA into the map.

What's next?
0
kalyangkmAuthor Commented:
So, is this output ok for the map?

"1602586":
com.csv.filecompare.CustomerSale@32c41a
"51310317":
com.csv.filecompare.CustomerSale@e89b94
"51622067":
com.csv.filecompare.CustomerSale@13e205
0
gurpsbassiCommented:
yes its correct. We don't need to print the output anyway.
i believe you need to now read in file B
0
kalyangkmAuthor Commented:
Its done. Now I have both the files in 2 separate Maps.

package com.csv.filecompare;

import java.beans.IntrospectionException;
import java.io.IOException;
import java.util.Iterator;
import java.util.Set;
import java.util.LinkedHashMap;
import java.util.Map;
import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.lang.reflect.InvocationTargetException;
import com.csv.filecompare.CustomerSale;
import com.csv.filecompare.CustomerID;
import com.opencsv.bean.CsvToBean;
import au.com.bytecode.opencsv.bean.ColumnPositionMappingStrategy;


public class CompareCsvFiles {
	
	public static void main(String[] args){
		try {
		String RemoteFile1 = "C:/Users/exde2b8f/Desktop/CSV/Record match Java/FILE A.csv";
		String RemoteFile2 = "C:/Users/exde2b8f/Desktop/CSV/Record match Java/FILE B.csv";
		File file1 = new File(RemoteFile1);
		File file2 = new File(RemoteFile2);
		InputStream in = new FileInputStream(file1);
		InputStream in2 = new FileInputStream(file2);
		OutputStream out = new FileOutputStream(new File("C:/Users/exde2b8f/Desktop/CSV/Record match Java/FILE D.csv")); 
		InputStreamReader Reader = new InputStreamReader(in);
		InputStreamReader Reader2 = new InputStreamReader(in2);
		BufferedReader buffer = new BufferedReader(Reader);
		BufferedReader buffer2 = new BufferedReader(Reader2);
		
        //reading File A contents into Hashmap
		String read;
		String[] temp;
		Map<String,CustomerSale> customerSaleMap = new LinkedHashMap<String,CustomerSale>();
		buffer.readLine();
		while((read = buffer.readLine())!= null){
			temp=read.split(";");
			
			//for(int i=0;i<temp.length;i++){
			//	allrows.add(temp[i]);
			CsvToBean<CustomerSale> csv = new CsvToBean<CustomerSale>();
			ColumnPositionMappingStrategy<CustomerSale> strat = new ColumnPositionMappingStrategy<CustomerSale>();
			strat.setType(CustomerSale.class);
			String[] columns = new String[]{"organisation","salesRep","customer","price"};
			strat.setColumnMapping(columns);
			CustomerSale customerSale = (CustomerSale) csv.processLine(strat, temp);
			
	
			customerSaleMap.put(customerSale.getCustomer(),customerSale);
	
		}
		
		
		// Get a set of the entries
	    Set set = customerSaleMap.entrySet();
	    // Get an iterator
	    Iterator i = set.iterator();
	    
	    while(i.hasNext()){
		  Map.Entry me = (Map.Entry) i.next();
		  System.out.println(me.getKey() + ":");
		  System.out.println(me.getValue());
	  }		
		
		 //reading File B contents into Hashmap
		String read2;
		String[] temp2;
		Map<String,CustomerID> customerIDMap = new LinkedHashMap<String,CustomerID>();
		buffer2.readLine();
		while((read2 = buffer2.readLine())!= null){
			temp2=read2.split(",");
			
			//for(int i=0;i<temp.length;i++){
			//	allrows.add(temp[i]);
			CsvToBean<CustomerID> csv2 = new CsvToBean<CustomerID>();
			ColumnPositionMappingStrategy<CustomerID> strat2 = new ColumnPositionMappingStrategy<CustomerID>();
			strat2.setType(CustomerID.class);
			String[] columns2 = new String[]{"id","customerNumber"};
			strat2.setColumnMapping(columns2);
			CustomerID customerID = (CustomerID) csv2.processLine(strat2,temp2);
			
	
			customerIDMap.put(customerID.getCustomerNumber(),customerID);	

		}
		
          

		// Get a set of the entries
		    Set set2 = customerIDMap.entrySet();
		    // Get an iterator
		    Iterator i2 = set2.iterator();
		    
		    while(i2.hasNext()){
			  Map.Entry me2 = (Map.Entry) i2.next();
			  System.out.println(me2.getKey() + ":");
			  System.out.println(me2.getValue());
		  }		
		
		
        }catch (FileNotFoundException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
        } 
        catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IntrospectionException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
        
	} 
}

Open in new window

0
gurpsbassiCommented:
Good work.
What do we have to do next? I've forgotten .
0
kalyangkmAuthor Commented:
We need to filter File A records based on File B customer numbers, where Customer number is the key with unique values in File A.

So our file File A is

"ORG";"SALES REP";"Customer";"Price"
"DS01";"DAK0010003";"1602586";"10"
"DL01";"DAK0010003";"51310317";"20"
"DM01";"DSK0010004";"51622067";"40"


File B is

Id,Customer
1234345555,3466777
1222344444,51310317
2345566666,51622067

And as File B has customer Numbers 51310317 and 51622067 which match the numbers in File A, we need to get the output as

"ORG";"SALES REP";"Customer";"Price"
"DL01";"DAK0010003";"51310317";"20"
"DM01";"DSK0010004";"51622067";"40"
0
gurpsbassiCommented:
I would loop through the first map (fileA) and check whether the same key exists in the second map (fileB)

List<CustomerSale> filteredRecords = new ArrayList<CustomerSale>();
for (Map.EntryMap<String, CustomerSale> entry : customerSaleMap.entrySet())
{
  if(customerIDMap.containsKey(entry.getKey())){
   
      //store this CustomerSale object into a new list.
     filteredRecords.add(entry.getValue());
  }
}


so now you are left with a list of records from fileA that have been filtered by fileB.

We need to do something like:

ColumnPositionMappingStrategy<CustomerSale> strat = new ColumnPositionMappingStrategy<CustomerSale>();
strat.setType(CustomerSale.class);
String[] columns = new String[] {"organisation", "salesRep", "customer", "price"};
strat.setColumnMapping(columns);

StringWriter sw = new StringWriter();
bean.write(strat, sw, testData);

System.out.println(sw.getBuffer().toString());

This should print the resulting data to standard ouput.

If you want to write the results to a file then we can do that. But first verify whether the correct data is being printed to standard output. In normal circumstances I woul write a Junit test for this but this may be a new concept for you to grasp since java isn't your first language.
0
kalyangkmAuthor Commented:
I am bit confused with the following piece of code, what exactly we are trying to do here?

StringWriter sw = new StringWriter();
bean.write(strat, sw, testData);
0
gurpsbassiCommented:
the write() method of the BeanToCsv class takes a Writer. I have suggested you use a StringWriter to test it. It basically buffers the output to a string.

When we come to writing it to a file, we will use something else e.g. FileWriter.
0
kalyangkmAuthor Commented:
I dont have BeanToCsv, so is it another jar that I need to download. If so can you please provide the jar file of the Bean as I dont want to do the same mistake of finding the source code just like I did it for CsvToBean.
0
gurpsbassiCommented:
its all part of opencsv same jar
0
kalyangkmAuthor Commented:
I think I got it in the same com.opencsv, I just added an import statement

coming back to the statement what is the testData, i suppose you meant the customer number values

	//Comparing the Customer Number in the 2 files.
		List<CustomerSale> filteredRecords = new ArrayList<CustomerSale>();
		for(Map.Entry<String,CustomerSale> entry : customerSaleMap.entrySet())
		{
			if(customerIDMap.containsKey(entry.getKey())){
				filteredRecords.add(entry.getValue());
			}
		}
		
		BeanToCsv<CustomerID> beantocsv = new BeanToCsv<CustomerID>();
		ColumnPositionMappingStrategy<CustomerSale> stratout = new ColumnPositionMappingStrategy<CustomerSale>();
		stratout.setType(CustomerSale.class);
		String[] columnsout = new String[] {"organisation", "salesRep", "customer", "price"}; 
		stratout.setColumnMapping(columnsout);  
		
		StringWriter sw = new StringWriter();
	    beantocsv.write(stratout,sw,??);

Open in new window

0
gurpsbassiCommented:
filteredRecords
0
kalyangkmAuthor Commented:
Thanks, Actually I tried that, but I am getting the following error as shown in the screenshot in the attachment. Please check.
Capture.PNG
0
gurpsbassiCommented:
You have got: BeanToCsv<CustomerID> beantocsv = new BeanToCsv<CustomerID>();
this is wrong

it should be BeanToCsv<CustomerSale> beantocsv = new BeanToCsv<CustomerSale>();
0
kalyangkmAuthor Commented:
After changing  the Bean, I am having the same kind of error with CustomerID now. Please check the screenshot.
Capture.PNG
0
gurpsbassiCommented:
you havent done what I asked.
your screenshot still shows CustomerID being used in BeanToCsv
0
kalyangkmAuthor Commented:
Sorry, I have sent you the wrong screenshot, Please check now.
Capture.PNG
0
gurpsbassiCommented:
I need to see the beantocsv class you have. Since you are using a copy of the source code
0
kalyangkmAuthor Commented:
Here it is

package com.opencsv.bean;

/**
 Copyright 2007 Kyle Miller.

 Licensed under the Apache License, Version 2.0 (the "License");
 you may not use this file except in compliance with the License.
 You may obtain a copy of the License at

 http://www.apache.org/licenses/LICENSE-2.0

 Unless required by applicable law or agreed to in writing, software
 distributed under the License is distributed on an "AS IS" BASIS,
 WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 See the License for the specific language governing permissions and
 limitations under the License.
 */

import au.com.bytecode.opencsv.CSVReader;
import au.com.bytecode.opencsv.bean.MappingStrategy;

import java.beans.IntrospectionException;
import java.beans.PropertyDescriptor;
import java.beans.PropertyEditor;
import java.beans.PropertyEditorManager;
import java.io.Reader;
import java.lang.reflect.InvocationTargetException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class CsvToBean<T> {
	private Map <Class<?>, PropertyEditor> editorMap = null;
    public CsvToBean() {
    }

    public List<T> parse(MappingStrategy<T> mapper, Reader reader) {

    	return parse(mapper, new CSVReader(reader));
    }
    
    public List<T> parse(MappingStrategy<T> mapper, CSVReader csv) {
        try {
            mapper.captureHeader(csv);
            String[] line;
            List<T> list = new ArrayList<T>();
            while(null != (line = csv.readNext())) {
                T obj = processLine(mapper, line);
                list.add(obj); // TODO: (Kyle) null check object
            }
            return list;
        } catch (Exception e) {
            throw new RuntimeException("Error parsing CSV!", e);
        }
    }

    public T processLine(MappingStrategy<T> mapper, String[] line) throws IllegalAccessException, InvocationTargetException, InstantiationException, IntrospectionException {
    	T bean = mapper.createBean();
        for(int col = 0; col < line.length; col++) {
            String value = line[col];
            PropertyDescriptor prop = mapper.findDescriptor(col);
            if (null != prop) {
                Object obj = convertValue(value, prop);
                prop.getWriteMethod().invoke(bean, obj);
            }
        }
        return bean;
    }

    protected Object convertValue(String value, PropertyDescriptor prop) throws InstantiationException, IllegalAccessException {
        PropertyEditor editor = getPropertyEditor(prop);
        Object obj = value;
        if (null != editor) {
            editor.setAsText(value.trim());
            obj = editor.getValue();
        }
        return obj;
    }
    
    private PropertyEditor getPropertyEditorValue(Class<?> cls)
    {
       if (editorMap == null)
       {
          editorMap = new HashMap<Class<?>, PropertyEditor>();
       }
       
       PropertyEditor editor = editorMap.get(cls);
       
       if (editor == null)
       {
          editor = PropertyEditorManager.findEditor(cls);
          addEditorToMap(cls, editor);
       }
       
       return editor;
    }

   private void addEditorToMap(Class<?> cls, PropertyEditor editor)
   {
      if (editor != null)
       {   
          editorMap.put(cls, editor);
       }
   }


    /*
     * Attempt to find custom property editor on descriptor first, else try the propery editor manager.
     */
    protected PropertyEditor getPropertyEditor(PropertyDescriptor desc) throws InstantiationException, IllegalAccessException {
        Class<?> cls = desc.getPropertyEditorClass();
        if (null != cls) return (PropertyEditor) cls.newInstance();
        return getPropertyEditorValue(desc.getPropertyType());
    }

}

Open in new window

0
gurpsbassiCommented:
thats not the class I asked for
0
kalyangkmAuthor Commented:
I thought you were asking for CsvToBean. If that is the case then BeanToCsv is from standard .jar file I downloaded and that is opencsv-3.6.jar.
0
gurpsbassiCommented:
you have totally lost me.

You are using CsvToBean as a source file.
You are using BeanToCsv as a standard jar file (even though you said it doesn't work with java 1.6).

I am very confused.
0
kalyangkmAuthor Commented:
I see the problem now. I had an issue with "processLine" method earlier when you first asked me to use the method(at that time the opencsv 3.6 wasnt recognizing the method) because of which I went for this custom code for CsvToBean. Now I removed the custom CsvToBean code completely and I am using the standard opencsv 3.6 and the following attachment error is the only error I am getting and its occuring at 2 places where it is used int eh code. You see what I am trying to say. I know I really confused you but the reason why I had to go for the custom bean was this reason. Somehow the standard opencsv 3,6. Please find the attachment.
Capture.PNG
0
kalyangkmAuthor Commented:
To be more precise this is what I was referring as shown in the screenshot. the CsvToBean doesnt have the processline method with opencsv 3.6
Capture.PNG
0
gurpsbassiCommented:
but wait i thought you said 3.6 didn't work on java 1.6

Ill have to come back to this. signing out now.
0
kalyangkmAuthor Commented:
I actually had one more thread opened sometime back and in that I was informed that 3.6 would be an issue as I have 1.6. You were also part of it.

http://www.experts-exchange.com/questions/28903559/looking-for-CSVReader-package.html


That was actually the reason why I thought 3.6 is not recognoizing processline and hence told you 3.6 didnt work with java 1.6. But looks like I am wrong and it seems 3.6 is working with the current program. But the only issue is "processline" method.
0
kalyangkmAuthor Commented:
Hi gurpsbassi,

You still busy?
0
gurpsbassiCommented:
http://opencsv.sourceforge.net/apidocs/com/opencsv/bean/CsvToBean.html#processLine(com.opencsv.bean.MappingStrategy,%20java.lang.String[])

as you can see csvtobean DOES have the processLine method.
Whatever version you are using does not.
0
kalyangkmAuthor Commented:
Yes I do agree and like you said my version doesnt have it. thats why i had the whole confusion. Is there any way you can get me the version which supports? I have the latest and for some strange reason i don't have it.
0
gurpsbassiCommented:
take a step back.
you claim to have the latest. tell me where you got it from.
0
kalyangkmAuthor Commented:
0
gurpsbassiCommented:
not sure whats going on to be honest.
0
kalyangkmAuthor Commented:
Actually that is the reason why i had to go for that custom code by Kyle Miller. Its a mess now.
0
gurpsbassiCommented:
This is not a good library to use. I would suggest using something else or doing something bespoke.
depends how much time you have.
0
kalyangkmAuthor Commented:
I have time. Please suggest a new approach. I can alot the points as you have patiently spent lot of time on this and we can probably start a new thread. Let me know.
0
gurpsbassiCommented:
I don't care about the points. I'll have a look to see if I can find a better library. But may be a few days, I'm busy with paid work at the moment.
0
kalyangkmAuthor Commented:
Sure, I can wait. Thank You.
0
kalyangkmAuthor Commented:
Hi gurpbassi,

Its been a while; So do you have time to go through this anytime?
0
gurpsbassiCommented:
gosh. Its been so long.
I've completely lost context.
0
kalyangkmAuthor Commented:
Let me give you a very brief overview. My requirement which is at the very beginning is to compare 2 files (which have a key field customer) and generate the output with records which are matching with the customer# in both the files. We tried using the opencsv based on the following blog.
http://opencsv.sourceforge.net/

But it the version of CsvToBean that I downloaded from the following doesn't have the Process the processLine method.

https://sourceforge.net/projects/opencsv/

Because of which I had to customize the CsvToBean which has become a mess when I started using in my program and I was having issues.

So eventually you have decided to try a new library or go with bespoke but we have to discontinue this until you were free and I got busy later so thats why it took so long.
0
gurpsbassiCommented:
Have a look at jacksoncsv.
0
kalyangkmAuthor Commented:
Should I use the following CSV

http://www.java2s.com/Code/Jar/j/Downloadjacksondataformatavro215sourcesjar.htm

or the following which has lot many classes.

http://www.java2s.com/Code/Jar/j/Downloadjacksonall190jar.htm

Also I was going through the following blog it was sort difficult for me to comprehend and visualize it for my requirement.

http://www.cowtowncoder.com/blog/archives/2012/03/entry_468.html

I was looking at the following class and it looks like the POJO that I created as of now is of no use, if I am not wrong or it atleast it needs modification? Please find my 2 POJOs below for the 2 file structures which I have to compare. So, Inorder to use the POJO in the following class I need to have a Schema, Is it?

MappingIterator<Entry> it = mapper
    .reader(User.class)
    .with(schema)
    .readValues(new File("Users.csv"());


POJO 1: CustomerID class

public class CustomerID {
	private String id;
	private String customerNumber;
	
  public void setId(String id){
	this.id = id;
  }
  
  public void setCustomerNumber(String cust){
		this.customerNumber = cust;
	  }
  
  public String getId(){
	  return id;
  }
  
  public String getCustomerNumber(){
	  return customerNumber;
  }
  
}

Open in new window


POJO 2 : CustomerSale

public class CustomerSale {
	private String organisation;
	private String salesRep;
	private String customer;
	private String price;	


  public void setOrganisation(String org){
	this.organisation = org;
  }
  
  public void setSalesRep(String salesrep){
		this.salesRep = salesrep;
	  }
  
  public void setCustomer(String cust){
		this.customer = cust;
	  }
  
  public void setPrice(String price){
		this.price = price;
	  }
  
  public String getOrganisation(){
	  return organisation;
  }
  
  public String getSalesRep(){
	  return salesRep;
  }
  
  public String getCustomer(){
	  return customer;
  }
  
  public String getPrice(){
	  return price;
  }
}

Open in new window

0
gurpsbassiCommented:
1) go to http://mvnrepository.com/artifact/com.fasterxml.jackson.dataformat/jackson-dataformat-csv/2.7.3 and click on the Download Bundle link.

2) once downloaded. get it on your classpath and try some examples. Make some small test programs. This is the only way you will learn.
0
kalyangkmAuthor Commented:
Sure thanks.

I tried to extract the .jar in eclipse and I only see .class files and no .java files.
0
gurpsbassiCommented:
You should not need to see the java files.
Put the jar on your classpath and thats it.

In order to learn how to use the API - you need to read the documentation - not the source code!!!!
0
kalyangkmAuthor Commented:
I know the jar doesn't contain the .java files, and that's what and how I was using it opencsv jar. But I thought you said it had some samples, I didn't realize you mentioned to read through the whole documentation and try the samples. I know that's the best way to learn but that would take me a very long time as I have others jobs to do at my clients place.
      I can do this, but do you know for sure that I can achieve what I am looking to achieve with this JacksonCSV?
0
gurpsbassiCommented:
Yes I have used jackson csv before to produce a csv file I assume it is just as good when reading a csv file.
0
kalyangkmAuthor Commented:
Thanks I will try.
0
kalyangkmAuthor Commented:
Hi,

In my first attempt in trying an example I am trying to implement this AnySetterTest.java. But I am not finding this class ModuleTestBase. This class is used in pretty much all the examples. Is there any other .jar file that needs to be added to the library?

import java.util.*;

	import com.fasterxml.jackson.annotation.JsonAnySetter;
	import com.fasterxml.jackson.dataformat.csv.*;

	public class AnySetterTest extends ModuleTestBase
	{
	    static class Entry {
	        Map<String,Object> stuff = new LinkedHashMap<String,Object>();
	        
	        public int age;
	        public String name;

	        @JsonAnySetter
	        public void set(String key, Object value) {
	            // for secondary test, where name remains empty:
	            if (key.isEmpty()) {
	                key = String.valueOf(stuff.size());
	            }
	            stuff.put(key, value);
	        }
	    }

	    /*
	    /**********************************************************************
	    /* Test methods
	    /**********************************************************************
	     */

	    public void testSimpleHeader() throws Exception
	    {
	        CsvMapper mapper = mapperForCsv();
	        CsvSchema schema = CsvSchema.emptySchema().withHeader();
	        Entry entry = mapper.readerFor(Entry.class).with(schema).readValue(
	                "name,age,gender,extra\nBarbara,35,F,1246\n");
	        assertEquals(35, entry.age);
	        assertEquals("F", entry.stuff.get("gender"));
	        assertEquals("1246", entry.stuff.get("extra"));
	        assertEquals(2, entry.stuff.size());
	    }

	    // [dataformat-csv@109]: allow "any-setter-like"
	    public void testWithMapToAny() throws Exception
	    {
	        CsvMapper mapper = mapperForCsv();
	        CsvSchema schema = CsvSchema.emptySchema().withHeader()
	                .withAnyPropertyName("");
	        Entry entry = mapper.readerFor(Entry.class).with(schema)
	                .readValue("name,age\nJoe,28,first,second\n");
	        assertEquals("Joe", entry.name);
	        assertEquals(28, entry.age);
	        assertEquals("first", entry.stuff.get("0"));
	        assertEquals("second", entry.stuff.get("1"));
	        assertEquals(2, entry.stuff.size());
	    }
	}
	
	
	
}

Open in new window

0
gurpsbassiCommented:
Where did u get this example from?
0
gurpsbassiCommented:
AnySetterTest is a test class for the project. I don't understand what you are trying to do!!


Read the home page. It has all the examples you need.
https://github.com/FasterXML/jackson-dataformat-csv
0
kalyangkmAuthor Commented:
I am using manually created schema for POJO as

CsvSchema schema = CsvSchema.builder()
	        .addColumn("Organisation")
	        .addColumn("salesRep")
	        .addColumn("customer")
	        .addColumn("price")
	        .build();

Open in new window



But what about this Schema from POJO using @JasonPropertyOrder ? I am not able to understand the following representation.

  public enum Gender { MALE, FEMALE };
  // Note: MUST ensure a stable ordering; either alphabetic, or explicit
  // (JDK does not guarantee order of properties)
  @JsonPropertyOrder({ "name", "gender", "verified", "image" })
   public class User {
   public Gender gender;
   public String name;
   public boolean verified;
   public byte[] image;
  }
  // note: we could use std ObjectMapper; but CsvMapper has convenience methods
  CsvMapper mapper = new CsvMapper();
  CsvSchema schema = mapper.schemaFor(User.class);

Open in new window

0
gurpsbassiCommented:
are you familiar with json?
0
kalyangkmAuthor Commented:
yes, the format
0
gurpsbassiCommented:
using jackson-csv is going to require you to know about Json annotations and what they mean.
If you are comfortable with that then we can continue or we need another plan.
0
kalyangkmAuthor Commented:
My level of comfort is only recognizing the format and knowing the items in it as I worked on JSON to XML and XML to JSON conversions using standard REST adapters. That' it. So may be we need a separate plan. But was wondering why is it so tricky. Is it the syntax that I should be worried about?
0
gurpsbassiCommented:
Ok, In that case I don't want to confuse you with using such frameworks.
Better to go with a custom solution.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gurpsbassiCommented:
Lets start by reading File A.
Read the records into the pojo. Write the code for then we will assess.
0
kalyangkmAuthor Commented:
Yes, I already have the POJOs for File A and File B.

POJO 1: CustomerID class

public class CustomerID {
	private String id;
	private String customerNumber;
	
  public void setId(String id){
	this.id = id;
  }
  
  public void setCustomerNumber(String cust){
		this.customerNumber = cust;
	  }
  
  public String getId(){
	  return id;
  }
  
  public String getCustomerNumber(){
	  return customerNumber;
  }
  
}

Open in new window


POJO 2: CustomerSale

public class CustomerSale {
	private String organisation;
	private String salesRep;
	private String customer;
	private String price;	


  public void setOrganisation(String org){
	this.organisation = org;
  }
  
  public void setSalesRep(String salesrep){
		this.salesRep = salesrep;
	  }
  
  public void setCustomer(String cust){
		this.customer = cust;
	  }
  
  public void setPrice(String price){
		this.price = price;
	  }
  
  public String getOrganisation(){
	  return organisation;
  }
  
  public String getSalesRep(){
	  return salesRep;
  }
  
  public String getCustomer(){
	  return customer;
  }
  
  public String getPrice(){
	  return price;
  }
}

Open in new window

0
gurpsbassiCommented:
yes you can reuse the same pojos.

As I said, work on the code that reads from a file, skips the column headers.
splits the string by comma and uses the tokens to set the bean values on the pojos.
0
kalyangkmAuthor Commented:
I already have everything ready from the previous triel where we tried to use opencsv parser. So in the following we need to get the things started after the split the string by comma replacing CsvToBean of opencsv.

package com.csv.filecompare;

import java.beans.Beans;
import java.beans.IntrospectionException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Set;
import java.util.LinkedHashMap;
import java.util.Map;
import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.OutputStream;
import java.io.StringWriter;
import java.lang.reflect.InvocationTargetException;
import com.csv.filecompare.CustomerSale;
import com.csv.filecompare.CustomerID;
import com.opencsv.bean.BeanToCsv;
import com.opencsv.bean.CsvToBean;
import com.opencsv.bean.MappingStrategy;
import com.opencsv.bean.ColumnPositionMappingStrategy;
//import au.com.bytecode.opencsv.bean.ColumnPositionMappingStrategy;


public class CompareCsvFiles {
	
	public static void main(String[] args){
		try {
		String RemoteFile1 = "C:/Users/exde2b8f/Desktop/CSV/Record match Java/FILE A.csv";
		String RemoteFile2 = "C:/Users/exde2b8f/Desktop/CSV/Record match Java/FILE B.csv";
		File file1 = new File(RemoteFile1);
		File file2 = new File(RemoteFile2);
		InputStream in = new FileInputStream(file1);
		InputStream in2 = new FileInputStream(file2);
		OutputStream out = new FileOutputStream(new File("C:/Users/exde2b8f/Desktop/CSV/Record match Java/FILE D.csv")); 
		InputStreamReader Reader = new InputStreamReader(in);
		InputStreamReader Reader2 = new InputStreamReader(in2);
		BufferedReader buffer = new BufferedReader(Reader);
		BufferedReader buffer2 = new BufferedReader(Reader2);
		
        //reading File A contents into Hashmap
		String read;
		String[] temp;
		Map<String,CustomerSale> customerSaleMap = new LinkedHashMap<String,CustomerSale>();
		buffer.readLine();
		while((read = buffer.readLine())!= null){
			temp=read.split(";");
			
			//for(int i=0;i<temp.length;i++){
			//	allrows.add(temp[i]);
			CsvToBean<CustomerSale> csv = new CsvToBean<CustomerSale>();
			ColumnPositionMappingStrategy<CustomerSale> strat = new ColumnPositionMappingStrategy<CustomerSale>();
			strat.setType(CustomerSale.class);
			String[] columns = new String[]{"organisation","salesRep","customer","price"};
			strat.setColumnMapping(columns);
			CustomerSale customerSale = (CustomerSale) csv.processLine(strat, temp);
			//.processLine(strat, temp);
			
	
			customerSaleMap.put(customerSale.getCustomer(),customerSale);
	
		}
		
		
		// Get a set of the entries
	    Set set = customerSaleMap.entrySet();
	    // Get an iterator
	    Iterator i = set.iterator();
	    
	    while(i.hasNext()){
		  Map.Entry me = (Map.Entry) i.next();
		  System.out.println(me.getKey() + ":");
		  System.out.println(me.getValue());
	  }		
		
		 //reading File B contents into Hashmap
		String read2;
		String[] temp2;
		Map<String,CustomerID> customerIDMap = new LinkedHashMap<String,CustomerID>();
		buffer2.readLine();
		while((read2 = buffer2.readLine())!= null){
			temp2=read2.split(",");
			
			//for(int i=0;i<temp.length;i++){
			//	allrows.add(temp[i]);
			CsvToBean<CustomerID> csv2 = new CsvToBean<CustomerID>();
			ColumnPositionMappingStrategy<CustomerID> strat2 = new ColumnPositionMappingStrategy<CustomerID>();
			strat2.setType(CustomerID.class);
			String[] columns2 = new String[]{"id","customerNumber"};
			strat2.setColumnMapping(columns2);
			CustomerID customerID = (CustomerID) csv2.processLine(strat2,temp2);
			
	
			customerIDMap.put(customerID.getCustomerNumber(),customerID);	

		}
		
          

		// Get a set of the entries
		    Set set2 = customerIDMap.entrySet();
		    // Get an iterator
		    Iterator i2 = set2.iterator();
		    
		    while(i2.hasNext()){
			  Map.Entry me2 = (Map.Entry) i2.next();
			  System.out.println(me2.getKey() + ":");
			  System.out.println(me2.getValue());
		  }		
		
		//Comparing the Customer Number in the 2 files.
		List<CustomerSale> filteredRecords = new ArrayList<CustomerSale>();
		for(Map.Entry<String,CustomerSale> entry : customerSaleMap.entrySet())
		{
			if(customerIDMap.containsKey(entry.getKey())){
				filteredRecords.add(entry.getValue());
			}
		}
		
		BeanToCsv<CustomerSale> beantocsv = new BeanToCsv<CustomerSale>();
		ColumnPositionMappingStrategy<CustomerSale> stratout = new ColumnPositionMappingStrategy<CustomerSale>();
		stratout.setType(CustomerSale.class);
		String[] columnsout = new String[] {"organisation", "salesRep", "customer", "price"}; 
		stratout.setColumnMapping(columnsout);  
		
		StringWriter sw = new StringWriter();
	    beantocsv.write(stratout, sw, filteredRecords);

	
	    System.out.println(sw.getBuffer().toString());			    
		    
		    
        }catch (FileNotFoundException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
        } 
        catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IntrospectionException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
        
	} 
}

Open in new window

0
gurpsbassiCommented:
so basically while looping through the lines of data, just create a pojo for each line and pass in all the required data to the constructor of that mojo (you will need to create a constructor if you don't have one already). get rid of the opencsv stuff.
0
kalyangkmAuthor Commented:
Do I try reading the each line into POJO using the following bean or is there a better way to learn?

http://www.codejava.net/coding/super-csv-reading-csv-files-into-pojos-with-csvbeanreader
0
gurpsbassiCommented:
the link you mentioned is for a framework called supercsv.
we are NOT using a framework.

You create a bean using a constructor method on the bean. Read up about constructors.
0
kalyangkmAuthor Commented:
I see what you mean by reading each line into a POJO using a constructor based on the following. But it is been implemented using Arraylist, but I was wondering it would be better if we have it in Hashmap. So if I have to read it through hashmap how should I put each line entries after seperating using the ";". Should I have the entire line as value and the particular customer field as Key? Bit confused, need some help here.

http://java67.blogspot.com/2015/08/how-to-load-data-from-csv-file-in-java.html
0
gurpsbassiCommented:
show me the code that you have got so far...
I want to see you creating the pojos through constructor. I can then advise what to do next.
0
kalyangkmAuthor Commented:
Hi Gurpbassi,

As this has been hanging for a long time and as I am not able to get time to focus on this. I am closing this question. Thanks for your inputs. I might open a new question later when I am ready, I would appreciate your inputs then if possible.

Regards
Kalyan.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Java

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.