#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    2
    Rep Power
    0

    Searching Excel spreadsheet and returning yes or no?


    I am looking to write a small script that when run will ask the user to type in a username, it will then search the specified spreadsheet full of usernames for that specific name. If the username is there it will simply reply "yes" if not it will reply "no" I am fairly new and have tried a few ways but nothing will work.

    Cheers
  2. #2
  3. Contributing User
    Devshed Demi-God (4500 - 4999 posts)

    Join Date
    Aug 2011
    Posts
    4,931
    Rep Power
    481
    I suppose you tell excel to search the spreadsheet for you and return a result using the COM interface (at least that's what it was twelve years ago).

    I've never succeeded with this tack.

    Another approach is longer, you'd save the relevant spreadsheet regions in a text form (typically comma separated values, .csv) then search that file with egrep (or with python in this forum).

    I've always used the second approach because
    1) I can inspect and manipulate the text file with emacs,
    and
    2) The COM interface confronted me with some sort of registration key question to which I had no clue of what my response ought to be.
    [code]Code tags[/code] are essential for python code and Makefiles!
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    2
    Rep Power
    0
    Originally Posted by b49P23TIvg
    I suppose you tell excel to search the spreadsheet for you and return a result using the COM interface (at least that's what it was twelve years ago).

    I've never succeeded with this tack.

    Another approach is longer, you'd save the relevant spreadsheet regions in a text form (typically comma separated values, .csv) then search that file with egrep (or with python in this forum).

    I've always used the second approach because
    1) I can inspect and manipulate the text file with emacs,
    and
    2) The COM interface confronted me with some sort of registration key question to which I had no clue of what my response ought to be.
    Thanks for the suggestion, any idea how I get started doing this? All I have is a .xlsx file to go off. so how would I convert it then run the search through python. Thanks.
  6. #4
  7. Contributing User
    Devshed Demi-God (4500 - 4999 posts)

    Join Date
    Aug 2011
    Posts
    4,931
    Rep Power
    481
    I'll pretend that I know next to nothing about excel.

    In excel:

    Activate the spreadsheet of interest. If it has graphs, pictures, equations, links, or other stuff you want to ignore and this fails then move only the interesting text like columns onto a new sheet and try again using that sheet.

    Note: you could, as far as I know, have a customized spreadsheet for which none of this makes any sense what-so-ever. Indeed, you might have a little clickable box that exports as csv in one shot.

    Click File (I think)
    Click export in the menu that appears.
    wiggle your way through the rest of the menus to reach export as comma separated value (.csv)
    Then choose a file name, and know where that file went.

    One of my hang ups with MicroSoft Windows is that I like to use the command line, and I like to know where my files are. In the giga-successful Microsoft model one does not need to know path names, they need to know "recent file" or "funny looking picture toward the upper left of my monitor". Python needs a path. Good luck finding this .csv file.

    Anyway, why must you invoke python for the task? Here I'd use the "find" command on the DOS command line.

    Actually, I wouldn't. I would have installed Cygwin/X onto my Microsoft system and I'd use grep or gawk.

    Anyway, here's a python code. By your problem statement I really don't care if the username appears in a specific column.
    Code:
    '''
        example use, this file is p.py in current directory:
    
        $ cat a   # display file a
        c sea 1
        b bee 2
        $ python p.py           # example misuse.
        Use: python p.py username file
        $ python p.py sea a    # successful example.
        yes
        $ python p.py sxa a    # username is not in the file.
        no
        $ python p.py  sea 'no such file'   # another sort of failure
        Use: python p.py username file
        $
    '''
    
    import sys, os
    
    try:
        word = sys.argv[1]
        filename = sys.argv[2]
        os.stat(filename)
    except:
        print('Use: python '+sys.argv[0]+' username file')
        sys.exit(1)
    
    with open(filename,'r') as inf:
        print('no yes'.split()[word in inf.read()])
    Last edited by b49P23TIvg; September 12th, 2012 at 10:06 AM. Reason: insert a few more comments, although the OP might not recognize a bash comment. oh well!
    [code]Code tags[/code] are essential for python code and Makefiles!

IMN logo majestic logo threadwatch logo seochat tools logo