Compare two excel files in python

Question

import xlrd
wb_1 = xlrd.open_workbook('Book1.xls', on_demand=True)
ws_1 = wb_1.sheet_by_name('Sheet3')
wb_2 = xlrd.open_workbook('Book2.xls', on_demand=True)
ws_2 = wb_2.sheet_by_name('Sheet3')
for i in range(ws_1.ncols):
    col_value1 = ws_1.cell_value(0, i)
    for cell in range(ws_1.nrows):
        cell_value1 = ws_1.cell(cell, i)
        for j in range(ws_2.ncols):
            col_value2 = ws_2.cell_value(0, i)       
            for cell in range(ws_2.nrows):
                cell_value2 = ws_2.cell(cell, i)
                if cell_value2 == cell_value1:
                    print('same')

Im trying to compare two excel worksheets, Im not sure whether im going in a right way.
How to find the changed values

Python Bang · Accepted Answer · 2020-03-02 07:27:38Z

Try the below code for extracting row and columns differences.

import xlrd
wb_1 = xlrd.open_workbook('Book1.xlsx', on_demand=True)
ws_1 = wb_1.sheet_by_name('Sheet3')

rw,cl,rw2,cl2=[[] for i in range(4)]

for i in range(0,ws_1.ncols):
    col_value1 = ws_1.cell(0, i).value
    cl.append(col_value1)
    for cell in range(0,ws_1.nrows):
        row_value1 = ws_1.cell(cell, i).value
        rw.append(row_value1)

wb_2 = xlrd.open_workbook('Book2.xlsx', on_demand=True)
ws_2 = wb_2.sheet_by_name('Sheet3')        

for i in range(0,ws_2.ncols):
    col_value2 = ws_2.cell(0, i).value
    cl2.append(col_value2)
    for cell in range(0,ws_2.nrows):
        row_value2 = ws_2.cell(cell, i).value
        rw2.append(row_value2)

for i in range(len(cl)):
    for j in range(len(cl2)):
        if cl[i]!=cl2[j]:
            print("column difference",i,j)

for i in range(len(rw)):
    for j in range(len(rw2)):
        if rw[i]!=rw2[j]:
            print("row difference",i,j)

გუჯა ლომსაძე · Accepted Answer · 2020-03-02 07:20:57Z

1

Try to convert Excel into CSV file, it will seperate your values with commas. Library is called CSV Just "import csv" Then open file using "with", get rows with column names and you will get list or dictionary ( depends on approach ) You'll have to just compare list indexes and that's the easiest way.

Read article: https://realpython.com/python-csv/

answered Mar 2, 2020 at 7:20

გუჯა ლომსაძე

111 bronze badge

Collectives™ on Stack Overflow

Compare two excel files in python

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related