This python script is to remove formula and system columns from Talend schema file

removeNonWritableColumnsFromTalendSchemaXml.py

import subprocess
import os
import shutil
import xml.etree.ElementTree as ET

objectName = 'SBQQ__ConfigurationRule__c'

command = "sfdx force:schema:sobject:describe --sobjecttype=" + objectName + " --targetusername=TEST --json | jq '.result.fields[]' | jq '.name' | jq -s ."

commandOutput = subprocess.check_output(command,shell=True)

allColumnNames = set(eval(commandOutput))

objectsWithExteranlIdCol = set(["SBQQ__OptionConstraint__c","SBQQ__ErrorCondition__c","SBQQ__ConfigurationRule__c","SBQQ__PriceRule__c","SBQQ__ProductOption__c","SBQQ__ProductRule__c","SBQQ__SummaryVariable__c","blng__BillingRule__c","blng__BillingTreatment__c","blng__FinanceBook__c","blng__FinancePeriod__c","blng__GLAccount__c","blng__GLRule__c","blng__GLTreatment__c","blng__RevenueDistributionMethod__c","blng__RevenueRecognitionRule__c","blng__RevenueRecognitionTreatment__c","blng__TaxRule__c","blng__TaxTreatment__c","SBQQ__SearchFilter__c","SBQQ__ProductAction__c","SBQQ__PriceAction__c","SBQQ__DiscountTier__c","SBQQ__CustomActionCondition__c","SBQQ__CustomAction__c","SBQQ__Cost__c","SBQQ__ConfigurationAttribute__c","SBQQ__BlockPrice__c","SBQQ__AttributeSet__c","Product2","Pricebook2","PricebookEntry","ConsumptionRate","ConsumptionSchedule","ProductConsumptionSchedule","SBQQ__ProductFeature__c","SBQQ__DiscountSchedule__c","Product2"])

command = "sfdx force:schema:sobject:describe --sobjecttype=" + objectName + " --targetusername=TEST --json | jq '.result.fields[]' | jq 'select((.referenceTo | length) > 0)' | jq '{name:.name,referenceTo:.referenceTo}' | jq -s ."

commandOutput = subprocess.check_output(command, shell=True)
commandOutput = eval(commandOutput)

referenceObjectDictionary = {}
for obj in commandOutput:
    if obj is not None:
        referenceObjectDictionary[obj['name']] = obj['referenceTo']

curatedColumnNames = set([])

for fieldName in referenceObjectDictionary.viewkeys():
    for referenceObjectName in referenceObjectDictionary[fieldName]:
        if referenceObjectName in objectsWithExteranlIdCol:
            allColumnNames.remove(fieldName)
            if fieldName.endswith('__c'):
                curatedColumnNames.add(fieldName.replace('__c','__r.External_Id__c'))
            else:
                curatedColumnNames.add(fieldName.replace('Id','.External_Id__c'))

command = "sfdx force:schema:sobject:describe --sobjecttype=" + objectName +  " --targetusername=TEST --json | jq '.result.fields[]' | jq 'select(.calculatedFormula != null)' | jq '.name' | jq -s ."

formulaColNamesToRemove = set(eval(subprocess.check_output(command, shell=True)))

command = "sfdx force:schema:sobject:describe --sobjecttype=" + objectName + " --targetusername=TEST --json | jq '.result.fields[]' | jq 'select(.autoNumber == true)' | jq '.name' | jq -s ."

autoNumberColNamesToRemove = set(eval(subprocess.check_output(command, shell=True)))

systemColNamesToRemvoe = set(["Id","CreatedById","OwnerId","LastModifiedById","SystemModstamp","CreatedDate","LastModifiedDate","IsDeleted","LastViewedDate","LastReferencedDate","ExternalDataSourceId","IsArchived","LastActivityDate"])

columnNamesToRemove = formulaColNamesToRemove.union(systemColNamesToRemvoe)
columnNamesToRemove = columnNamesToRemove.union(autoNumberColNamesToRemove)

for colName in columnNamesToRemove:
    if colName in allColumnNames:
        allColumnNames.remove(colName)

finalOutput = ''

for colName in allColumnNames:
    finalOutput = finalOutput + ',' + colName

for colName in curatedColumnNames:
    finalOutput = finalOutput + ',' + colName

finalOutput = finalOutput.replace(',,',',')

print 'SELECT ' + finalOutput + ' FROM ' + objectName + ' WHERE External_Id__c > 0'