Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

how to compare integers in two excel sheets using java program

Posted on 2014-03-10
6
Medium Priority
?
1,286 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
[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
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

By the end of 1980s, object oriented programming using languages like C++, Simula69 and ObjectPascal gained momentum. It looked like programmers finally found the perfect language. C++ successfully combined the object oriented principles of Simula w…
Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
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:
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
Suggested Courses

719 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