Active_prgs/Redgrave/Lilly-DataFlowSearch.py

"""

Lilly-DataFlowSearch

Created by:
Emanuel Borges
02.13.2024

Very simple program that will read an XLS file and search for references to specific systems.  If found, that entire row will be copied.

"""

import os
from datetime import datetime
from win32com.client import Dispatch

class DataFlowFileSearcher(object):
    version = "0.01"

    def __init__(self):
        self.fileToSearch = r"C:\Users\eborges\OneDrive - Redgrave LLP\Documents\Cases\Eli Lilly\Clinical Data Flow Analysis Req\_working\cmdb_ci_business_app_02022024_EB.xlsx"


        #self.searchTermsList = ["Vault EDC","Vault Coder","Vault CDB","CDTS","Argus","LSS","Information Hub","CLUWE","IMPACT","eCTS","CTMS","Adjudication","IQVIA-ECG","Central Laboratory","Covance","Q2 Ithica","Bioagilytix","Immunogenicity","Vault Clinical-CTMS","LabCorp","WuXi","IQVia","ABPM","Biotel","REESE"]
        self.searchTermsList = ["LSS","Information Hub","CLUWE","IMPACT","eCTS","CTMS","Adjudication","IQVIA-ECG","Central Laboratory","Covance","Q2 Ithica","Bioagilytix","Immunogenicity","Vault Clinical-CTMS","LabCorp","WuXi","IQVia","ABPM","Biotel","REESE"]
        self.workingDir = r"C:\Test_Dir\lili_dataflow"


        self.xlApp = Dispatch('Excel.Application')

    def PerformDataFlowSearch(self):
        wb = self.xlApp.Workbooks.Open(self.fileToSearch)
        sht = wb.Worksheets(1)
        for keyTerm in self.searchTermsList:
            print(f"Now searching for {keyTerm}...") 
            outputFile = open(os.path.join(self.workingDir,f"{keyTerm}.TXT"),'w')
            for rowNumb in range(1,13840):
                termFound = False
                for colNumb in range(1,4):
                    cellToSearchValue = sht.Cells(rowNumb,colNumb).Value
                    if keyTerm.upper() in cellToSearchValue.upper():
                        termFound = True
                if termFound:
                    fullRow = sht.Range(f"A{rowNumb}:W{rowNumb}")
                    outputFile.write(f"{fullRow}\n")
            outputFile.close()
        wb.Close()
            
            
if __name__ == '__main__':
    dfs = DataFlowFileSearcher()
    dfs.PerformDataFlowSearch()
Revision:	820
Committed:	Wed Feb 14 14:49:42 2024 UTC (2 years, 1 month ago) by nino.borges
Content type:	text/x-python
File size:	2212 byte(s)
Log Message:	First version of a simple program that will, using COM, search an Excel spreadsheet for specific key terms and, if it finds it, will copy the entire row out to a file organized by key term. This is the first working version but is just too slow. I think I'm making too many calls to the API. I'm going to refactor this to see if I can speed it up substantially.
#	User	Rev	Content
1	nino.borges	820	"""
2
3			Lilly-DataFlowSearch
4
5			Created by:
6			Emanuel Borges
7			02.13.2024
8
9			Very simple program that will read an XLS file and search for references to specific systems. If found, that entire row will be copied.
10
11			"""
12
13			import os
14			from datetime import datetime
15			from win32com.client import Dispatch
16
17			class DataFlowFileSearcher(object):
18			version = "0.01"
19
20			def __init__(self):
21			self.fileToSearch = r"C:\Users\eborges\OneDrive - Redgrave LLP\Documents\Cases\Eli Lilly\Clinical Data Flow Analysis Req\_working\cmdb_ci_business_app_02022024_EB.xlsx"
22
23
24
25			#self.searchTermsList = ["Vault EDC","Vault Coder","Vault CDB","CDTS","Argus","LSS","Information Hub","CLUWE","IMPACT","eCTS","CTMS","Adjudication","IQVIA-ECG","Central Laboratory","Covance","Q2 Ithica","Bioagilytix","Immunogenicity","Vault Clinical-CTMS","LabCorp","WuXi","IQVia","ABPM","Biotel","REESE"]
26			self.searchTermsList = ["LSS","Information Hub","CLUWE","IMPACT","eCTS","CTMS","Adjudication","IQVIA-ECG","Central Laboratory","Covance","Q2 Ithica","Bioagilytix","Immunogenicity","Vault Clinical-CTMS","LabCorp","WuXi","IQVia","ABPM","Biotel","REESE"]
27			self.workingDir = r"C:\Test_Dir\lili_dataflow"
28
29
30			self.xlApp = Dispatch('Excel.Application')
31
32			def PerformDataFlowSearch(self):
33			wb = self.xlApp.Workbooks.Open(self.fileToSearch)
34			sht = wb.Worksheets(1)
35			for keyTerm in self.searchTermsList:
36			print(f"Now searching for {keyTerm}...")
37			outputFile = open(os.path.join(self.workingDir,f"{keyTerm}.TXT"),'w')
38			for rowNumb in range(1,13840):
39			termFound = False
40			for colNumb in range(1,4):
41			cellToSearchValue = sht.Cells(rowNumb,colNumb).Value
42			if keyTerm.upper() in cellToSearchValue.upper():
43			termFound = True
44			if termFound:
45			fullRow = sht.Range(f"A{rowNumb}:W{rowNumb}")
46			outputFile.write(f"{fullRow}\n")
47			outputFile.close()
48			wb.Close()
49
50
51
52			if __name__ == '__main__':
53			dfs = DataFlowFileSearcher()
54			dfs.PerformDataFlowSearch()