| 1 |
nino.borges |
778 |
"""
|
| 2 |
|
|
|
| 3 |
|
|
Endo2010CopyCompareWProd
|
| 4 |
|
|
|
| 5 |
|
|
Created by:
|
| 6 |
|
|
Emanuel Borges
|
| 7 |
|
|
02.09.2022
|
| 8 |
|
|
|
| 9 |
|
|
This program will take exports from the 2010Copy database and, using a unique key like CallID or stdActivity,
|
| 10 |
|
|
compare these entries with SIF2007Backup or SIF 2008-2012 entries and generate a report that, on select fields
|
| 11 |
|
|
will show how the matched field compares. The csvs should be checked for field consistency across the files
|
| 12 |
|
|
and a fieldCompare matrix will need to be created, since field names wont be the same.
|
| 13 |
|
|
Also there is almost ALWAYS pipes in the data, so you will either need to manually fix after or before OR start using csv lib.
|
| 14 |
|
|
|
| 15 |
|
|
"""
|
| 16 |
|
|
|
| 17 |
|
|
import os
|
| 18 |
|
|
|
| 19 |
|
|
def CopyProdCompare(prodValue,copyValue):
|
| 20 |
|
|
if prodValue == copyValue:
|
| 21 |
|
|
textValue = "YES"
|
| 22 |
|
|
elif prodValue == "":
|
| 23 |
|
|
if copyValue == "":
|
| 24 |
|
|
textValue = "NO - BothEmpty"
|
| 25 |
|
|
else:
|
| 26 |
|
|
textValue = "NO - ProdValueMissing"
|
| 27 |
|
|
elif copyValue == "":
|
| 28 |
|
|
textValue = "NO - CopyValueMissing"
|
| 29 |
|
|
else:
|
| 30 |
|
|
textValue = "No - DifferentValues"
|
| 31 |
|
|
return textValue
|
| 32 |
|
|
|
| 33 |
|
|
if __name__ == '__main__':
|
| 34 |
|
|
#outputFileName = r"C:\Users\eborges\Documents\Cases\Endo\20220207 - SIF2010Copy Data Compare\testMatch_2008-2012_coded-2.csv"
|
| 35 |
|
|
outputFileName = r"C:\Test_Dir\t\foo2.csv"
|
| 36 |
|
|
## Coinc that this is the same index for both sets of files
|
| 37 |
|
|
keyIndex = 2
|
| 38 |
|
|
#copyStartDir = r"C:\Users\eborges\Documents\Cases\Endo\20220207 - SIF2010Copy Data Compare\2010Achive_csvVersion"
|
| 39 |
|
|
copyStartDir = r"C:\Users\eborges\Documents\Cases\Endo\Temp\2022.02.09 Aspect Delivery - SIF2010Copy-SIF2008-2012 Exports for compare\SIF2010Copy\csv_version"
|
| 40 |
|
|
#copyStartDir = r"C:\Test_Dir\t\t2"
|
| 41 |
|
|
#prodStartDir = r"C:\Users\eborges\Documents\Cases\Endo\Temp\2021.07.19 Aspect Delivery - SIF2007Backup files\ACCDB converted to Excel by Redgrave for production\csv_version"
|
| 42 |
|
|
prodStartDir = r"C:\Users\eborges\Documents\Cases\Endo\Temp\2022.02.09 Aspect Delivery - SIF2010Copy-SIF2008-2012 Exports for compare\SIF2008-2012\csv_version"
|
| 43 |
|
|
#prodStartDir = r"C:\Test_Dir\t\t1"
|
| 44 |
|
|
dupList = []
|
| 45 |
|
|
headders = {}
|
| 46 |
|
|
callIDMatrix = {}
|
| 47 |
|
|
|
| 48 |
|
|
dupListProd = []
|
| 49 |
|
|
headdersProd = {}
|
| 50 |
|
|
callIDMatrixProd = {}
|
| 51 |
|
|
|
| 52 |
|
|
## Ingest the 2010 Copy files
|
| 53 |
|
|
for f in os.listdir(copyStartDir):
|
| 54 |
|
|
print("Now ingesting %s ..."%f)
|
| 55 |
|
|
contents = open(os.path.join(copyStartDir,f),'r',encoding='cp850').readlines()
|
| 56 |
|
|
headder = contents[0]
|
| 57 |
|
|
contents = contents[1:]
|
| 58 |
|
|
headders[headder] = 1
|
| 59 |
|
|
for line in contents:
|
| 60 |
|
|
line = line.replace("\n","")
|
| 61 |
|
|
line = line.upper()
|
| 62 |
|
|
line = line.split("|")
|
| 63 |
|
|
try:
|
| 64 |
|
|
callIDMatrix[line[keyIndex]].append(line)
|
| 65 |
|
|
dupList.append(line[keyIndex])
|
| 66 |
|
|
except:
|
| 67 |
|
|
callIDMatrix[line[keyIndex]] = [line,]
|
| 68 |
|
|
print("Done. %s duplicates"%len(dupList))
|
| 69 |
|
|
print("Current count is %s"% len(callIDMatrix.keys()))
|
| 70 |
|
|
print("Final headder count is %s"%len(headders.keys()))
|
| 71 |
|
|
print("----")
|
| 72 |
|
|
|
| 73 |
|
|
## Ingest the produciton files
|
| 74 |
|
|
for f in os.listdir(prodStartDir):
|
| 75 |
|
|
print("Now ingesting %s ..."%f)
|
| 76 |
|
|
contents = open(os.path.join(prodStartDir,f),'r',encoding='cp850').readlines()
|
| 77 |
|
|
headderProd = contents[0]
|
| 78 |
|
|
contents = contents[1:]
|
| 79 |
|
|
headdersProd[headderProd] = 1
|
| 80 |
|
|
for line in contents:
|
| 81 |
|
|
line = line.replace("\n","")
|
| 82 |
|
|
line = line.upper()
|
| 83 |
|
|
line = line.split("|")
|
| 84 |
|
|
try:
|
| 85 |
|
|
callIDMatrixProd[line[keyIndex]].append(line)
|
| 86 |
|
|
dupListProd.append(line[keyIndex])
|
| 87 |
|
|
except:
|
| 88 |
|
|
callIDMatrixProd[line[keyIndex]] = [line,]
|
| 89 |
|
|
print("Done. %s duplicates"%len(dupListProd))
|
| 90 |
|
|
print("Current count is %s"% len(callIDMatrixProd.keys()))
|
| 91 |
|
|
print("Final headder count is %s"%len(headdersProd.keys()))
|
| 92 |
|
|
print("----")
|
| 93 |
|
|
|
| 94 |
|
|
callIDList = list(callIDMatrix.keys())
|
| 95 |
|
|
callIDProdList = list(callIDMatrixProd.keys())
|
| 96 |
|
|
|
| 97 |
|
|
uniqueToProd = list(set(callIDProdList) - set(callIDList))
|
| 98 |
|
|
print("There are %s records that are unique to Production"% len(uniqueToProd))
|
| 99 |
|
|
outputFile = open(r"C:\Users\eborges\Documents\Cases\Endo\20220207 - SIF2010Copy Data Compare\UniqueToProd_2008-2012.csv",'w')
|
| 100 |
|
|
for i in uniqueToProd:
|
| 101 |
|
|
nl = outputFile.write("%s\n"%i)
|
| 102 |
|
|
outputFile.close()
|
| 103 |
|
|
uniqueToCopy = list(set(callIDList) - set(callIDProdList))
|
| 104 |
|
|
print("There are %s records that are unique to Copy"% len(uniqueToCopy))
|
| 105 |
|
|
matchedItems = list(set(callIDList) & set(callIDProdList))
|
| 106 |
|
|
print("There are %s records that are matches and these will be analyzed."% len(matchedItems))
|
| 107 |
|
|
|
| 108 |
|
|
outputFile = open(outputFileName,'w')
|
| 109 |
|
|
print("Writing report...")
|
| 110 |
|
|
|
| 111 |
|
|
|
| 112 |
|
|
## for id in matchedItems:
|
| 113 |
|
|
## nl = outputFile.write("%s|"%(callIDMatrixProd[id][0][0]))
|
| 114 |
|
|
## nl = outputFile.write("%s|"%(callIDMatrix[id][0][0]))
|
| 115 |
|
|
## nl = outputFile.write("%s|"%(CopyProdCompare(callIDMatrixProd[id][0][0], callIDMatrix[id][0][0])))
|
| 116 |
|
|
## nl = outputFile.write("%s|"%(callIDMatrixProd[id][0][2]))
|
| 117 |
|
|
## nl = outputFile.write("%s|"%(callIDMatrix[id][0][2]))
|
| 118 |
|
|
## nl = outputFile.write("%s|"%(CopyProdCompare(callIDMatrixProd[id][0][2], callIDMatrix[id][0][2])))
|
| 119 |
|
|
## nl = outputFile.write("%s|"%(callIDMatrixProd[id][0][4]))
|
| 120 |
|
|
## nl = outputFile.write("%s|"%(callIDMatrix[id][0][4]))
|
| 121 |
|
|
## nl = outputFile.write("%s|"%(CopyProdCompare(callIDMatrixProd[id][0][4], callIDMatrix[id][0][4])))
|
| 122 |
|
|
## nl = outputFile.write("%s|"%(callIDMatrixProd[id][0][9]))
|
| 123 |
|
|
## nl = outputFile.write("%s|"%(callIDMatrix[id][0][8]))
|
| 124 |
|
|
## nl = outputFile.write("%s|"%(CopyProdCompare(callIDMatrixProd[id][0][9], callIDMatrix[id][0][8])))
|
| 125 |
|
|
## nl = outputFile.write("%s|"%(callIDMatrixProd[id][0][8]))
|
| 126 |
|
|
## nl = outputFile.write("%s|"%(callIDMatrix[id][0][9]))
|
| 127 |
|
|
## nl = outputFile.write("%s|"%(CopyProdCompare(callIDMatrixProd[id][0][8], callIDMatrix[id][0][9])))
|
| 128 |
|
|
## nl = outputFile.write("%s|"%(callIDMatrixProd[id][0][18]))
|
| 129 |
|
|
## nl = outputFile.write("%s|"%(callIDMatrix[id][0][19]))
|
| 130 |
|
|
## nl = outputFile.write("%s|"%(CopyProdCompare(callIDMatrixProd[id][0][18], callIDMatrix[id][0][19])))
|
| 131 |
|
|
## nl = outputFile.write("%s|"%(callIDMatrixProd[id][0][20]))
|
| 132 |
|
|
## nl = outputFile.write("%s|"%(callIDMatrixProd[id][0][21]))
|
| 133 |
|
|
## nl = outputFile.write("%s|"%(callIDMatrixProd[id][0][22]))
|
| 134 |
|
|
## nl = outputFile.write("%s|"%(callIDMatrix[id][0][23]))
|
| 135 |
|
|
## nl = outputFile.write("\n")
|
| 136 |
|
|
|
| 137 |
|
|
for id in matchedItems:
|
| 138 |
|
|
nl = outputFile.write("%s|"%(callIDMatrixProd[id][0][0]))
|
| 139 |
|
|
nl = outputFile.write("%s|"%(callIDMatrix[id][0][0]))
|
| 140 |
|
|
nl = outputFile.write("%s|"%(CopyProdCompare(callIDMatrixProd[id][0][0], callIDMatrix[id][0][0])))
|
| 141 |
|
|
nl = outputFile.write("%s|"%(callIDMatrixProd[id][0][2]))
|
| 142 |
|
|
nl = outputFile.write("%s|"%(callIDMatrix[id][0][2]))
|
| 143 |
|
|
nl = outputFile.write("%s|"%(CopyProdCompare(callIDMatrixProd[id][0][2], callIDMatrix[id][0][2])))
|
| 144 |
|
|
nl = outputFile.write("%s|"%(callIDMatrixProd[id][0][4]))
|
| 145 |
|
|
nl = outputFile.write("%s|"%(callIDMatrix[id][0][4]))
|
| 146 |
|
|
nl = outputFile.write("%s|"%(CopyProdCompare(callIDMatrixProd[id][0][4], callIDMatrix[id][0][4])))
|
| 147 |
|
|
nl = outputFile.write("%s|"%(callIDMatrixProd[id][0][9]))
|
| 148 |
|
|
nl = outputFile.write("%s|"%(callIDMatrix[id][0][9]))
|
| 149 |
|
|
nl = outputFile.write("%s|"%(CopyProdCompare(callIDMatrixProd[id][0][9], callIDMatrix[id][0][9])))
|
| 150 |
|
|
nl = outputFile.write("%s|"%(callIDMatrixProd[id][0][8]))
|
| 151 |
|
|
nl = outputFile.write("%s|"%(callIDMatrix[id][0][8]))
|
| 152 |
|
|
nl = outputFile.write("%s|"%(CopyProdCompare(callIDMatrixProd[id][0][8], callIDMatrix[id][0][8])))
|
| 153 |
|
|
nl = outputFile.write("%s|"%(callIDMatrixProd[id][0][10]))
|
| 154 |
|
|
nl = outputFile.write("%s|"%(callIDMatrix[id][0][10]))
|
| 155 |
|
|
nl = outputFile.write("%s|"%(CopyProdCompare(callIDMatrixProd[id][0][10], callIDMatrix[id][0][10])))
|
| 156 |
|
|
nl = outputFile.write("%s|"%(callIDMatrixProd[id][0][19]))
|
| 157 |
|
|
nl = outputFile.write("%s|"%(callIDMatrix[id][0][19]))
|
| 158 |
|
|
nl = outputFile.write("%s|"%(CopyProdCompare(callIDMatrixProd[id][0][19], callIDMatrix[id][0][19])))
|
| 159 |
|
|
nl = outputFile.write("%s|"%(callIDMatrixProd[id][0][23]))
|
| 160 |
|
|
nl = outputFile.write("%s|"%(callIDMatrix[id][0][23]))
|
| 161 |
|
|
nl = outputFile.write("\n")
|
| 162 |
|
|
|
| 163 |
|
|
|
| 164 |
|
|
outputFile.close()
|
| 165 |
|
|
print("Report Finished.") |
