Comparing 2 excel file python

Question

I have tried to compare 2 excel files. It is working when it is in range but when not in range it is displaying an error.

Note: In range I mean, the 1st excel file has 5 rows and 5 columns, the 2nd excel file have more or less rows than the 1st excel file and same number of column.

How can I achieve this?

The code are as below:

from itertools import izip_longest
import xlrd
rb1 = xlrd.open_workbook('a.xlsx')
rb2 = xlrd.open_workbook('b.xlsx')
sheet1 = rb1.sheet_by_index(0)
sheet2 = rb2.sheet_by_index(0)
for rownum in range(max(sheet1.nrows, sheet2.nrows)):
    if rownum < sheet1.nrows:
        row_rb1 = sheet1.row_values(rownum)
        row_rb2 = sheet2.row_values(rownum)
        for colnum, (c1, c2) in enumerate(izip_longest(row_rb1, row_rb2)):
            if c1 != c2:***emphasized text***
                print "Row {} Col {} - {} != {}".format(rownum+1, colnum+1, c1, c2)
    else:
        print "Row {} missing".format(rownum+1)

Hi, could you please give context as to why and how you're trying to compare two Excel files? Are you trying to spot the differences? Or to tell if the files are the same? — Corentin Pane
– Corentin Pane, Commented Nov 5, 2019 at 16:12
Hello @CorentinPane, I want to spot the difference and print it. Even if, for example row5 in excel 1 is empty and in excel 2 row5 not empty or the other way around. Sorry for my bad english. — garena7 garena7
– garena7 garena7, Commented Nov 5, 2019 at 16:29
What error is it displaying? What line causes the error? Check here. — Corentin Pane
– Corentin Pane, Commented Nov 5, 2019 at 16:31

Christopher Lee · Accepted Answer · 2019-11-08 17:55:44Z

1

Would this solution work for you? I just added a check to see which sheet has the lower number of rows.

from itertools import izip_longest
import xlrd
rb1 = xlrd.open_workbook('a.xlsx')
rb2 = xlrd.open_workbook('b.xlsx')
sheet1 = rb1.sheet_by_index(0)
sheet2 = rb2.sheet_by_index(0)
for rownum in range(max(sheet1.nrows, sheet2.nrows)):
    if rownum < min(sheet1.nrows, sheet2.nrows):
        row_rb1 = sheet1.row_values(rownum)
        row_rb2 = sheet2.row_values(rownum)
        for colnum, (c1, c2) in enumerate(izip_longest(row_rb1, row_rb2)):
            if c1 != c2:***emphasized text***
                print "Row {} Col {} - {} != {}".format(rownum+1, colnum+1, c1, c2)
    else:
        print "Row {} missing".format(rownum+1)

This is the sample output I get when I try to run it on two sample workbooks.

Row 15 Col 2 - Monkey != Cow

edited Nov 8, 2019 at 17:55

answered Nov 5, 2019 at 17:10

Christopher Lee

897 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

garena7 garena7 Over a year ago

TypeError: '<' not supported between instances of 'method' and 'int'

Christopher Lee Over a year ago

Hmm I just tested the code out on my machine using two sample work books I created. It seems to work fine for me. Are you sure that sheet1.nrows are integer values. It looks like it is trying to compare a method to an int.

Christopher Lee Over a year ago

I updated the answer to show what sample output I get when I try to run it.

Collectives™ on Stack Overflow

Comparing 2 excel file python

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related