Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>It first depends on the Python module you are using to access Excel. If you are using Windows I would recommend using Win32Com which can be found <a href="http://sourceforge.net/projects/pywin32/files/pywin32/Build%20217/" rel="nofollow">here.</a> This module gives Python programmatic access to any Microsoft Office Application (including Excel) and uses many of the same methods used in VBA.</p> <p>Here is the solution to your problem using Win32Com for Excel. I am assuming that you are using a comma (',') as your delimiter between names AND that your match on names between column A and column B is case sensitive (remember that in Python "A" does not equal "a")</p> <p>To start we want to connect to Excel and get access to the sheet with your names</p> <pre><code>#First we need to access the module that lets us connect to Excel import win32com.client # Next we want to create a variable that represents Excel app = win32com.client.Dispatch("Excel.Application") # Lastly we will assume that the sheet with the names is the active sheet sheet = app.ActiveSheet </code></pre> <p>At this point we have a variable named <em>sheet</em> that represents the active excel work sheet with the names. You can activate the sheet simply by clicking on any cell. Now we want to start by getting all of the names in Column A and storing it to a list. We have two options for doing this:</p> <ol> <li>We can iterate over all of the Cells in Column A and extract the name stored a as a string and append it to a list of all names using <em>sheet.Cells(row,col).Value.</em> This works just fine with smaller ranges, but can be slow if you are dealing with 500+ rows. </li> <li>We can extract the entire range of cells in Column A that contains the names using <em>sheet.Range("A1","A3").Value</em> and add the values to the list. This can be much faster if you have a large range of cells. For this example we will use Range.</li> </ol> <p>Example Continued:</p> <pre><code>import win32com.client app = win32com.client.Dispatch("Excel.Application") sheet = app.ActiveSheet # rang is an object representing the cells A1, A2, A3 rang = sheet.Range("A1","A3") # names is a tuple of length 3 containing tuples of length 2 names = rang.Value #nameLst is a list of all values in names nameLst = [name[0] for name in names] </code></pre> <p>Next we want to iterate over all of the names in Column B. For this we are going to use the sheet.Cells.Value function to get the list of names of each cell in Col B. We are also going to use the string.split(",") function to split the comma delimited names into a list of the names and string.strip() to remove any unnecessary whitespace. If any of the names in this list are in nameLst, we know we have a match and will put it in Col C.</p> <pre><code>import win32com.client app = win32com.client.Dispatch("Excel.Application") sheet = app.ActiveSheet rang = sheet.Range("A1","A3") names = rang.Value nameLst = [name[0] for name in names] #Iterate over the rows ic ColB. Remember Excel uses base 1 not 0 for inexing for rowI in range(1,3): cellNames = sheet.Cells(rowI,2).Value #split cellNames by "," and add all of the names to a list. cellNamesLst = [cellName.strip() for cellName in cellNames.split(",")] #Now we want a list of all names that are in cellNamesLst and in nameLst matchLst = [matchName for matchName in cellNamesLst if matchName in nameLst] #Create a string of all matches to go in Col C allMatches = ", ".join(matchLst) #Lastly put all matches in in Col C sheet.Cells(rowI,3).Value = allMatches </code></pre> <p>This will will put the string "Bob" into Cell C1 and "Dan, Brian" into cell C2. The use of win32com can be very powerful and can be used to automate much of what you do in all of MS Office applications.</p> <p>Here is the Final Code w/o comments:</p> <pre><code>import win32com.client app = win32com.client.Dispatch("Excel.Application") sheet = app.ActiveSheet rang = sheet.Range("A1","A3") names = rang.Value nameLst = [name[0] for name in names] for rowI in range(1,3): cellNames = sheet.Cells(rowI,2).Value cellNamesLst = [cellName.strip() for cellName in cellNames.split(",")] matchLst = [matchName for matchName in cellNamesLst if matchName in nameLst] allMatches = ", ".join(matchLst) sheet.Cells(rowI,3).Value = allMatches </code></pre> <p>Hope this helps.</p>
    singulars
    1. This table or related slice is empty.
    1. POIn excel with python, how do you find words, from an entire column, in a cell containing multiple words?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USMichael David Watson
    1. USMichael David Watson
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POIn excel with python, how do you find words, from an entire column, in a cell containing multiple words?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COThanks Michael. This worked an absolute treat!?! And most importantly thanks for the novice guide through it with the step by step explained.
      singulars
      1. PO
      1. USOnyx
    2. CONo problem Onyx. I remember when I started using Python for Excel, PowerPoint, Word and Access and it was really difficult to put all of the information together. If you have any more questions feel free to shoot me a message and I can see how I can help.
      singulars
      1. PO
      1. USMichael David Watson
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload