Solved

how to compare integers in two excel sheets using java program

Posted on 2014-03-10
6
1,231 Views
Last Modified: 2014-03-29
Hi
I have two excel sheets
sheet 1 contains some numbers(sheet1 contains total 500k nos in excel)
sheet 2 contains same numbers(490k) but some of the nos in sheet 2 doesn't contain in sheet 1

i have to print the output like matching nos and unmatched nos
how to do using java program
0
Comment
Question by:srikotesh
6 Comments
 
LVL 12

Expert Comment

by:Sharon Seth
ID: 39919820
That's done easily in excel , do you need to use a java program ?
0
 
LVL 16

Expert Comment

by:krakatoa
ID: 39920569
I can't see either why you need Java at this stage anyway.

Just copy this formula into the cells of a third spreadsheet behind the other two :

=IF(Sheet1!A1=Sheet2!A1,"match","no match")

Open in new window

0
 
LVL 32

Expert Comment

by:awking00
ID: 39921134
If for some reason you must use java, I would suggest using the POI api to loop through the first spreadsheet comparing the values found looping through the second spreadsheet. However, with 500,000 numbers, I suspect some will be duplicated in the same spreadsheet so, for example, if the number 33 shows up 7 times in one sheet and 4 times in the other, is that considered 4 matches and 3 mismatches or is the number 33 just considered a match? Are there other columns in the spreadsheets that can act as a key to comparing values? Perhaps some sample data for the two spreadsheets and your expected results would help clarify.
0
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
LVL 16

Expert Comment

by:krakatoa
ID: 39921961
How did all this data get into a spreadsheet?

No-one inputs that amount for a one-off analysis.
0
 
LVL 2

Author Comment

by:srikotesh
ID: 39928506
ya ,thru excel we can complete this task, one of my friend has written this program we can do this way also
can any one explain how he has converted excel sheet into .txt file
please see the code give some suggestion to do in the following way

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.util.HashSet;
import java.util.Set;
import java.util.StringTokenizer;

public class ReaddFile {

      public static void main(String[] args) {

            BufferedReader br = null;

            BufferedReader br1 = null;

            try {

                  String VMSString;

                  Set<String> vmsList = new HashSet<String>();
                  Set<String> vfsList = new HashSet<String>();

                  br = new BufferedReader(new FileReader(
                              "C:\Users\Smasikukku\Desktop\\VMS.txt"));

                  while ((VMSString = br.readLine()) != null) {
                        // System.out.println(VMSString);
                        StringTokenizer st2 = new StringTokenizer(VMSString, "|");

                        while (st2.hasMoreElements()) {
                              vmsList.add(((String) st2.nextElement()).trim());
                        }
                  }

                  String VFSString;

                  br1 = new BufferedReader(new FileReader(
                              "C:\Users\Smasikukku\Desktop\\VFS.txt"));

                  while ((VFSString = br1.readLine()) != null) {
                        // System.out.println(VFSString);
                        StringTokenizer st3 = new StringTokenizer(VFSString, "|");

                        while (st3.hasMoreElements()) {
                              vfsList.add(((String) st3.nextElement()).trim());
                        }

                  }

                  for (String val : vmsList) {
                        if (vfsList.contains(val.trim())) {
                              // System.out.println("Not find in VMS List  : " + val);
                        } else {
                              System.out.println("Not find in VFS List  : " + val);
                        }
                  }

            } catch (IOException e) {
                  e.printStackTrace();
            } finally {
                  try {
                        if (br != null)
                              br.close();
                  } catch (IOException ex) {
                        ex.printStackTrace();
                  }
            }

      }
}
0
 
LVL 16

Accepted Solution

by:
krakatoa earned 500 total points
ID: 39928750
An Excel spreadsheet can be saved in several other formats, one of which is CSV - comma separated values - text.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
why cannot we forward request once the response is committed 2 46
egit plugin on eclipse 8 82
java jdbc batch example 8 41
expectj telnet failing 5 45
After being asked a question last year, I went into one of my moods where I did some research and code just for the fun and learning of it all.  Subsequently, from this journey, I put together this article on "Range Searching Using Visual Basic.NET …
I had a project requirement for a displaying a user workbench .This workbench would consist multiple data grids .In each grid the user will be able to see a large number of data. These data grids should allow the user to 1. Sort 2. Export the …
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

856 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