Concat and write values in new column using python

Question

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.

@CDP1802 inserting a new column after 'Variable', before 'Issue' — kar_n, Mar 16 '20 at 17:49
xlrd only reads see https://stackoverflow.com/questions/41886791/how-to-use-xlrd-for-writing-an-excel-file. Do you have openpyxl — CDP1802, Mar 16 '20 at 18:04
@CDP1802 I thought of using xlsx writer, but I'm open to use openpyxl too — kar_n, Mar 16 '20 at 18:10
Why are you using `xlrd` ? Last I checked the library was unmaintained and openpyxl was the recommended replacement. — AMC, Mar 16 '20 at 21:57

CDP1802 · Accepted Answer · 2020-03-17T08:14:15.893

1

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)

edited Mar 17 '20 at 08:14

answered Mar 16 '20 at 20:25

CDP1802

13,871
2
7
17

this line _'if ws.cell(r,4).value is None: s = ws.cell(r,1).value + "." + ws.cell(r,2).value'_ **TypeError: unsupported operand type(s) for +: 'NoneType' and 'str'**. I made small modifications and its working fine. Thank you – kar_n Mar 17 '20 at 07:20
No, both columns are not empty. But some of the cells in the 'Issue' column is empty – kar_n Mar 17 '20 at 07:25
I'm using python 3.7. I think **None** in `if ws.cell(r,4).value is None:` is causing the error. there is no error if I remove the **is None** – kar_n Mar 17 '20 at 07:32
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/209749/discussion-between-keshor-and-cdp1802). – kar_n Mar 17 '20 at 07:39

Concat and write values in new column using python

1 Answers1