Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

how to compare integers in two excel sheets using java program

Posted on 2014-03-10
6
Medium Priority
?
1,311 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 17

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 17

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 17

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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Java contains several comparison operators (e.g., <, <=, >, >=, ==, !=) that allow you to compare primitive values. However, these operators cannot be used to compare the contents of objects. Interface Comparable is used to allow objects of a cl…
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 will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
This video teaches viewers about errors in exception handling.
Suggested Courses

963 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