String/regex search over Excel in Python issue

Question

I'm a newb to SO, and relatively new to Python, so i'm sorry if this is a simple fix or an inappropriate question.

Firstly, my program generally works, but i'm trying to implement some redundancy/catchalls for to make it robust.

The program looks over a directory (and sub-dirs) of excel files, opens them individually, scours for data (on a specific sheet), and dumps it out to a csv. There are loops involved as each search term is effectively for the head of a column, and i want 4 values beneath this.

I use regular expressions to define search terms.

I've written a function to search over the excel sheet for a match to a regular expression. The sheet has strings and other format-types within the cells, hence the type(query) for strings.

def SearchXLWithRe(regex)
    for i in range(1, Row_limit):         # row limit is defined by OpenPyXL module
        for j in range(1, Column_limit):    # same here for column limit
            query = ws.cell(row = i, column = j).value
            if type(query) == str:         # i only want to look at strings
                if regex.search(query):    # of the responses that are strings, i want to match to the regex
                    return [i,j]

This function works for searches on strings that are there (which has so far always been the case). I want to add redundancy for when some excel files wont contain terms I want to search for, but others will (it could just return some made up coordinates for a blank cell at eg. 1000,1000 or something).

I have tried putting an else but as it's looping over an excel doc and finding multiple string, all this returns is a None.

I think i have a simple logic problem, but I just can't see it; if anyone can offer me some pointers the help would be gratefully (and eagerly!) received.

Questions i've reviewed (but i'm still lost):

In Python how should I test if a variable is None, True or False

OpenPyXL + How can I search for content in a cell in Excel, and if the content matches the search criteria update the content?

If a worksheet does not contain what you're looking for then the return value is None: this the return value of any Python function with no explicit return. — Charlie Clark
– Charlie Clark, Commented May 4, 2016 at 17:13
Thanks, and yes that's what i'm getting now (with the suggested answer so far) even when i know the value is there to be found. I'm confident i've a logic problem. Just don't know what! — HDSci
– HDSci, Commented May 4, 2016 at 18:39

R Singh · Accepted Answer · 2016-05-04 16:44:13Z

1

def SearchXLWithRe(regex)
    for i in range(1, Row_limit):         # row limit is defined by OpenPyXL module
        for j in range(1, Column_limit):    # same here for column limit
            query = ws.cell(row = i, column = j).value
            if type(query) == str:         # i only want to look at strings
                if regex.search(query):    # of the responses that are strings, i want to match to the regex
                    return [i,j]
     return [x,y] #x,y are the dummy locations

Just return after the for loops, it will only be executed if no match was found.

answered May 4, 2016 at 16:44

R Singh

7651 gold badge5 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

HDSci Over a year ago

I'd tried this actually - moved the return [100,100] into all positions (stupid i realise!) but unfortunately this doesn't work - it just returns None for each value being sought, implying that it's finding nothing and just dumping out.

HDSci Over a year ago

EDIT: A THOUSAND APOLOGIES!!! YES THIS FIXED IT. Initially it didn't, but i had a similar error in another function further on (which depended on this result) and i've now fixed that. Thank you very much.

Collectives™ on Stack Overflow

String/regex search over Excel in Python issue

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related