def main():
# Define PumpData sheet object
#wb = xw.Book('ESP_config.xlsm')
wb = xw.Book.caller()
pmpDataSht = wb.sheets["PumpData"]
tmpSht = wb.sheets["TempSheet"]
# Import Shop data as an array (list of lists)
rowSHO = cF.findHeader(pmpDataSht, 'SHOP GROUP')
rowEnd = cF.findHeader(pmpDataSht, 'SHAFT')
colEnd = pmpDataSht[rowSHO + 1, 0].end('right').column
rngSHO = pmpDataSht[rowSHO + 1:rowEnd, 0:colEnd].value
# Determine values for necessary column
colItemN = rngSHO[0].index("Item Notes")
colDesc = rngSHO[0].index("Description")
colItemID = rngSHO[0].index("Item ID")
# Build BOM elements
strShopItemID = rngSHO[1][colItemID]
strShopDescription = rngSHO[1][colDesc]
strShopItemNotes = rngSHO[1][colItemN]
# Write to TempSheet
tmpSht[0, 0].value = strShopItemID
tmpSht[0, 1].value = strShopDescription
tmpSht[0, 2].value = strShopItemNotes
def main():
# Define PumpData sheet object
#wb = xw.Book('ESP_config.xlsm')
wb = xw.Book.caller()
pmpDataSht = wb.sheets["PumpData"]
# Import Shipping data as an array (list of lists)
rowSHP = cF.findHeader(pmpDataSht, 'SHIPPING GROUP')
rowEnd = cF.findHeader(pmpDataSht, 'COAT/PAINT GROUP')
colEnd = pmpDataSht[rowSHP+1,0].end('right').column
rngSHP = pmpDataSht[rowSHP+1:rowEnd,0:colEnd].options(empty='').value
# Determine values for necessary column
colSeries = rngSHP[0].index("Series")
colItemN = rngSHP[0].index("Item Notes")
colDesc = rngSHP[0].index("Description")
# Define strings to match in description of the SHIPPING group (and item notes)
searchPattern = re.compile("T.")
# Main loop that populates the array (list of lists) for SHIPPING group data
for rowi in range(1,len(rngSHP)):
desc = rngSHP[rowi][colDesc]
itemN = rngSHP[rowi][colItemN]
shipExtDesc = desc + " " + itemN
# Search using regular expressions
descMatch = re.search(searchPattern, shipExtDesc)
if descMatch:
# Series
rngSHP[rowi][colSeries] = descMatch.group(0)
# Export modified SHIPPING data back to worksheet
pmpDataSht[rowSHP+2,0].value = rngSHP[1:]
def main():
# Define PumpData sheet object
wb = xw.Book.caller()
pmpDataSht = wb.sheets["PumpData"]
# Import EXISTING ESP PUMP data as an array (list of lists)
rowPMP = cF.findHeader(pmpDataSht, 'EXISTING ESP PUMPS')
rowEnd = cF.findHeader(pmpDataSht, 'COMMON')
colEnd = pmpDataSht[rowPMP + 1, 0].end('right').column
rngPMP = pmpDataSht[rowPMP + 1:rowEnd, 0:colEnd].value
# Determine values for necessary column
colSeries = rngPMP[0].index("Series")
colBPD = rngPMP[0].index("BPD")
colType = rngPMP[0].index("Type")
colHsgNum = rngPMP[0].index("Housing Number")
colStgCount = rngPMP[0].index("Stage Count")
colDesc = rngPMP[0].index("Description")
# Define strings to match in description of pumps
searchPattern1 = re.compile(
"PU?MP,(?P<series>T.) ?(?P<BPD>\d+) (?P<stgtype>[a-zA-Z]+) (?P<stgcount>\d+)STG \#(?P<hsgnum>\d+)"
)
searchPattern2 = re.compile(
"PU?MP,(?P<series>T.) ?(?P<BPD>\d+) (?P<stgtype1>[a-zA-Z]+)[ -](?P<stgtype2>[a-zA-z]+) (?P<stgcount>\d+)/\d+B \#(?P<hsgnum>\d+)"
)
SPs = [searchPattern1, searchPattern2]
# Main loop that populates the array (list of lists) for PUMP data
for rowi in range(1, len(rngPMP)):
desc = rngPMP[rowi][colDesc]
# Search using regular expressions
descMatch = [re.search(SP, desc) for SP in SPs]
if any(descMatch):
ind = [i for i, v in enumerate(descMatch) if v][0]
# Series
rngPMP[rowi][colSeries] = descMatch[ind].group('series')
# BPD
rngPMP[rowi][colBPD] = descMatch[ind].group('BPD')
# Type
if ind == 0:
rngPMP[rowi][colType] = descMatch[ind].group('stgtype')
else:
rngPMP[rowi][colType] = descMatch[ind].group('stgtype1') + "-" + \
descMatch[ind].group('stgtype2')
# Housing number
rngPMP[rowi][colHsgNum] = descMatch[ind].group('hsgnum')
# Stage count
rngPMP[rowi][colStgCount] = descMatch[ind].group('stgcount')
# Export modified PUMP data back to worksheet
pmpDataSht[rowPMP + 2, 0].value = rngPMP[1:]
def main():
# Define PumpData sheet object
#wb = xw.Book('ESP_config.xlsm')
wb = xw.Book.caller()
pmpDataSht = wb.sheets["PumpData"]
# Import H&B GROUP data as an array (list of lists)
rowOR = cF.findHeader(pmpDataSht, 'O-RING GROUP')
rowEnd = cF.findHeader(pmpDataSht, 'SPACER GROUP')
colEnd = pmpDataSht[rowOR+1,0].end('right').column
rngOR = pmpDataSht[rowOR+1:rowEnd,0:colEnd].options(empty='').value
# Determine values for necessary column
colSeries = rngOR[0].index("Series")
colMat = rngOR[0].index("Material")
colDuro = rngOR[0].index("Durometer")
colTyp = rngOR[0].index("Type Designation")
colApp = rngOR[0].index("Application")
colItemN = rngOR[0].index("Item Notes")
colDesc = rngOR[0].index("Description")
# Define strings to match in description & item notes of Oring group
searchPattern = re.compile("(GROUP,P.*?MP) (T.)")
matList = ["AFL","HSN","VIT","CHEM","FFKM"]
duroList = ["75","80","90"]
typeList = ["CMP","FLT"]
appList = ["UHP","XHT"]
# Main loop that populates the array (list of lists) for oring group data
for rowi in range(1,len(rngOR)):
desc = rngOR[rowi][colDesc]
itemN = rngOR[rowi][colItemN]
orExtDesc = desc + " " + itemN
# Search using regular expressions
descMatch = re.search(searchPattern, orExtDesc)
if descMatch:
# Series
rngOR[rowi][colSeries] = descMatch.group(2)
# Material
rngOR[rowi][colMat] = ", ".join(orMat for orMat in matList if orMat in orExtDesc)
# Durometer
rngOR[rowi][colDuro] = ", ".join(dur for dur in duroList if dur in orExtDesc)
# Type designation
rngOR[rowi][colTyp] = ", ".join(typ for typ in typeList if typ in orExtDesc)
# Application
rngOR[rowi][colApp] = ", ".join(app for app in appList if app in orExtDesc)
# Export modified O-RING GROUP data back to worksheet
pmpDataSht[rowOR+2,0].value = rngOR[1:]
def main():
# Define PumpData sheet object
#wb = xw.Book('ESP_config.xlsm')
wb = xw.Book.caller()
pmpDataSht = wb.sheets["PumpData"]
tmpSht = wb.sheets["TempSheet"]
# Import Coat/paint data as an array (list of lists)
rowFIN = cF.findHeader(pmpDataSht, 'COAT/PAINT GROUP')
rowEnd = cF.findHeader(pmpDataSht, 'SHOP GROUP')
colEnd = pmpDataSht[rowFIN + 1, 0].end('right').column
rngFIN = pmpDataSht[rowFIN + 1:rowEnd, 0:colEnd].value
# Determine values for necessary column
colType = rngFIN[0].index("Category")
colItemN = rngFIN[0].index("Item Notes")
colDesc = rngFIN[0].index("Description")
colItemID = rngFIN[0].index("Item ID")
# Extract pump info from TempSheet and clear TempSheet
pmpFinish = tmpSht[0, 0].value
tmpSht[0, 0].value = ""
# Finish type designation
if pmpFinish == "Paint":
strFinishType = "PAINT"
elif pmpFinish == "Monel Coating":
strFinishType = "COAT"
# Initialize BOM elements
strFinishItemID = "NEW"
strFinishDescription = strFinishType
strFinishItemNotes = ""
# Find a part number matching the coat/paint group (if any existing)
for rowi in range(1, len(rngFIN)):
if strFinishType == rngFIN[rowi][colType]:
strFinishItemID = rngFIN[rowi][colItemID]
strFinishDescription = rngFIN[rowi][colDesc]
strFinishItemNotes = rngFIN[rowi][colItemN]
break
# Write to TempSheet
tmpSht[0, 0].value = strFinishItemID
tmpSht[0, 1].value = strFinishDescription
tmpSht[0, 2].value = strFinishItemNotes
def main():
# Define PumpData sheet object
wb = xw.Book.caller()
pmpDataSht = wb.sheets["PumpData"]
# Import COMMON (com parts) data as an array (list of lists)
rowCOM = cF.findHeader(pmpDataSht, 'COMMON')
rowEnd = cF.findHeader(pmpDataSht, 'STAGE GROUP')
colEnd = pmpDataSht[rowCOM+1,0].end('right').column
rngCOM = pmpDataSht[rowCOM+1:rowEnd,0:colEnd].options(empty='').value
# Determine values for necessary column
colSeries = rngCOM[0].index("Series")
colStgType = rngCOM[0].index("Stage Type")
colShaftSize = rngCOM[0].index("Shaft Size")
colDesignation = rngCOM[0].index("Designation")
colItemN = rngCOM[0].index("Item Notes")
colDesc = rngCOM[0].index("Description")
# Define strings to match in description of the COM PART group (and item notes)
searchPattern = re.compile("(COM PART,PU?MP) (?P<series>T.) (?P<stgtype1>[a-zA-Z]+)[ -](?P<stgtype2>[a-zA-Z]+) (?P<size>\.\d+)")
listDesig = ["UHP","NLP","WAG"]
# Main loop that populates the array (list of lists) for COM PART group data
for rowi in range(1,len(rngCOM)):
desc = rngCOM[rowi][colDesc]
itemN = rngCOM[rowi][colItemN]
comExtDesc = desc + " " + itemN
# Search using regular expressions
descMatch = re.search(searchPattern, comExtDesc)
if descMatch:
# Series
rngCOM[rowi][colSeries] = descMatch.group('series')
# Stage type
rngCOM[rowi][colStgType] = descMatch.group('stgtype1') + "-" + descMatch.group('stgtype2')
# Shaft size
rngCOM[rowi][colShaftSize] = descMatch.group('size')
# Designation
if descMatch.group('series') == "TA":
rngCOM[rowi][colDesignation] = "NLP"
else:
rngCOM[rowi][colDesignation] = ", ".join(dTyp for dTyp in listDesig if dTyp in comExtDesc)
# Export modified COMMON data back to worksheet
pmpDataSht[rowCOM+2,0].value = rngCOM[1:]
def main():
# Define PumpData sheet object
#wb = xw.Book('ESP_config.xlsm')
wb = xw.Book.caller()
pmpDataSht = wb.sheets["PumpData"]
# Import SPACER data as an array (list of lists)
rowSPC = cF.findHeader(pmpDataSht, 'SPACER GROUP')
rowEnd = cF.findHeader(pmpDataSht, 'SHIPPING GROUP')
colEnd = pmpDataSht[rowSPC + 1, 0].end('right').column
rngSPC = pmpDataSht[rowSPC + 1:rowEnd, 0:colEnd].options(empty='').value
# Determine values for necessary column
colShaftSize = rngSPC[0].index("Shaft Size")
colMat = rngSPC[0].index("Material")
colItemN = rngSPC[0].index("Item Notes")
colDesc = rngSPC[0].index("Description")
# Useful info
shaftList = ["5/8", "11/16", "1/2", "7/8", ".50", ".62", ".69", ".88"]
shaftdict = {"5/8":0.62, "11/16":0.69, "1/2":0.5, "7/8":0.88, ".50":0.5, \
".62": 0.62, ".69": 0.69, ".88": 0.88}
strMaterial = ["304", "N1"]
# Main loop that populates the array (list of lists) for COM PART group data
for rowi in range(1, len(rngSPC)):
desc = rngSPC[rowi][colDesc]
itemN = rngSPC[rowi][colItemN]
spcExtDesc = desc + " " + itemN
# Shaft size
for siz in shaftList:
if siz in spcExtDesc and siz in shaftdict:
rngSPC[rowi][colShaftSize] = shaftdict[siz]
# Material (standard is 304 going forward)
for mat in strMaterial:
if mat in spcExtDesc:
rngSPC[rowi][colMat] = mat
# Export modified O-RING GROUP data back to worksheet
pmpDataSht[rowSPC + 2, 0].value = rngSPC[1:]
def main():
# Define PumpData sheet object
#wb = xw.Book('ESP_config.xlsm')
wb = xw.Book.caller()
pmpDataSht = wb.sheets["PumpData"]
tmpSht = wb.sheets["TempSheet"]
# Import Shipping data as an array (list of lists)
rowSHP = cF.findHeader(pmpDataSht, 'SHIPPING GROUP')
rowEnd = cF.findHeader(pmpDataSht, 'COAT/PAINT GROUP')
colEnd = pmpDataSht[rowSHP + 1, 0].end('right').column
rngSHP = pmpDataSht[rowSHP + 1:rowEnd, 0:colEnd].options(empty='').value
# Determine values for necessary column
colSeries = rngSHP[0].index("Series")
colItemN = rngSHP[0].index("Item Notes")
colDesc = rngSHP[0].index("Description")
colItemID = rngSHP[0].index("Item ID")
# Extract pump info from TempSheet and clear TempSheet
pmpSeries = tmpSht[0, 0].value
tmpSht[0, 0].value = ""
# Initialize BOM elements
strShippingItemID = "NEW"
strShippingDescription = "GROUP,PMP " + pmpSeries + " SHIPPING COMPONENTS"
strShippingItemNotes = ""
# Find a part number matching the Shipping group (if any existing)
for rowi in range(1, len(rngSHP)):
if pmpSeries == rngSHP[rowi][colSeries]:
strShippingItemID = rngSHP[rowi][colItemID]
break
# Write to TempSheet
tmpSht[0, 0].value = strShippingItemID
tmpSht[0, 1].value = strShippingDescription
tmpSht[0, 2].value = strShippingItemNotes
def main():
# Define PumpData sheet object
wb = xw.Book.caller()
pmpDataSht = wb.sheets["PumpData"]
# Import COUPLING data as an array (list of lists)
rowCPLG = cF.findHeader(pmpDataSht, 'COUPLING')
rowEnd = pmpDataSht[rowCPLG + 1, 0].end('down').row
colEnd = pmpDataSht[rowCPLG + 1, 0].end('right').column
rngCPLG = pmpDataSht[rowCPLG + 1:rowEnd, 0:colEnd].value
# Determine values for necessary column
colSeries = rngCPLG[0].index("Series")
colStgType = rngCPLG[0].index("Stage Type")
colSize = rngCPLG[0].index("Size")
colMat = rngCPLG[0].index("Material")
colMS = rngCPLG[0].index("MS")
colItemN = rngCPLG[0].index("Item Notes")
colDesc = rngCPLG[0].index("Description")
# Define strings to match description of coupling (and item notes)
searchPattern = re.compile(
"CO?U?PLI?N?G,PU?MP (?P<series>T.) (?P<stgtype>\w+) (?P<size>\d+/\d+)")
matList = ["MNL", "NIT", "INC"]
# Main loop that populates the array (list of lists) for coupling data
for rowi in range(1, len(rngCPLG)):
desc = rngCPLG[rowi][colDesc]
itemN = rngCPLG[rowi][colItemN]
if itemN is None:
itemN = ""
cplgExtDesc = desc + " " + itemN
# Search description using regular expressions
descMatch = re.search(searchPattern, cplgExtDesc)
if descMatch:
# Series
rngCPLG[rowi][colSeries] = descMatch.group('series')
# Stage type
rngCPLG[rowi][colStgType] = descMatch.group('stgtype')
# Shaft size
rngCPLG[rowi][colSize] = descMatch.group('size')
# Material
rngCPLG[rowi][colMat] = ", ".join(cMat for cMat in matList
if cMat in cplgExtDesc)
# Export modified COUPLING data back to the worksheet
pmpDataSht[rowCPLG + 2, 0].value = rngCPLG[1:]
def main():
# Define PumpData sheet object
#wb = xw.Book('ESP_config.xlsm')
wb = xw.Book.caller()
pmpDataSht = wb.sheets["PumpData"]
tmpSht = wb.sheets["TempSheet"]
# Import Shaft data as an array (list of lists)
rowShft = cF.findHeader(pmpDataSht, 'SHAFT')
rowEnd = cF.findHeader(pmpDataSht, 'HOUSING')
colEnd = pmpDataSht[rowShft + 1, 0].end('right').column
rngShft = pmpDataSht[rowShft + 1:rowEnd, 0:colEnd].value
# Determine values for necessary column
colSeries = rngShft[0].index("Series")
colStgType = rngShft[0].index("Stage Type")
colShaftSize = rngShft[0].index("Shaft Size")
colShaftNum = rngShft[0].index("Shaft Number")
colMat = rngShft[0].index("Material")
colConn = rngShft[0].index("Connection Type")
colItemN = rngShft[0].index("Item Notes")
colDesc = rngShft[0].index("Description")
colItemID = rngShft[0].index("Item ID")
# Extract pump info from TempSheet and clear TempSheet
pmpSeries = tmpSht[0, 0].value
pmpStageType = tmpSht[1, 0].value
pmpShaftSize = tmpSht[2, 0].value
pmpShaftNum = tmpSht[3, 0].options(numbers=int).value
pmpShaftMat = tmpSht[4, 0].value
pmpConn = tmpSht[5, 0].value
tmpSht[0:6, 0].value = ""
# Useful transformations and associations
# 1. Stage type association
if pmpStageType in ["FLT", "AR-FLT", "AR-MDLR", "Q-PLUS"]:
strStageType = "AR-FLT"
else:
strStageType = "AR-CMP"
# 2. Shaft size to fraction format
if pmpShaftSize == 0.62:
strShaftSize = "5/8"
elif pmpShaftSize == 0.69:
strShaftSize = "11/16"
elif pmpShaftSize == 0.50:
strShaftSize = "1/2"
elif pmpShaftSize == 0.88:
strShaftSize = "7/8"
# 3. Shaft number to correct format
if pmpShaftNum < 10:
strShaftNum = "0" + str(pmpShaftNum)
else:
strShaftNum = str(pmpShaftNum)
# Initialize BOM elements
strShaftItemID = "NEW"
strShaftDescription = "SHAFT,PMP " + pmpSeries + " " + strStageType + " " + \
strShaftSize + " #" + strShaftNum + " " + pmpShaftMat + "*"
strShaftItemNotes = pmpConn
# Find a part number matching the SHAFT (if any existing)
for rowi in range(1, len(rngShft)):
if pmpSeries == rngShft[rowi][colSeries] and \
strStageType == rngShft[rowi][colStgType] and \
pmpShaftSize == rngShft[rowi][colShaftSize] and \
pmpShaftNum == rngShft[rowi][colShaftNum] and \
pmpShaftMat == rngShft[rowi][colMat] and \
pmpConn == rngShft[rowi][colConn]:
strShaftItemID = rngShft[rowi][colItemID]
break
# Write to TempSheet
tmpSht[0, 0].value = strShaftItemID
tmpSht[0, 1].value = strShaftDescription
tmpSht[0, 2].value = strShaftItemNotes
def main():
# Define PumpData sheet object
wb = xw.Book('ESP_config.xlsm')
#wb = xw.Book.caller()
pmpDataSht = wb.sheets["PumpData"]
tmpSht = wb.sheets["TempSheet"]
# Import H&B GROUP data as an array (list of lists)
rowHB = cF.findHeader(pmpDataSht, 'H&B GROUP')
rowEnd = cF.findHeader(pmpDataSht, 'O-RING GROUP')
colEnd = pmpDataSht[rowHB + 1, 0].end('right').column
rngHB = pmpDataSht[rowHB + 1:rowEnd, 0:colEnd].options(empty='').value
# Determine values for necessary column
colSeries = rngHB[0].index("Series")
colStgType = rngHB[0].index("Stage Type")
colShaftSize = rngHB[0].index("Shaft Size")
colHBMaterial = rngHB[0].index("H&B Material")
colConnectionType = rngHB[0].index("Connection Type")
colBSMaterial = rngHB[0].index("Bearing Support Material")
#colCoat = rngHB[0].index("Coating Type") # NO COATING DONE ON H&B ANYMORE
colTemp = rngHB[0].index("Temperature")
colItemN = rngHB[0].index("Item Notes")
colDesc = rngHB[0].index("Description")
colItemID = rngHB[0].index("Item ID")
# Extract pump info from TempSheet and clear TempSheet
pmpSeries = tmpSht[0, 0].value
pmpStageType = tmpSht[1, 0].value
pmpShaftSize = str(tmpSht[2, 0].value)
pmpHBMaterial = tmpSht[3, 0].value
pmpConnectionType = tmpSht[4, 0].value
pmpStagesMaterial = tmpSht[5, 0].value
pmpAppType = tmpSht[6, 0].value
tmpSht[0:7, 0].value = ""
# Stage type association for H&B group
if pmpStageType in ["FLT", "AR-FLT"]:
strHBStageType = "AR-FLT"
elif pmpStageType in ["CMP", "AR-CMP"]:
strHBStageType = "AR-CMP"
else:
strHBStageType = "AR-MDLR"
# Temperature rating string
if pmpAppType == "XHT":
strTemp = "XHT"
else:
strTemp = ""
# Shaft size converted to a string of the correct format for H&B group
shaftSizeMatch = re.search("\.\d+", pmpShaftSize)
strShaftSize = shaftSizeMatch.group(0)
# Initialize the BOM elements
strHBItemID = "NEW"
strHBDescription = "GROUP,PMP " + pmpSeries + " " + strHBStageType + " " + \
"H&B " + strShaftSize + "*"
strHBItemNotes = pmpHBMaterial + " " + pmpConnectionType + " " + pmpStagesMaterial
# Find a part number matching the H&B group (if any existing)
for rowi in range(1, len(rngHB)):
if pmpSeries == rngHB[rowi][colSeries] and \
strHBStageType[3:] in rngHB[rowi][colStgType] and \
strShaftSize in str(rngHB[rowi][colShaftSize]) and \
pmpHBMaterial == rngHB[rowi][colHBMaterial] and \
pmpConnectionType == rngHB[rowi][colConnectionType] and \
pmpStagesMaterial == rngHB[rowi][colBSMaterial] and \
strTemp == rngHB[rowi][colTemp]:
strHBItemID = rngHB[rowi][colItemID]
oldStgType = rngHB[rowi][colStgType]
if oldStgType == "AR-CMP/FLT":
newStgType = "AR-C/F"
else:
newStgType = oldStgType
strHBDescription = "GROUP,PMP " + pmpSeries + " " + newStgType \
+ " " + "H&B " + str(rngHB[rowi][colShaftSize]) + "*"
break
# Write to TempSheet
tmpSht[0, 0].value = strHBItemID
tmpSht[0, 1].value = strHBDescription
tmpSht[0, 2].value = strHBItemNotes
def main():
# Define PumpData sheet object
#wb = xw.Book('ESP_config.xlsm')
wb = xw.Book.caller()
pmpDataSht = wb.sheets["PumpData"]
# Import H&B GROUP data as an array (list of lists)
rowHB = cF.findHeader(pmpDataSht, 'H&B GROUP')
rowEnd = cF.findHeader(pmpDataSht, 'O-RING GROUP')
colEnd = pmpDataSht[rowHB + 1, 0].end('right').column
rngHB = pmpDataSht[rowHB + 1:rowEnd, 0:colEnd].options(empty='').value
# Determine values for necessary column
colSeries = rngHB[0].index("Series")
colStgType = rngHB[0].index("Stage Type")
colShaftSize = rngHB[0].index("Shaft Size")
colHBMaterial = rngHB[0].index("H&B Material")
colConnectionType = rngHB[0].index("Connection Type")
colBSMaterial = rngHB[0].index("Bearing Support Material")
#colCoat = rngHB[0].index("Coating Type") NO COATING DONE ON H&B anymore
colTemp = rngHB[0].index("Temperature")
colItemN = rngHB[0].index("Item Notes")
colDesc = rngHB[0].index("Description")
# Define strings to match in description of the H&B group (and item notes)
searchPattern1 = re.compile(
"(GROUP,P.*?MP) (?P<series>T.) (AR).(?P<stgtyp>[a-zA-Z/]+)")
searchPattern2 = re.compile("\.\d+")
HBMatList = ["4SS", "STL", "17-4", "DPX"]
BSM = ["N1", "N2", "N3", "N4", "DPX"]
coatType = ["NDP", "FPS", "CRP", "SF2"]
# Main loop that populates the array (list of lists) for H&B group data
for rowi in range(1, len(rngHB)):
desc = rngHB[rowi][colDesc]
itemN = rngHB[rowi][colItemN]
hbExtDesc = desc + " " + itemN
# Search using regular expressions
descMatch = re.search(searchPattern1, hbExtDesc)
if descMatch:
# Series
rngHB[rowi][colSeries] = descMatch.group('series')
# Stage type
sTyp = "AR-" + descMatch.group('stgtyp')
if sTyp == "AR-C/F":
sTyp = "AR-CMP/FLT"
rngHB[rowi][colStgType] = sTyp
# Shaft size
rngHB[rowi][colShaftSize] = "/".join(
re.findall(searchPattern2, hbExtDesc))
# H&B material
rngHB[rowi][colHBMaterial] = ", ".join(hbMat for hbMat in HBMatList
if hbMat in hbExtDesc)
# Connection type
if "UHP" in hbExtDesc:
connTyp = "UHP"
elif "BUT" in hbExtDesc:
connTyp = "BUT"
else:
connTyp = ""
rngHB[rowi][colConnectionType] = connTyp
# Bearing support material
rngHB[rowi][colBSMaterial] = ", ".join(bsType for bsType in BSM
if bsType in hbExtDesc)
# Temperature rating
if "XHT" in hbExtDesc:
rngHB[rowi][colTemp] = "XHT"
# Export modified H&B GROUP data back to worksheet
pmpDataSht[rowHB + 2, 0].value = rngHB[1:]
def main():
# Define PumpData sheet object
#wb = xw.Book('ESP_config.xlsm')
wb = xw.Book.caller()
pmpDataSht = wb.sheets["PumpData"]
tmpSht = wb.sheets["TempSheet"]
# Import H&B GROUP data as an array (list of lists)
rowOR = cF.findHeader(pmpDataSht, 'O-RING GROUP')
rowEnd = cF.findHeader(pmpDataSht, 'SPACER GROUP')
colEnd = pmpDataSht[rowOR + 1, 0].end('right').column
rngOR = pmpDataSht[rowOR + 1:rowEnd, 0:colEnd].options(empty='').value
# Determine values for necessary column
colSeries = rngOR[0].index("Series")
colMat = rngOR[0].index("Material")
colDuro = rngOR[0].index("Durometer")
colTyp = rngOR[0].index("Type Designation")
colApp = rngOR[0].index("Application")
colItemN = rngOR[0].index("Item Notes")
colDesc = rngOR[0].index("Description")
colItemID = rngOR[0].index("Item ID")
# Extract pump info from TempSheet and clear TempSheet
pmpSeries = tmpSht[0, 0].value
pmpStageType = tmpSht[1, 0].value
pmpOringMaterial = tmpSht[2, 0].value
pmpDuro = tmpSht[3, 0].options(numbers=int).value
pmpApp = tmpSht[4, 0].value
tmpSht[0:4, 0].value = ""
# Type designation & App designations
# 1. Type designation
if pmpStageType in ["FLT", "AR-FLT", "AR-MDLR", "Q-PLUS"]:
strTypeDesig = "FLT"
else:
strTypeDesig = "CMP"
# 2. Application type
if pmpApp in ["UHP", "XHT"]:
strApp = pmpApp
else:
strApp = ""
# Initialize the BOM elements
strOringItemID = "NEW"
strOringDescription = "GROUP,PMP " + pmpSeries + " O-RING " + pmpOringMaterial + \
" " + str(pmpDuro) + "*"
strOringItemNotes = strTypeDesig + " " + strApp
# Find a part number matching the O-RING group (if any existing)
for rowi in range(1, len(rngOR)):
if pmpSeries == rngOR[rowi][colSeries] and \
strTypeDesig == rngOR[rowi][colTyp] and \
pmpOringMaterial == rngOR[rowi][colMat] and \
pmpDuro == rngOR[rowi][colDuro] and \
strApp == rngOR[rowi][colApp]:
strOringItemID = rngOR[rowi][colItemID]
break
# Write to TempSheet
tmpSht[0, 0].value = strOringItemID
tmpSht[0, 1].value = strOringDescription
tmpSht[0, 2].value = strOringItemNotes
def main():
# Define PumpData sheet object
wb = xw.Book.caller()
pmpDataSht = wb.sheets["PumpData"]
tmpSht = wb.sheets["TempSheet"]
keySht = wb.sheets["KeystockTable"]
# Import KEYSTOCK data as an array (list of lists)
rowKEY = cF.findHeader(pmpDataSht, 'KEYSTOCK')
rowEnd = cF.findHeader(pmpDataSht, 'COUPLING')
colEnd = pmpDataSht[rowKEY + 1, 0].end('right').column
rngKEY = pmpDataSht[rowKEY + 1:rowEnd, 0:colEnd].value
# Determine values for necessary column
colMat = rngKEY[0].index("Material")
colItemID = rngKEY[0].index("Item ID")
# Extract pump info from TempSheet and clear TempSheet
pmpSeries = tmpSht[0, 0].value
pmpStageType = tmpSht[1, 0].value
pmpHousingNum = tmpSht[2, 0].options(numbers=int).value
pmpShaftSize = tmpSht[3, 0].value
pmpAppType = tmpSht[4, 0].value
tmpSht[0:5, 0].value = ""
# Useful associations
# 1. Keystock Material
if pmpStageType in ["AR-MDLR", "Q-PLUS"]:
strMat = "INC"
elif pmpAppType in ["UHP", "XHT"]:
strMat = "INC"
else:
strMat = "MNL"
# 2. Keystock length tables
lrow = keySht[0, 0].end('down').row
lcol = keySht[0, 0].end('right').column
rngTAB = keySht[0:lrow, 0:lcol].value
colSeries = rngTAB[0].index("Series")
colType = rngTAB[0].index("Type")
colSize = rngTAB[0].index("Size")
colHsgNum = rngTAB[0].index("Hsg Num")
colAval = rngTAB[0].index("A value")
if pmpSeries == "TA":
if pmpStageType in ["FLT", "AR-FLT", "AR-MDLR", "Q-PLUS"]:
strType = "FLT"
if pmpShaftSize == 0.62:
subtract_len = 2.028 + 2.580
else:
subtract_len = 2.225 + 2.830
else:
strType = "CMP"
if pmpShaftSize == 0.62:
subtract_len = 1.69 + 0.255 + 0.382 + 2.179
else:
subtract_len = 1.69 + 0.382 + 0.382 + 2.142
# FIX THIS!!!! (JUST TEMPORARY TO AVOID PYTHON ERRORS)
else:
subtract_len = 5
# Calculate keystock quantity
Aval = 10000 # default value for distinction
for rowi in range(1, len(rngTAB)):
if pmpSeries == rngTAB[rowi][colSeries] and \
strType == rngTAB[rowi][colType] and \
pmpHousingNum == rngTAB[rowi][colHsgNum] and \
pmpShaftSize == rngTAB[rowi][colSize]:
Aval = rngTAB[rowi][colAval]
break
intKeystockQuantity = ceil(Aval - subtract_len)
# Initialize BOM elements
strKeystockItemID = "NEW"
strKeystockDescription = "KEYSTOCK," + strMat + " 0.062 SQ (PMP)"
strKeystockItemNotes = ""
# Find a part number for KEYSTOCK data (if any existing)
for rowi in range(1, len(rngKEY)):
if strMat == rngKEY[rowi][colMat]:
strKeystockItemID = rngKEY[rowi][colItemID]
# Write to TempSheet
tmpSht[0, 0].value = strKeystockItemID
tmpSht[0, 1].value = strKeystockDescription
tmpSht[0, 2].value = strKeystockItemNotes
tmpSht[0, 3].value = intKeystockQuantity
def main():
# Define PumpData sheet object
wb = xw.Book.caller()
pmpDataSht = wb.sheets["PumpData"]
tmpSht = wb.sheets["TempSheet"]
# Import LOWER DIFFUSER data as an array (list of lists)
rowLDF = cF.findHeader(pmpDataSht, 'LOWER DIFFUSER')
rowEnd = cF.findHeader(pmpDataSht, 'KEYSTOCK')
colEnd = pmpDataSht[rowLDF + 1, 0].end('right').column
rngLDF = pmpDataSht[rowLDF + 1:rowEnd, 0:colEnd].value
# Determine values for necessary column
colSeries = rngLDF[0].index("Series")
colBPD = rngLDF[0].index("BPD")
colType1 = rngLDF[0].index("Type 1")
colType2 = rngLDF[0].index("Type 2")
colMat = rngLDF[0].index("Material")
colCoat = rngLDF[0].index("Coating Material")
colMS = rngLDF[0].index("MS")
colItemN = rngLDF[0].index("Item Notes")
colDesc = rngLDF[0].index("Description")
colItemID = rngLDF[0].index("Item ID")
# Extract pump info from TempSheet and clear TempSheet
pmpSeries = tmpSht[0, 0].value
pmpCapacity = str(tmpSht[1, 0].options(numbers=int).value)
pmpStageType = tmpSht[2, 0].value
pmpStagesMaterial = tmpSht[3, 0].value
pmpCoatingMaterial = tmpSht[4, 0].value
tmpSht[0:5, 0].value = ""
# Other useful associations
# 1. Stage type
if pmpStageType in ["FLT", "AR-FLT", "AR-MDLR"]:
strStageType = "FLT"
else:
strStageType = "CMP"
# 2. Coating type (if applicable)
if pmpCoatingMaterial != "N/A":
boolCoat = True
strCoating = pmpCoatingMaterial
else:
boolCoat = False
strCoating = None
# Initialize BOM elements
strLowerDiffuserItemID = "NEW"
strLowerDiffuserDescription = "DIFFUSER, LWR " + pmpSeries + pmpCapacity + \
" " + strStageType + " Type2 " + pmpStagesMaterial + "*"
if boolCoat:
strLowerDiffuserItemNotes = strCoating
else:
strLowerDiffuserItemNotes = ""
# Find a part number for LOWER DIFFUSER data (if any existing)
for rowi in range(1, len(rngLDF)):
if pmpSeries == rngLDF[rowi][colSeries] and \
pmpCapacity in rngLDF[rowi][colBPD] and \
strStageType in rngLDF[rowi][colType1] and \
pmpStagesMaterial == rngLDF[rowi][colMat] and \
strCoating == rngLDF[rowi][colCoat]:
strLowerDiffuserItemID = rngLDF[rowi][colItemID]
strLowerDiffuserDescription = strLowerDiffuserDescription.replace(\
pmpCapacity, rngLDF[rowi][colBPD])
oldStgType = rngLDF[rowi][colType1]
if oldStgType == "CMP/FLT":
newStgType = "C/F"
strLowerDiffuserDescription = strLowerDiffuserDescription.replace(\
strStageType, newStgType)
type2Val = rngLDF[rowi][colType2]
if type2Val != None:
strLowerDiffuserDescription = strLowerDiffuserDescription.replace(\
"Type2", type2Val)
break
# If no part number found, replace Type2 in description with appropriate Type 2 value
if strLowerDiffuserItemID == "NEW":
if boolCoat and strCoating != "NDP":
strLowerDiffuserDescription = strLowerDiffuserDescription.replace(\
"Type2", "P-MACH")
else:
strLowerDiffuserDescription = strLowerDiffuserDescription.replace(\
"Type2", "MACH")
# Write to TempSheet
tmpSht[0, 0].value = strLowerDiffuserItemID
tmpSht[0, 1].value = strLowerDiffuserDescription
tmpSht[0, 2].value = strLowerDiffuserItemNotes
def main():
# Define PumpData sheet object
wb = xw.Book.caller()
pmpDataSht = wb.sheets["PumpData"]
# Import HOUSING data as an array (list of lists)
rowHSG = cF.findHeader(pmpDataSht, 'HOUSING')
rowEnd = cF.findHeader(pmpDataSht, 'LOWER DIFFUSER')
colEnd = pmpDataSht[rowHSG + 1, 0].end('right').column
rngHSG = pmpDataSht[rowHSG + 1:rowEnd, 0:colEnd].value
# Determine values for necessary column
colSeries = rngHSG[0].index("Series")
colType = rngHSG[0].index("Type Association")
colHsgNum = rngHSG[0].index("Housing Number")
colMat = rngHSG[0].index("Material")
colMS = rngHSG[0].index("MS")
colConn = rngHSG[0].index("Connection Type")
colItemN = rngHSG[0].index("Item Notes")
colDesc = rngHSG[0].index("Description")
# Define strings to match in description of the HOUSING group (and item notes)
searchPattern = re.compile(
"(HOUSING,P.?MP) (?P<series>T.) (?P<typ>\w+)? ?\#(?P<hsg_num>\d+)")
hsgMatList = ["T-9", "13cr", "STL", "DPX"]
hsgMatDict = {'MS-1111-03': 'STL', 'MS-1122-02': 'T9'}
connList = ["BUT", "UHP"]
# Main loop that populates the array (list of lists) for HOUSING data
for rowi in range(1, len(rngHSG)):
desc = rngHSG[rowi][colDesc]
itemN = rngHSG[rowi][colItemN]
if itemN is None:
itemN = ""
hsgExtDesc = desc + " " + itemN
# Search using regular expressions
descMatch = re.search(searchPattern, hsgExtDesc)
if descMatch:
# Series
rngHSG[rowi][colSeries] = descMatch.group('series')
# Type association
if descMatch.group('typ') is not None:
rngHSG[rowi][colType] = descMatch.group('typ')
else:
rngHSG[rowi][colType] = "FLT"
# Housing number
rngHSG[rowi][colHsgNum] = descMatch.group('hsg_num')
# Material
if rngHSG[rowi][colMS] != "":
matKey = rngHSG[rowi][colMS][0:10]
rngHSG[rowi][colMat] = hsgMatDict[matKey]
else:
rngHSG[rowi][colMat] = ", ".join(hsgMat for hsgMat in hsgMatList
if hsgMat in hsgExtDesc)
# Connection type (type of housing)
if any(conn in hsgExtDesc for conn in connList):
rngHSG[rowi][colConn] = ", ".join(conn for conn in connList
if conn in hsgExtDesc)
# Export modified HOUSING data back to worksheet
pmpDataSht[rowHSG + 2, 0].value = rngHSG[1:]
def main():
# Define PumpData sheet object
wb = xw.Book.caller()
pmpDataSht = wb.sheets["PumpData"]
# Import SHAFT data as an array (list of lists)
rowShft = cF.findHeader(pmpDataSht, 'SHAFT')
rowEnd = cF.findHeader(pmpDataSht, 'HOUSING')
colEnd = pmpDataSht[rowShft + 1, 0].end('right').column
rngShft = pmpDataSht[rowShft + 1:rowEnd, 0:colEnd].options(empty='').value
# Determine values for necessary column
colSeries = rngShft[0].index("Series")
colStgType = rngShft[0].index("Stage Type")
colShaftSize = rngShft[0].index("Shaft Size")
colShaftNum = rngShft[0].index("Shaft Number")
colMat = rngShft[0].index("Material")
colMS = rngShft[0].index("MS")
colConn = rngShft[0].index("Connection Type")
colItemN = rngShft[0].index("Item Notes")
colDesc = rngShft[0].index("Description")
# Define strings to match in description of the SHAFT group (and item notes)
searchPattern = re.compile(
"(SHAFT,P.?MP) (?P<series>T.) (AR.)?(?P<remStgType>\w+) (?P<frac1>\d+)/(?P<frac2>\d+) \#(?P<shft_num>\d+)"
)
shftMatList = ["INC", "MNL", "NIT", "UHS"]
shftMatDict = {
'MS-1121-04': 'NIT',
'MS-1131-01': 'INC',
'MS-1131-02': 'UHS',
'MS-1241-03': 'MNL'
}
shaftSizeDict = {"5/8": 0.62, "11/16": 0.69, "1/2": 0.5, "7/8": 0.88}
connList = ["BUT", "UHP"]
# Main loop that populates the array (list of lists) for SHAFT data
for rowi in range(1, len(rngShft)):
desc = rngShft[rowi][colDesc]
itemN = rngShft[rowi][colItemN]
shftExtDesc = desc + " " + itemN
# Search using regular expressions
descMatch = re.search(searchPattern, shftExtDesc)
if descMatch:
# Series
rngShft[rowi][colSeries] = descMatch.group('series')
# Type
rngShft[rowi][colStgType] = "AR-" + descMatch.group('remStgType')
# Shaft size
shaftsizefrac = descMatch.group('frac1') + "/" + \
descMatch.group('frac2')
if shaftsizefrac in shaftSizeDict:
rngShft[rowi][colShaftSize] = shaftSizeDict[shaftsizefrac]
# Shaft number
rngShft[rowi][colShaftNum] = descMatch.group('shft_num')
# Material
if rngShft[rowi][colMS] != "":
matKey = rngShft[rowi][colMS][0:10]
if matKey in shftMatDict:
rngShft[rowi][colMat] = shftMatDict[matKey]
else:
rngShft[rowi][colMat] = ", ".join(shftMat
for shftMat in shftMatList
if shftMat in shftExtDesc)
# Connection type (type of shaft)
if any(conn in shftExtDesc for conn in connList):
rngShft[rowi][colConn] = ", ".join(conn for conn in connList
if conn in shftExtDesc)
elif rngShft[rowi][colSeries] == "TA":
rngShft[rowi][colConn] = "BUT"
# Export modified SHAFT data back to worksheet
pmpDataSht[rowShft + 2, 0].value = rngShft[1:]
def main():
# Define PumpData sheet object
wb = xw.Book.caller()
pmpDataSht = wb.sheets["PumpData"]
tmpSht = wb.sheets["TempSheet"]
# Import HOUSING data as an array (list of lists)
rowHSG = cF.findHeader(pmpDataSht, 'HOUSING')
rowEnd = cF.findHeader(pmpDataSht, 'LOWER DIFFUSER')
colEnd = pmpDataSht[rowHSG + 1, 0].end('right').column
rngHSG = pmpDataSht[rowHSG + 1:rowEnd, 0:colEnd].value
# Determine values for necessary column
colSeries = rngHSG[0].index("Series")
colType = rngHSG[0].index("Type Association")
colHsgNum = rngHSG[0].index("Housing Number")
colMat = rngHSG[0].index("Material")
colMS = rngHSG[0].index("MS")
colConn = rngHSG[0].index("Connection Type")
colItemN = rngHSG[0].index("Item Notes")
colDesc = rngHSG[0].index("Description")
colItemID = rngHSG[0].index("Item ID")
# Extract pump info from TempSheet and clear TempSheet
pmpSeries = tmpSht[0, 0].value
pmpStageType = tmpSht[1, 0].value
pmpHousingNum = tmpSht[2, 0].options(numbers=int).value
pmpHousingMat = tmpSht[3, 0].value
pmpConn = tmpSht[4, 0].value
tmpSht[0:5, 0].value = ""
# Useful transformations and associations
# 1. Type association
if pmpStageType in ["FLT", "AR-FLT", "AR-MDLR"]:
strType = "FLT"
else:
strType = "CMP"
# 2. Housing number to correct format
if pmpHousingNum < 10:
strHousingNum = "0" + str(pmpHousingNum)
else:
strHousingNum = str(pmpHousingNum)
# Initialize BOM elements
strHousingItemID = "NEW"
strHousingDescription = "HOUSING,PMP " + pmpSeries + " " + strType + " #" + \
strHousingNum + " " + pmpHousingMat + "*"
strHousingItemNotes = pmpConn
# Find a part number matching the HOUSING (if any existing)
for rowi in range(1, len(rngHSG)):
if pmpSeries == rngHSG[rowi][colSeries] and \
strType == rngHSG[rowi][colType] and \
pmpHousingNum == rngHSG[rowi][colHsgNum] and \
pmpHousingMat == rngHSG[rowi][colMat] and \
pmpConn == rngHSG[rowi][colConn]:
strHousingItemID = rngHSG[rowi][colItemID]
break
# Write to TempSheet
tmpSht[0, 0].value = strHousingItemID
tmpSht[0, 1].value = strHousingDescription
tmpSht[0, 2].value = strHousingItemNotes
def main():
# Define PumpData sheet object
wb = xw.Book.caller()
#wb = xw.Book("ESP_config.xlsm")
pmpDataSht = wb.sheets["PumpData"]
tmpSht = wb.sheets["TempSheet"]
# Import STAGE GROUP data as an array (list of lists)
rowSTG = cF.findHeader(pmpDataSht, 'STAGE GROUP')
rowEnd = cF.findHeader(pmpDataSht, 'H&B GROUP')
colEnd = pmpDataSht[rowSTG + 1, 0].end('right').column
rngSTG = pmpDataSht[rowSTG + 1:rowEnd, 0:colEnd].options(empty='').value
# Determine values for necessary column
colSeries = rngSTG[0].index("Series")
colBPD = rngSTG[0].index("BPD")
colStgType = rngSTG[0].index("Stage Type")
colTemp = rngSTG[0].index("Temperature")
colUPR = rngSTG[0].index("UPR")
colOring = rngSTG[0].index("O-Ring Material")
colMat = rngSTG[0].index("Material")
colCoat = rngSTG[0].index("Coating Type")
colItemN = rngSTG[0].index("Item Notes")
colDesc = rngSTG[0].index("Description")
colItemID = rngSTG[0].index("Item ID")
# Extract pump info from TempSheet and clear TempSheet
pmpSeries = tmpSht[0, 0].value
pmpCapacity = tmpSht[1, 0].options(numbers=int).value
pmpStageType = tmpSht[2, 0].value
pmpStageCount = tmpSht[3, 0].options(numbers=int).value
if "AR" in pmpStageType or "Q" in pmpStageType:
pmpBearingCount = tmpSht[4, 0].options(numbers=int).value
pmpStagesMaterial = tmpSht[5, 0].value
pmpOringMaterial = tmpSht[6, 0].value
pmpCoatingMaterial = tmpSht[7, 0].value
pmpAppType = tmpSht[8, 0].value
tmpSht[0:9, 0].value = ""
# Other useful info
noOringList = ["TA400", "TA550", "TA900", "TA1200", "TA1500"]
# -------------------------------------------------------------------------
# Initialize REGULAR stage group (for both AR and non-AR pumps)
# -------------------------------------------------------------------------
# Item ID initialized as "NEW"
strStgGrpItemID = "NEW"
# REGULAR stage group description
strStgGrpDescription = "GROUP,STAGE " + pmpSeries + str(pmpCapacity)
# Check whether pump configuration is AR/Q-plus, which then requires modification
# in the REGULAR stage group description of stage type
if "AR" in pmpStageType:
strStgGrpDescription = strStgGrpDescription + " " + pmpStageType[3:]
strStgType = pmpStageType[3:] # For use when matching part numbers
elif "Q" in pmpStageType:
strStgGrpDescription = strStgGrpDescription + " " + "MDLR"
strStgType = "MDLR"
else:
strStgGrpDescription = strStgGrpDescription + " " + pmpStageType
strStgType = pmpStageType
# Add O-ring material to description if not in the noOringList
# ****************EXCEPTIONS: pumps with Quad Rings****************
if pmpSeries == "TA":
if pmpSeries + str(pmpCapacity) in noOringList:
strStgGrpDescription = strStgGrpDescription + "*"
strOring = "" # For use when matching part numbers
else:
strStgGrpDescription = strStgGrpDescription + " " + pmpOringMaterial + "*"
strOring = pmpOringMaterial
elif pmpSeries == "TD":
strStgGrpDescription = strStgGrpDescription + " EPDM*"
strOring = "EPDM"
# Item notes
strStgGrpItemNotes = pmpStagesMaterial
strCoating = "" # For use when matching part numbers
# Add coating material to item notes if applicable
if pmpCoatingMaterial != "N/A":
strStgGrpItemNotes = strStgGrpItemNotes + " " + pmpCoatingMaterial
strCoating = pmpCoatingMaterial
#--------------------------------O-----------------------------------------
# Determine REGULAR and AR (bearing) stage counts (if any)
if "AR" in pmpStageType:
strStgCount = pmpStageCount - pmpBearingCount + 2
strARStgCount = pmpBearingCount - 2
elif "Q" in pmpStageType:
strStgCount = pmpStageCount - pmpBearingCount + 2
strQpStgCount = pmpBearingCount - 3
strQpUPRStgCount = 1
else:
strStgCount = pmpStageCount
# -------------------------------------------------------------------------
# Initialize AR stage group (ONLY for AR pumps)
# -------------------------------------------------------------------------
if "AR" in pmpStageType:
# Item ID initialized as "NEW"
strARStgGrpItemID = "NEW"
# AR stage group description
strARStgGrpDescription = "GROUP,STAGE " + pmpSeries + str(pmpCapacity) + " " \
+ pmpStageType
# Add O-ring material to description if not in the noOringList
# ****************EXCEPTIONS: pumps with Quad Rings****************
if pmpSeries == "TA":
if pmpSeries + str(pmpCapacity) in noOringList:
strARStgGrpDescription = strARStgGrpDescription + "*"
strAROring = "" # For use when matching part numbers
else:
strARStgGrpDescription = strARStgGrpDescription + " " + pmpOringMaterial + "*"
strAROring = pmpOringMaterial
elif pmpSeries == "TD":
strARStgGrpDescription = strARStgGrpDescription + " EPDM*"
strAROring = "EPDM"
# Item notes
strARStgGrpItemNotes = pmpStagesMaterial
strARCoating = "" # For use when matching part numbers
# Add coating material to item notes if applicable
if pmpCoatingMaterial != "N/A":
strARStgGrpItemNotes = strARStgGrpItemNotes + " " + pmpCoatingMaterial
strARCoating = pmpCoatingMaterial
# -------------------------------------------------------------------------
# Initialize Q+ & Q+ UPR stage group (ONLY for Q+ pumps)
# -------------------------------------------------------------------------
if "Q" in pmpStageType:
# Item ID initialized as "NEW"
strQpStgGrpItemID = "NEW"
# Q+ stage group description
strQpStgGrpDescription = "GROUP,STAGE " + pmpSeries + str(pmpCapacity) + " " \
+ pmpStageType
# Add O-ring material to description if not in the noOringList
# ****************EXCEPTIONS: pumps with Quad Rings****************
if pmpSeries == "TA":
if pmpSeries + str(pmpCapacity) in noOringList:
strQpStgGrpDescription = strQpStgGrpDescription + "*"
strQpOring = "" # For use when matching part numbers
else:
strQpStgGrpDescription = strQpStgGrpDescription + " " + pmpOringMaterial + "*"
strQpOring = pmpOringMaterial
elif pmpSeries == "TD":
strQpStgGrpDescription = strQpStgGrpDescription + " EPDM*"
strQpOring = "EPDM"
# Item notes
strQpStgGrpItemNotes = pmpStagesMaterial
strQpCoating = "" # For use when matching part numbers
# Add coating material to item notes if applicable
if pmpCoatingMaterial != "N/A":
strQpStgGrpItemNotes = strQpStgGrpItemNotes + " " + pmpCoatingMaterial
strQpCoating = pmpCoatingMaterial
# Q+ UPR stage Item ID initialized as "NEW"
strQpUPRStgGrpItemID = "NEW"
# Q+ UPRstage group description
strQpUPRStgGrpDescription = "GROUP,STAGE " + pmpSeries + str(pmpCapacity) + " " \
+ pmpStageType + "-UPR"
# Add O-ring material to description if not in the noOringList
# ****************EXCEPTIONS: pumps with Quad Rings****************
if pmpSeries == "TA":
if pmpSeries + str(pmpCapacity) in noOringList:
strQpUPRStgGrpDescription = strQpUPRStgGrpDescription + "*"
strQpUPROring = "" # For use when matching part numbers
else:
strQpUPRStgGrpDescription = strQpUPRStgGrpDescription + " " + pmpOringMaterial + "*"
strQpUPROring = pmpOringMaterial
elif pmpSeries == "TD":
strQpUPRStgGrpDescription = strQpUPRStgGrpDescription + " EPDM*"
strQpUPROring = "EPDM"
# Item notes
strQpUPRStgGrpItemNotes = pmpStagesMaterial
strQpUPRCoating = "" # For use when matching part numbers
# Add coating material to item notes if applicable
if pmpCoatingMaterial != "N/A":
strQpUPRStgGrpItemNotes = strQpUPRStgGrpItemNotes + " " + pmpCoatingMaterial
strQpUPRCoating = pmpCoatingMaterial
# Application type designation
if pmpAppType == "XHT":
strApp = "XHT"
else:
strApp = ""
#--------------------------------O-----------------------------------------
# Find a part number for REGULAR stage group matching the STAGE GROUP data (if any existing)
for rowi in range(1, len(rngSTG)):
if pmpSeries == rngSTG[rowi][colSeries] and \
pmpCapacity == rngSTG[rowi][colBPD] and \
strStgType in rngSTG[rowi][colStgType] and \
"AR" not in rngSTG[rowi][colStgType] and \
"Q" not in rngSTG[rowi][colStgType] and \
strOring == rngSTG[rowi][colOring] and \
pmpStagesMaterial == rngSTG[rowi][colMat] and \
strCoating == rngSTG[rowi][colCoat] and \
strApp == rngSTG[rowi][colTemp]:
strStgGrpItemID = rngSTG[rowi][colItemID]
oldStgType = rngSTG[rowi][colStgType]
if oldStgType == "CMP/MDLR":
newStgType = "C/MDLR"
strStgGrpDescription = strStgGrpDescription.replace(strStgType,\
newStgType)
elif oldStgType == "CMP/FLT":
newStgType = "C/F"
strStgGrpDescription = strStgGrpDescription.replace(strStgType,\
newStgType)
break
# If no matching part number found and if pump type is a TA series AR-MDLR,
# try to find a match with CMP stage type (done CURRENTLY for TA550, TA900
# and TA1200)
if pmpSeries == "TA" and pmpStageType == "AR-MDLR" and strStgGrpItemID == "NEW":
for rowi in range(1, len(rngSTG)):
if pmpSeries == rngSTG[rowi][colSeries] and \
pmpCapacity == rngSTG[rowi][colBPD] and \
"CMP" == rngSTG[rowi][colStgType] and \
strOring == rngSTG[rowi][colOring] and \
pmpStagesMaterial == rngSTG[rowi][colMat] and \
strCoating == rngSTG[rowi][colCoat] and \
strApp == rngSTG[rowi][colTemp]:
strStgGrpItemID = rngSTG[rowi][colItemID]
strStgGrpDescription = strStgGrpDescription.replace(
"MDLR", "CMP")
break
# Find a part number for AR stage group matching the STAGE GROUP data (if any existing)
if "AR" in pmpStageType:
for rowi in range(1, len(rngSTG)):
if pmpSeries == rngSTG[rowi][colSeries] and \
pmpCapacity == rngSTG[rowi][colBPD] and \
pmpStageType[3:] in rngSTG[rowi][colStgType] and \
"AR" in rngSTG[rowi][colStgType] and \
strAROring == rngSTG[rowi][colOring] and \
pmpStagesMaterial == rngSTG[rowi][colMat] and \
strARCoating == rngSTG[rowi][colCoat] and \
strApp == rngSTG[rowi][colTemp]:
strARStgGrpItemID = rngSTG[rowi][colItemID]
oldStgType = rngSTG[rowi][colStgType]
if oldStgType == "AR-CMP/FLT":
newStgType = "AR-C/F"
strStgGrpDescription = strStgGrpDescription.replace(pmpStageType,\
newStgType)
break
# Find a part number for Q+ stage group matching the STAGE GROUP data (if any existing)
if "Q" in pmpStageType:
# For Q+ stage
for rowi in range(1, len(rngSTG)):
if pmpSeries == rngSTG[rowi][colSeries] and \
pmpCapacity == rngSTG[rowi][colBPD] and \
"Q-PLUS" == rngSTG[rowi][colStgType] and \
"" == rngSTG[rowi][colUPR] and \
strQpOring == rngSTG[rowi][colOring] and \
pmpStagesMaterial == rngSTG[rowi][colMat] and \
strQpCoating == rngSTG[rowi][colCoat] and \
strApp == rngSTG[rowi][colTemp]:
strQpStgGrpItemID = rngSTG[rowi][colItemID]
break
# For Q+ UPR stage
for rowi in range(1, len(rngSTG)):
if pmpSeries == rngSTG[rowi][colSeries] and \
pmpCapacity == rngSTG[rowi][colBPD] and \
"Q-PLUS" == rngSTG[rowi][colStgType] and \
"UPR" == rngSTG[rowi][colUPR] and \
strQpUPROring == rngSTG[rowi][colOring] and \
pmpStagesMaterial == rngSTG[rowi][colMat] and \
strQpUPRCoating == rngSTG[rowi][colCoat] and \
strApp == rngSTG[rowi][colTemp]:
strQpUPRStgGrpItemID = rngSTG[rowi][colItemID]
break
# Write to TempSheet
tmpSht[0, 0].value = strStgGrpItemID
tmpSht[0, 1].value = strStgCount
tmpSht[0, 2].value = strStgGrpDescription
tmpSht[0, 3].value = strStgGrpItemNotes
if "AR" in pmpStageType:
tmpSht[1, 0].value = strARStgGrpItemID
tmpSht[1, 1].value = strARStgCount
tmpSht[1, 2].value = strARStgGrpDescription
tmpSht[1, 3].value = strARStgGrpItemNotes
elif "Q" in pmpStageType:
tmpSht[1, 0].value = strQpStgGrpItemID
tmpSht[1, 1].value = strQpStgCount
tmpSht[1, 2].value = strQpStgGrpDescription
tmpSht[1, 3].value = strQpStgGrpItemNotes
tmpSht[2, 0].value = strQpUPRStgGrpItemID
tmpSht[2, 1].value = strQpUPRStgCount
tmpSht[2, 2].value = strQpUPRStgGrpDescription
tmpSht[2, 3].value = strQpUPRStgGrpItemNotes
def main():
# Define PumpData sheet object
wb = xw.Book.caller()
pmpDataSht = wb.sheets["PumpData"]
# Import STAGE GROUP data as an array (list of lists)
rowSTG = cF.findHeader(pmpDataSht, 'STAGE GROUP')
rowEnd = cF.findHeader(pmpDataSht, 'H&B GROUP')
colEnd = pmpDataSht[rowSTG+1,0].end('right').column
rngSTG = pmpDataSht[rowSTG+1:rowEnd,0:colEnd].options(empty='').value
# Determine values for necessary column
colSeries = rngSTG[0].index("Series")
colBPD = rngSTG[0].index("BPD")
colStgType = rngSTG[0].index("Stage Type")
colTemp = rngSTG[0].index("Temperature")
colUPR = rngSTG[0].index("UPR")
colOring = rngSTG[0].index("O-Ring Material")
colMat = rngSTG[0].index("Material")
colCoat = rngSTG[0].index("Coating Type")
colItemN = rngSTG[0].index("Item Notes")
colDesc = rngSTG[0].index("Description")
# Define strings to match in description of the stage group (and item notes)
seriesPattern = re.compile("(T.) *?(\d+)")
StageTypes = [re.compile("AR.CMP"),re.compile("AR.FLT"),re.compile("AR.MDLR"),\
re.compile("AR.C/F"),"CMP","FLT","C/F","C/MDLR","MDLR","MODULAR","Q PLUS"]
noOringList = ["TA400","TA550","TA900","TA1200","TA1500"]
OringMatList = ["AFL","HSN","VIT","CHEM","FFKM","EPDM"]
MatList = ["N1","N2","N3","N4","DPX","SDX","3SS","CI"]
coatType = ["NDP","FPS","CRP","SF2","DGS","BDP","GET"]
tempInd = ["HTP","XHT"]
# Main loop that populates the array (list of lists) for stage group data
for rowi in range(1,len(rngSTG)):
desc = rngSTG[rowi][colDesc]
itemN = rngSTG[rowi][colItemN]
stgExtDesc = desc + " " + itemN
# Series and BPD
seriesMatch = re.search(seriesPattern, stgExtDesc)
if seriesMatch:
rngSTG[rowi][colSeries] = seriesMatch.group(1)
rngSTG[rowi][colBPD] = seriesMatch.group(2)
# Stage type
sTypMatch = [re.search(s, stgExtDesc) for s in StageTypes]
if any(sTypMatch):
firstMatchIndex = [i for i,v in enumerate(sTypMatch) if v][0]
sTyp = sTypMatch[firstMatchIndex].group(0)
# If AR type stages are found, make sure that there is a hyphen after the AR
if sTyp[0:2] == "AR":
listSType = list(sTyp)
listSType[0:3] = ['A','R','-']
sTyp = "".join(listSType)
elif sTyp[0] == "Q":
listSType = list(sTyp)
listSType[0:3] = ['Q','-','P']
sTyp = "".join(listSType)
# If combo stage type found, then spell out the types
if sTyp == "AR-C/F":
sTyp = "AR-CMP/FLT"
elif sTyp == "C/F":
sTyp = "CMP/FLT"
elif sTyp == "C/MDLR":
sTyp = "CMP/MDLR"
elif sTyp == "MODULAR":
sTyp = "MDLR"
rngSTG[rowi][colStgType] = sTyp
# Determine if a Q PLUS stage is UPR
if sTyp == "Q-PLUS":
if "UPR" in stgExtDesc:
rngSTG[rowi][colUPR] = "UPR"
# Oring material
seriesBPD = rngSTG[rowi][colSeries] + rngSTG[rowi][colBPD]
if seriesBPD in noOringList:
rngSTG[rowi][colOring] = ""
elif any(oMat in stgExtDesc for oMat in OringMatList):
rngSTG[rowi][colOring] = ", ".join(oMat for oMat in OringMatList if oMat in stgExtDesc)
elif seriesMatch.group(1) == "TA":
rngSTG[rowi][colOring] = "HSN" # seems to be the default if TA Stage O-Ring Material not specified
elif seriesMatch.group(1) == "TD":
rngSTG[rowi][colOring] = "EPDM" # all TDs by default have EPDM material quad rings in the stages
# Stage Material
if any(sMat in stgExtDesc for sMat in MatList):
rngSTG[rowi][colMat] = ", ".join(sMat for sMat in MatList if sMat in stgExtDesc)
else:
rngSTG[rowi][colMat] = "N1"
# Coating type
rngSTG[rowi][colCoat] = ", ".join(cTyp for cTyp in coatType if cTyp in stgExtDesc)
# Temperature
if any(tInd in stgExtDesc for tInd in tempInd):
rngSTG[rowi][colTemp] = "XHT"
# Export modified STAGE GROUP data back to worksheet
pmpDataSht[rowSTG+2,0].value = rngSTG[1:]
def main():
# Define PumpData sheet object
wb = xw.Book.caller()
pmpDataSht = wb.sheets["PumpData"]
tmpSht = wb.sheets["TempSheet"]
# Import COMMON (com parts) data as an array (list of lists)
rowCOM = cF.findHeader(pmpDataSht, 'COMMON')
rowEnd = cF.findHeader(pmpDataSht, 'STAGE GROUP')
colEnd = pmpDataSht[rowCOM + 1, 0].end('right').column
rngCOM = pmpDataSht[rowCOM + 1:rowEnd, 0:colEnd].options(empty='').value
# Determine values for necessary column
colSeries = rngCOM[0].index("Series")
colStgType = rngCOM[0].index("Stage Type")
colShaftSize = rngCOM[0].index("Shaft Size")
colDesignation = rngCOM[0].index("Designation")
colItemN = rngCOM[0].index("Item Notes")
colDesc = rngCOM[0].index("Description")
colItemID = rngCOM[0].index("Item ID")
# Extract pump info from TempSheet and clear TempSheet
pmpSeries = tmpSht[0, 0].value
pmpStageType = tmpSht[1, 0].value
pmpShaftSize = tmpSht[2, 0].value
pmpExternal = tmpSht[3, 0].value
pmpAppType = tmpSht[4, 0].value
tmpSht[0:5, 0].value = ""
# Stage type association for COM PART group
if pmpStageType in ["FLT", "AR-FLT"]:
strComStageType = "AR-FLT"
elif pmpStageType in ["CMP", "AR-CMP"]:
strComStageType = "AR-CMP"
elif pmpStageType == "AR-MDLR":
strComStageType = "AR-MDLR"
elif pmpStageType == "Q-PLUS":
strComStageType = "Q-PLUS"
# Shaft size converted to a string of the correct format for COM PART group
shaftSizeMatch = re.search("\.\d+", str(pmpShaftSize))
strShaftSize = shaftSizeMatch.group(0)
# Special designation based on desired pump configuration
if pmpExternal == "NLP":
strDesignation = "NLP"
elif pmpAppType in ["UHP", "WAG"]:
strDesignation = pmpAppType
else:
strDesignation = ""
# Initialize the BOM elements
strComItemID = "NEW"
strComDescription = "COM PART,PMP " + pmpSeries + " " + strComStageType + " " + \
strShaftSize
if strDesignation != "":
strComDescription = strComDescription + "*"
strComItemNotes = strDesignation
# Find a part number matching the COM PART group (if any existing)
for rowi in range(1, len(rngCOM)):
if pmpSeries == rngCOM[rowi][colSeries] and \
strComStageType == rngCOM[rowi][colStgType] and \
pmpShaftSize == rngCOM[rowi][colShaftSize] and \
strDesignation == rngCOM[rowi][colDesignation]:
strComItemID = rngCOM[rowi][colItemID]
break
# Write to TempSheet
tmpSht[0, 0].value = strComItemID
tmpSht[0, 1].value = strComDescription
tmpSht[0, 2].value = strComItemNotes
def main():
# Define PumpData sheet object
#wb = xw.Book('ESP_config.xlsm')
wb = xw.Book.caller()
pmpDataSht = wb.sheets["PumpData"]
tmpSht = wb.sheets["TempSheet"]
# Import SPACER data as an array (list of lists)
rowSPC = cF.findHeader(pmpDataSht, 'SPACER GROUP')
rowEnd = cF.findHeader(pmpDataSht, 'SHIPPING GROUP')
colEnd = pmpDataSht[rowSPC + 1, 0].end('right').column
rngSPC = pmpDataSht[rowSPC + 1:rowEnd, 0:colEnd].options(empty='').value
# Determine values for necessary column
colShaftSize = rngSPC[0].index("Shaft Size")
colMat = rngSPC[0].index("Material")
colItemN = rngSPC[0].index("Item Notes")
colDesc = rngSPC[0].index("Description")
colItemID = rngSPC[0].index("Item ID")
# Extract pump info from TempSheet and clear TempSheet
pmpShaftSize = tmpSht[0, 0].value
tmpSht[0, 0].value = ""
# Material for SPACER (304 is standard going forward)
if pmpShaftSize == 0.50:
strMat = "N1"
elif pmpShaftSize in [0.62, 0.69, 0.88]:
strMat = "304"
# Shaft size association to fraction
if pmpShaftSize == 0.62:
strShaftSize = "5/8"
elif pmpShaftSize == 0.69:
strShaftSize = "11/16"
elif pmpShaftSize == 0.5:
strShaftSize = "1/2"
elif pmpShaftSize == 0.88:
strShaftSize = "7/8"
# Initialize the BOM elements
strSpacerItemID = "NEW"
strSpacerDescription = "GROUP,SPACER IMPELLER " + strShaftSize + " " + \
strMat
strSpacerItemNotes = ""
# Find a part number matching the SPACER group (if any existing)
for rowi in range(1, len(rngSPC)):
if pmpShaftSize == rngSPC[rowi][colShaftSize]:
dataMat = rngSPC[rowi][colMat]
if str(int(dataMat)) == "304":
compDataMat = "304"
else:
compDataMat = dataMat
if strMat == compDataMat:
strSpacerItemID = rngSPC[rowi][colItemID]
break
# Write to TempSheet
tmpSht[0, 0].value = strSpacerItemID
tmpSht[0, 1].value = strSpacerDescription
tmpSht[0, 2].value = strSpacerItemNotes
def main():
# Define PumpData sheet object
wb = xw.Book.caller()
pmpDataSht = wb.sheets["PumpData"]
tmpSht = wb.sheets["TempSheet"]
# Import COUPLING data as an array (list of lists)
rowCPLG = cF.findHeader(pmpDataSht, 'COUPLING')
rowEnd = pmpDataSht[rowCPLG + 1, 0].end('down').row
colEnd = pmpDataSht[rowCPLG + 1, 0].end('right').column
rngCPLG = pmpDataSht[rowCPLG + 1:rowEnd, 0:colEnd].value
# Determine values for necessary column
colSeries = rngCPLG[0].index("Series")
colStgType = rngCPLG[0].index("Stage Type")
colSize = rngCPLG[0].index("Size")
colMat = rngCPLG[0].index("Material")
colMS = rngCPLG[0].index("MS")
colItemN = rngCPLG[0].index("Item Notes")
colDesc = rngCPLG[0].index("Description")
colItemID = rngCPLG[0].index("Item ID")
# Extract pump info from TempSheet and clear TempSheet
pmpSeries = tmpSht[0, 0].value
pmpStageType = tmpSht[1, 0].value
pmpCplgMat = tmpSht[2, 0].value
pmpShaftSize = tmpSht[3, 0].value
tmpSht[0:4, 0].value = ""
# Useful transformations and associations
# 1. Stage type association
if pmpStageType in ["FLT", "AR-FLT", "AR-MDLR", "Q-PLUS"]:
strStageType = "FLT"
else:
strStageType = "CMP"
# 2. Shaft size to fraction format
if pmpShaftSize == 0.62:
strShaftSize = "5/8"
elif pmpShaftSize == 0.69:
strShaftSize = "11/16"
elif pmpShaftSize == 0.50:
strShaftSize = "1/2"
elif pmpShaftSize == 0.88:
strShaftSize = "7/8"
# 3. Series assocation
if pmpSeries == "TA" and strStageType == "FLT" and strShaftSize == "5/8":
strSeries = "TA"
elif pmpSeries == "TA":
strSeries = "TD"
else:
strSeries = pmpSeries
# Initialize BOM elements
strCouplingItemID = "NEW"
strCouplingDescription = "COUPLING,PMP " + strSeries + " " + strStageType + \
" " + strShaftSize + " S/A " + pmpCplgMat
strCouplingItemNotes = ""
# Find a part number matching the COUPLING (if any existing)
for rowi in range(1, len(rngCPLG)):
if strSeries == rngCPLG[rowi][colSeries] and \
strStageType == rngCPLG[rowi][colStgType] and \
strShaftSize == rngCPLG[rowi][colSize] and \
pmpCplgMat == rngCPLG[rowi][colMat]:
strCouplingItemID = rngCPLG[rowi][colItemID]
break
# Write to TempSheet
tmpSht[0, 0].value = strCouplingItemID
tmpSht[0, 1].value = strCouplingDescription
tmpSht[0, 2].value = strCouplingItemNotes
def main():
# Define PumpData sheet object
wb = xw.Book.caller()
pmpDataSht = wb.sheets["PumpData"]
# Import LOWER DIFFUSER data as an array (list of lists)
rowLDF = cF.findHeader(pmpDataSht, 'LOWER DIFFUSER')
rowEnd = cF.findHeader(pmpDataSht, 'KEYSTOCK')
colEnd = pmpDataSht[rowLDF + 1, 0].end('right').column
rngLDF = pmpDataSht[rowLDF + 1:rowEnd, 0:colEnd].value
# Determine values for necessary column
colSeries = rngLDF[0].index("Series")
colBPD = rngLDF[0].index("BPD")
colType1 = rngLDF[0].index("Type 1")
colType2 = rngLDF[0].index("Type 2")
colMat = rngLDF[0].index("Material")
colCoat = rngLDF[0].index("Coating Material")
colMS = rngLDF[0].index("MS")
colItemN = rngLDF[0].index("Item Notes")
colDesc = rngLDF[0].index("Description")
# Define strings to match in description of the lower diffuser (and item notes)
searchPattern = re.compile(
"DIFFUSER,LO?WE?R (?P<series>T.) ?(?P<BPD1>\d+)/?(?P<BPD2>\d+)?")
StageTypes = ["CMP", "FLT", "C/F"]
type2List = [
re.compile("P-MA?CH"),
re.compile("CA?ST"),
re.compile("MA?CH")
]
matList = ["N1", "N2", "N3", "N4", "DPX", "SDX", "3SS", "CI"]
coatType = ["NDP", "FPS", "CRP", "SF2", "DGS", "BDP"]
# Main loop that populates the array (list of lists) for lower diffuser data
for rowi in range(1, len(rngLDF)):
desc = rngLDF[rowi][colDesc]
itemN = rngLDF[rowi][colItemN]
if itemN is None:
itemN = ""
ldfExtDesc = desc + " " + itemN
# Search description using regular expressions
descMatch = re.search(searchPattern, ldfExtDesc)
if descMatch:
# Series
rngLDF[rowi][colSeries] = descMatch.group('series')
# BPD1
rngLDF[rowi][colBPD] = descMatch.group('BPD1')
# BPD2 (if at all)
if descMatch.group('BPD2') is not None:
rngLDF[rowi][colBPD] = rngLDF[rowi][
colBPD] + "/" + descMatch.group('BPD2')
# Type 1 (stage type)
type1Match = [re.search(s, ldfExtDesc) for s in StageTypes]
if any(type1Match):
firstMatchIndex = [i for i, v in enumerate(type1Match) if v][0]
sTyp = type1Match[firstMatchIndex].group(0)
# If combo stage type found, then spell out the types
if sTyp == "C/F":
sTyp = "CMP/FLT"
rngLDF[rowi][colType1] = sTyp
# Type 2
type2Match = [re.search(t, ldfExtDesc) for t in type2List]
if any(type2Match):
firstMatchIndex = [i for i, v in enumerate(type2Match) if v][0]
tTyp = type2Match[firstMatchIndex].group(0)
# correct abbreviations if needed
if tTyp == "P-MCH":
tTyp = "P-MACH"
elif tTyp == "CAST":
tTyp = "CST"
elif tTyp == "MCH":
tTyp = "MACH"
rngLDF[rowi][colType2] = tTyp
# Material (stg material)
if any(sMat in ldfExtDesc for sMat in matList):
rngLDF[rowi][colMat] = ", ".join(sMat for sMat in matList
if sMat in ldfExtDesc)
else:
rngLDF[rowi][colMat] = "N1"
# Coating type
rngLDF[rowi][colCoat] = ", ".join(cTyp for cTyp in coatType
if cTyp in ldfExtDesc)
# Export modified LOWER DIFFUSER data back to worksheet
pmpDataSht[rowLDF + 2, 0].value = rngLDF[1:]
