How to test cell coordinates in Python to change data?

Question

I'm trying to use python to migrate some data. I need to test see if I have specific column, then if cell value is one value, change to specific other value. So my problem is, how to do test for coordinate.

I am using python and excel. I need to read in excel file, then make changes to data, and output new, changed excel file. I'm using Python and PyCharm and now trying with openpyxl. I tried xlrd/xlwt, and tried doing with csv. To take data from sql to excel file, then make changes - this seemed to be easiest way to do.

So for instance, in specific case, I have in column 4 field for sex. So I try to specify only column 4 then. Old data has values either "Male" or "Female" new data needs to be numeric code, 1 for Male and 2 for Female.

    for cellObj in row:
     if cellObj.coordinate == [4:row]:
        if cellObj.value == 'Male':
            cellObj.value = 1
        elif cellObj.value == 'Female':
            cellObj.value = 2

(Sorry, I try to make format for question right, but code doesn't show the same.)

This code is not right. I don't understand how to do this. I have book Automate The Boring Stuff With Python by Al Sweigart. I have been looking in forums and blogs, but always you must know the exact coordinates. That's why I try with specific column 4 rather than testing in code to see which column I need.

EDIT:

I could not add to commant. Here is all code. Only getting 1 row now.

wb = openpyxl.load_workbook(file_location)
sheet = wb.worksheets[0]
for cellObj in sheet.rows[0]:
  print(cellObj.value),
  print(' | '),
for row in sheet.iter_rows():
  for row_count, cellObj in enumerate(row):
      if cellObj.coordinate == 'E{}'.format(row_count):
          print row_count
          print(cellObj.value),
          if cellObj.value == 'Male':
             cellObj.value = 1
             print(cellObj.value)
          elif cellObj.value == 'Female':
             cellObj.value = 2
             print(cellObj.value)

I added and change sheet.iter_rows....This here: OpenPyXL + How can I search for content in a cell in Excel, and if the content matches the search criteria update the content? But this doesn't work either. On for loop, I can't do:

for i in sheet:
   do this for each row "i"

This format does not work?

Also, here: OpenPyXL + How can I search for content in a cell in Excel, and if the content matches the search criteria update the content? Someone said can do sheet.rows() to go through each row, but this gives the tuple error again. TypeError: 'tuple' object is not callable

Mike Müller · Accepted Answer · 2015-12-14T21:29:51.870

1

EDIT

After your update, this seems to be a better solution:

for row in sheet.iter_rows():
    for cellObj in row:
        if cellObj.column == 'E':
             # do something

Old answer

Coordinates are in the form A1. You need to test against something like this:

for row_count, cellObj in enumerate(row, 1):
    if cellObj.coordinate == 'D{}'.format(row_count):

edited Dec 14 '15 at 21:29

answered Dec 14 '15 at 20:16

Mike Müller

82,630
20
166
161

Okay thank you for that Mike Müller. I think I was trying to make like Python array. Excel seems to work differently. I understand. My code did work, but I can't seem to move through rows. It only gives me first row. For loop does not seem to work as I would expect it. Here is – stahna Dec 14 '15 at 20:46
Perfect! That was I was looking for. Thank you again! I have it now working how I expect and want. – stahna Dec 15 '15 at 14:43

How to test cell coordinates in Python to change data?

1 Answers1