| 1 |
nino.borges |
777 |
"""
|
| 2 |
|
|
|
| 3 |
|
|
EndoConcurDataMerge
|
| 4 |
|
|
|
| 5 |
|
|
Created by:
|
| 6 |
|
|
Emanuel Borges
|
| 7 |
|
|
05.25.2022
|
| 8 |
|
|
|
| 9 |
|
|
This program will inspect and then combine a large set of spreadsheets with multiple tabs of Concur export data.
|
| 10 |
|
|
This is to compare this new merger with a previous merger performed by endo/apks.
|
| 11 |
|
|
|
| 12 |
|
|
"""
|
| 13 |
|
|
|
| 14 |
|
|
import MyCode.Tool_Box.ExcelLib
|
| 15 |
|
|
import os
|
| 16 |
|
|
|
| 17 |
|
|
|
| 18 |
|
|
def LastRowTest(shtNumber, rowNumber, colNumber):
|
| 19 |
|
|
pass
|
| 20 |
|
|
|
| 21 |
|
|
def LastColTest(shtNumber, rowNumber, colNumber):
|
| 22 |
|
|
pass
|
| 23 |
|
|
|
| 24 |
|
|
|
| 25 |
|
|
if __name__ == '__main__':
|
| 26 |
|
|
startDir = r"C:\Users\eborges\Documents\Cases\Endo\Temp\2021.10.20 Big Blue NCDS Datasets to Transition\Big Blue (noncustodial) Datasets_To Transition\Concur_Transition\Client Originals - DO NOT USE"
|
| 27 |
|
|
mannyAllYearsRowCount = 0
|
| 28 |
|
|
fileSkipList = ["Thumbs.db"]
|
| 29 |
|
|
|
| 30 |
|
|
|
| 31 |
|
|
## open the excel and get the number of tabs (sheets)
|
| 32 |
|
|
## find the headder row
|
| 33 |
|
|
## confirm that the headder row is the same across all
|
| 34 |
|
|
## find the last row
|
| 35 |
|
|
## run a test taht the last row actually has data and if not, test the row(s) before until you find the last row.
|
| 36 |
|
|
## run a test for 5 rows after the original last row to confirm that you didnt undershoot
|
| 37 |
|
|
## test the last column too
|
| 38 |
|
|
|
| 39 |
|
|
|
| 40 |
|
|
|
| 41 |
|
|
|
| 42 |
|
|
|
| 43 |
|
|
for xlFile in os.listdir(startDir):
|
| 44 |
|
|
if xlFile in fileSkipList:
|
| 45 |
|
|
pass
|
| 46 |
|
|
else:
|
| 47 |
|
|
print("-"*50)
|
| 48 |
|
|
print("Now opening %s"%xlFile)
|
| 49 |
|
|
xlBook = MyCode.Tool_Box.ExcelLib.ExcelConnection(os.path.join(startDir,xlFile))
|
| 50 |
|
|
shtCount = xlBook.xlBook.Worksheets.Count
|
| 51 |
|
|
print("There are %s sheets in %s"%(str(shtCount),xlFile))
|
| 52 |
|
|
totalSheetChartRowCount = 0
|
| 53 |
|
|
for shtNumber in range(shtCount):
|
| 54 |
|
|
if xlBook.getCell(shtNumber+1, 5,1) == "Employee":
|
| 55 |
|
|
print("Header row test passed.")
|
| 56 |
|
|
else:
|
| 57 |
|
|
print("Header row test FAILED!!!")
|
| 58 |
|
|
sht = xlBook.xlBook.Worksheets(shtNumber+1)
|
| 59 |
|
|
print("The last Row is %s"%str(sht.UsedRange.Rows.Count))
|
| 60 |
|
|
if xlBook.getCell(shtNumber+1, sht.UsedRange.Rows.Count,1):
|
| 61 |
|
|
pass
|
| 62 |
|
|
else:
|
| 63 |
|
|
print("WARNING: OverCount Found!")
|
| 64 |
|
|
chartRowCount = sht.UsedRange.Rows.Count
|
| 65 |
|
|
chartRowCount = chartRowCount - 5
|
| 66 |
|
|
print("There are %s rows on the chart."% str(chartRowCount))
|
| 67 |
|
|
totalSheetChartRowCount = totalSheetChartRowCount + chartRowCount
|
| 68 |
|
|
xlBook.close()
|
| 69 |
|
|
print("There are %s total chart rows on file %s."% (str(totalSheetChartRowCount),xlFile))
|
| 70 |
|
|
mannyAllYearsRowCount = mannyAllYearsRowCount+ totalSheetChartRowCount
|
| 71 |
|
|
print("-"*50)
|
| 72 |
|
|
print("\n\n")
|
| 73 |
|
|
print("There are a total of %s rows across all xls files."% str(mannyAllYearsRowCount)) |
