CODEWITHSUNDEEP
pythonexcel
kar_n
kar_n

Reputation: 78

Concat and write values in new column using python

enter image description here

import xlrd

book = xlrd.open_workbook("D:/Book.xlsx")
sh = book.sheet_by_name('Sheet4')

for rowidx in range(sh.nrows):
    row = sh.row(rowidx)
    for colidx, cell in enumerate(row):
        if cell.value == "Issue":
            print(sh.name)
            print(colidx, rowidx)
            data_colidx = colidx
            data_rowidx = rowidx
first_row = []
i = int(data_rowidx)+1
concat_val = []
for x in range(sh.nrows):
    first_row.append(sh.cell_value(i, data_colidx))
    i = i + 1
    if i >= sh.nrows:
        break
    if sh.cell(i, data_colidx).value == xlrd.empty_cell.value:
        #print(i)
        concat_val.append(sh.cell_value(i, 0) + '.' + sh.cell_value(i, 1))

Reading every row in the 'Issue' Column and if it is empty im concating the 'Dataset' and 'variable' column values.
What should do, if I need to concat both Dataset and Variable column and write in a new column.

Upvotes: 1

Views: 262

Answers (1)

CDP1802
CDP1802

Reputation: 16357

from openpyxl import load_workbook

# open workbook 
excel_file = 'D:/Book.xlsx'
wb = load_workbook(excel_file)
ws = wb["Sheet4"]
r = 1
while ws.cell(r,3).value != "Issue":
    r += 1
    if r > ws.max_row:
       exit("Issue not found in Col C")

# insert new column
ws.insert_cols(3)

# start search next row    
updates = 0
r += 1

while r <= ws.max_row:
    if ws.cell(r,4).value is None:
        colA = ws.cell(r,1).value
        colB = ws.cell(r,2).value

        if colA is None:
            if colB is None: s = ""
            else: s = colB
        elif colB is None:
            s = colA
        else:
            s = colA + "." + colB 

        ws.cell(r,3).value = s
        updates += 1
    r += 1    

# save with new name
updated_file = excel_file.replace(".xlsx", "_upd.xlsx")
wb.save( updated_file )
wb.close
print(str(updates) + " rows updated. File created " + updated_file)

Upvotes: 1

Related Questions