def createVlookupDelta(leftCsvPath, rightCsvPath, outputPath, matchColumn, dataColumn):
xlsxOutputMode = False
if os.path.splitext(outputPath)[1] == ".xlsx":
xlsxOutputMode = True
lineCount = 0
lineCountRight = 0
with open(leftCsvPath, 'r') as leftFile:
for lineNumber, text in enumerate(leftFile):
pass
lineCount = lineNumber + 1
with open(rightCsvPath, 'r') as rightFile:
for lineNumber, text in enumerate(rightFile):
pass
lineCountRight = lineNumber + 1
if lineCount != lineCountRight:
print("Files differ in line count, abort")
return
with open(leftCsvPath, 'r') as leftFile:
with open(rightCsvPath, 'r') as rightFile:
if xlsxOutputMode == False:
outputFile = open(outputPath, 'w')
else:
outputFile = Workbook(outputPath, {'strings_to_numbers': True})
worksheet = outputFile.add_worksheet("delta")
leftReader = csv.reader(leftFile)
rightReader = csv.reader(rightFile)
for leftLine,rightLine,lineNumber in zip(leftReader,rightReader,range(1, lineCount)):
if lineNumber == 1:
firstRow = False
if xlsxOutputMode == False:
outputFile.write("ID,Left,Right,%,Abs Diff,,Right ID,Right Data\n")
else:
worksheet.write(lineNumber-1, 0, "ID")
worksheet.write(lineNumber-1, 1, "Left")
worksheet.write(lineNumber-1, 2, "Right")
worksheet.write(lineNumber-1, 3, "%")
worksheet.write(lineNumber-1, 4, "Abs Diff")
worksheet.write(lineNumber-1, 5, "")
worksheet.write(lineNumber-1, 6, "Right ID")
worksheet.write(lineNumber-1, 7, "Right Data")
continue
if xlsxOutputMode == False:
outputList = []
outputList.append(leftLine[_convertColumnToZeroOffset(matchColumn)])
outputList.append(leftLine[_convertColumnToZeroOffset(dataColumn)])
outputList.append("\"=VLOOKUP(A{0},G$2:H${1},2,FALSE)\"".format(lineNumber, lineCount))
outputList.append("\"=C{0}/B{0}\"".format(lineNumber))
outputList.append("\"=C{0}-B{0}\"".format(lineNumber))
outputList.append("")
outputList.append(rightLine[_convertColumnToZeroOffset(matchColumn)])
outputList.append(rightLine[_convertColumnToZeroOffset(dataColumn)])
_writeListToCsvFile(outputFile, outputList)
else:
worksheet.write(lineNumber-1, 0, leftLine[_convertColumnToZeroOffset(matchColumn)])
worksheet.write(lineNumber-1, 1, leftLine[_convertColumnToZeroOffset(dataColumn)])
worksheet.write(lineNumber-1, 2, "=VLOOKUP(A{0},G$2:H${1},2,FALSE)".format(lineNumber, lineCount))
worksheet.write(lineNumber-1, 3, "=C{0}/B{0}".format(lineNumber))
worksheet.write(lineNumber-1, 4, "=C{0}-B{0}".format(lineNumber))
worksheet.write(lineNumber-1, 5, "")
worksheet.write(lineNumber-1, 6, rightLine[_convertColumnToZeroOffset(matchColumn)])
worksheet.write(lineNumber-1, 7, rightLine[_convertColumnToZeroOffset(dataColumn)])
outputFile.close()
