app.py

import xlrd, json



workbook = xlrd.open_workbook(r"C:\Users\lenovo\Downloads\Kotak-converted.xlsx")
workbook = xlrd.open_workbook(r"C:\Users\lenovo\Downloads\Kotak-converted.xlsx", on_demand = True)
worksheet = workbook.sheet_by_index(0)
first_row = []
for col in range(worksheet.ncols):
    for sheet in workbook.sheets():
        for rowidx in range(sheet.nrows):
            row = sheet.row(rowidx)
            for colidx, cell in enumerate(row):
                if cell.value == "Date" :
                    first_row.append(worksheet.cell_value(rowidx,col))
data =[]
for sheet in workbook.sheets():
    for rowidx in range(sheet.nrows):
        row = sheet.row(rowidx)
        for colidx, cell in enumerate(row):
            if cell.value == "Date" :
                for row in range(rowidx+1, worksheet.nrows):
                    elm = {}
                    for col in range(worksheet.ncols):
                        elm[first_row[col]]=worksheet.cell_value(row,col)
                    data.append(elm)          
for x in data:
    for y in data:
        if x['Date']=='':
            res = {key: str(x[key]) + str(y.get(key, '')) for key in x.keys()}
for x in data:
    if x['Date'] == '':
        data.remove(x)  
for x in data:
    x.pop('')
    
with open('results/result_kotak.json', 'w') as f:
    json.dump(data, f)

print(data, len(data))