| 1 |
"""
|
| 2 |
|
| 3 |
EndoTableFieldNameSearch
|
| 4 |
|
| 5 |
Created by:
|
| 6 |
Emanuel Borges
|
| 7 |
09.13.2021
|
| 8 |
|
| 9 |
Very simple program that will read a pair of csv files and extract the table and field data which matches specific terms
|
| 10 |
in the field or table name, along with other informaiton about that field and table.
|
| 11 |
|
| 12 |
"""
|
| 13 |
|
| 14 |
import os
|
| 15 |
|
| 16 |
if __name__ == '__main__':
|
| 17 |
tableReportFileName = r"C:\Users\eborges\Documents\Cases\Endo\Temp\2021.08.26 Aspect Delivery - Database object report (with Navigator but not top 50 rows)\SQL_Db_tab_col_info (SFA)-tableonly.csv"
|
| 18 |
fieldReportFileName = r"C:\Users\eborges\Documents\Cases\Endo\Temp\2021.08.26 Aspect Delivery - Database object report (with Navigator but not top 50 rows)\SQL_Db_tab_col_info (SFA).csv"
|
| 19 |
|
| 20 |
outputFileName = r"C:\Users\eborges\Documents\Cases\Endo\Temp\2021.08.18 IQVIA - Navigator data dictionary\RedgraveAnalysis(SFA).csv"
|
| 21 |
|
| 22 |
## List of terms to search in the table and field names
|
| 23 |
interestingList = ["COMMENT", "NOTE"]
|
| 24 |
fieldMatrix = {}
|
| 25 |
tableMatrix = {}
|
| 26 |
|
| 27 |
|
| 28 |
## First populate the tableMatrix
|
| 29 |
contents = open(tableReportFileName).readlines()
|
| 30 |
contents = contents[1:]
|
| 31 |
for line in contents:
|
| 32 |
line = line.replace("\n","")
|
| 33 |
line = line.split("|")
|
| 34 |
tableMatrix[line[0].upper()] = line[1]
|
| 35 |
print("Total rows, excluding the headder row, for table list is %s"% len(tableMatrix.keys()))
|
| 36 |
|
| 37 |
|
| 38 |
## Second populate the fieldMatrix
|
| 39 |
contents = open(fieldReportFileName).readlines()
|
| 40 |
contents = contents[1:]
|
| 41 |
for line in contents:
|
| 42 |
line = line.replace("\n","")
|
| 43 |
line = line.split("|")
|
| 44 |
fieldMatrix[(line[1].upper(),line[2].upper())] = (line[3], line[5],line[9])
|
| 45 |
print("Total rows, excluding the headder row, for field list is %s"% len(fieldMatrix.keys()))
|
| 46 |
|
| 47 |
|
| 48 |
|
| 49 |
|
| 50 |
|
| 51 |
|
| 52 |
tableFieldList = list(fieldMatrix.keys())
|
| 53 |
finalList = []
|
| 54 |
for term in interestingList:
|
| 55 |
temp = [i for i in tableFieldList if term in str(i)]
|
| 56 |
finalList += temp
|
| 57 |
|
| 58 |
print("Total entries that match terms is %s"% len(finalList))
|
| 59 |
|
| 60 |
## Now deduplicate the list
|
| 61 |
test = {}
|
| 62 |
for i in finalList:
|
| 63 |
test[i] = 1
|
| 64 |
print("Total Deduplicated entries that match terms is %s"% len(list(test.keys())))
|
| 65 |
|
| 66 |
|
| 67 |
outputFile = open(outputFileName,'w')
|
| 68 |
for tf in list(test.keys()):
|
| 69 |
nl = outputFile.write("%s|%s|%s|%s|%s|%s\n"%(tf[0],tf[1],fieldMatrix[tf][2],tableMatrix[tf[0]],fieldMatrix[tf][0],fieldMatrix[tf][1]))
|
| 70 |
outputFile.close() |
