Searching for URLs and printing the corresponding row from an Excel spreadsheet

[johnbernard]

Hi,

So far I have created a script that identifies the last modified folder in a list, appends all the spreadsheets in it and then outputs a new appended Spreadsheet.

Now I want to search the new spreadsheet I have created for a set list of URLs that I have and where a match is found, print the row that contains the URL.

Here ismy script so far:

import os
import pandas as pd
import xlsxwriter
exportroot = r'\\atlas\\Knowhow\\ExternalLinkValidationReports\\UK'  # change this to your folder path
folderlist = [] # blank list of folders - we'll put the paths to the subfolders in this
# check that the folder exists and is actually a folder
if os.path.exists(exportroot) and os.path.isdir(exportroot):
    exportfolders = next(os.walk(exportroot))[1]  # get list of children - this will include folders *and* files
    for exportfolder in exportfolders:  # for each of those children………
        exppath = os.path.join(exportroot, exportfolder)  # define the full path………
        if os.path.isdir(exppath):  # and if that path defines a directory………
            folderlist.append(exppath)    # add it to our list
sortedlist = sorted(folderlist, key=lambda t: -os.stat(t).st_mtime)  # sort the list, most recent first
mostrecent = sortedlist[0]  # get the full path to the most recently modified subfolder from the sorted list
print(mostrecent)
os.chdir(mostrecent)  # Changing current working directory to "mostrecent" folder
path = os.getcwd() # Defining path as new cwd
print(path)
cwd = path # Defining cwd as path
files = os.listdir(cwd)
df = pd.DataFrame() #Importing data frame
for file in files:
        if file.endswith('.xlsx'):
            df = df.append(pd.read_excel(file), ignore_index=True) #Identifying Excel files in "mostrecent" folder
df.head() #Printing first 5 rows to test data
    
writer = pd.ExcelWriter(r'Appended_Reports.xlsx', engine='xlsxwriter',options={'strings_to_urls': False})
df.to_excel(writer)
writer.close() #Creates report - changes options to avoid 255 character URL limit in excel 

Has anyone done something similar and might be able to help? I've looked through openpyxl guidance but as I'm a newbie I'm struggling to find the function. It's the Appended_Reports.xlsx spreadsheet that I create at the end of the process that I want to search.

Any help greatly appreciated!

Thanks,

John