This code may get you close to what you need. The commented out print statements may help debug or understand the code.
The first step was using Clinton Dow's suggestion of Excel To Table (this page has sample code that will add all worksheets into your geodatabase as tables.
The second step was using code to loop through the feature classes in your feature datasets. This code is basically the code used in your previous question: How to Remove domain from field for gdb.
The third step was including some code by Richard Fairhurst in his blog on Turbo Charging Data Manipulation with Python Cursors and Dictionaries. I've been wanting to explore Richard's code, and this question seemed to suitable.
Here's the code. It may need some tweaking and clean-up.
import arcpy
gdb = r'C:\Path\To\COBW_WS_FA_HVAC_SO_SW_FP\COBW_WS_FA_HVAC_SO_SW_FP.gdb'
arcpy.env.workspace = gdb
dsets = ['Audiovisual','Building','Communication','ConveyingSystem','DataSystem','Drianage','Fire_Alarm',
'Fire_Protection','Fuel','Gas_Air_Services','HVAC','Irrigation','Laboratory','Landbase','Lighting',
'lightning_Earthing','LPG','Master_Clock','MATV_IPTV','Power','Public_Address','Security','Solar',
'Steam_services','Storm_Water','Water_Supply','TSE','WaterResources','BMS' ]
for fds in arcpy.ListDatasets('','Feature'):
if fds in dsets:
print "DS: {}".format(fds)
sourceTbl = 'Asset_TypeCode_xlsx_' + fds
sourceFieldsList = [ "FeatureDataset", "FeatureClass", "SubtypeCode", "AssetTypeCode" ]
valueDict = {str(r[0]) + "," + str(r[1]) + "," + str(r[2]):(r[3:]) for r in arcpy.da.SearchCursor(sourceTbl, sourceFieldsList)}
for fc in arcpy.ListFeatureClasses("*","",fds):
print "\tFC: {}".format(fc)
subtypes = arcpy.da.ListSubtypes(fc)
for stcode, stdict in list(subtypes.items()):
for stkey in list(stdict.keys()):
if not stdict['SubtypeField'] == '':
st_name = stdict['SubtypeField']
else:
st_name = "-"
if stkey == 'FieldValues':
fields = stdict[stkey]
if 'ASSET_TYPE_CODE' in fields:
fieldFound = 1
else:
fieldFound = 0
print("\t\tSubtype: {}".format(st_name))
if st_name == '-' and fieldFound:
updateFieldsList = ["ASSET_TYPE_CODE"]
with arcpy.da.UpdateCursor(fc, updateFieldsList) as updateRows:
for updateRow in updateRows:
keyValue = "{},{},{}".format(fds, fc, '01')
if keyValue in valueDict:
updateRow[0] = valueDict[keyValue][0]
updateRows.updateRow(updateRow)
else:
print "NOT FOUND: {}".format(keyValue)
elif fieldFound:
updateFieldsList = [st_name, "ASSET_TYPE_CODE"]
with arcpy.da.UpdateCursor(fc, updateFieldsList) as updateRows:
for updateRow in updateRows:
keyValue = "{},{},{:02d}".format(fds, fc, updateRow[0] )
if keyValue in valueDict:
updateRow[1] = valueDict[keyValue][0]
updateRows.updateRow(updateRow)
else:
print "NOT FOUND: {}".format(keyValue)
else:
print "\t\tSkipping {}".format(fc)
del valueDict
print "Done."
It seems that your Asset Code Type is a combination of a numeric code for the dataset, feature and subtype code. It should be possible to create a dictionary of datasets and features that would provide the first two codes, then you could combine them with the subtype code in each row of the feature to calculate the Asset Code Type.
In your Fire_Alarm worksheet, there wasn't a code for FA_Module with a subtype of 03. Also, the code above is expecting certain rules: tab names are same as dataset names, the feature class name is in the table, names are case sensitive, etc.