From Excel to package.xml

Cleaning up an org that has gone through several generations of ownership and objectives is fun. Some tooling helps

Data frugality

A computing principle, very much the anathema to Google and Facebook, is Data Frugality, storing only what you actually need. It is the data equivalent to coders' YAGNI principle. Latest since GDPR it got center stage attention.

Your cleanup plan

So your cleanup exercise has a few steps:

• Find fields that don't have any data. You can use tools like Field Trip to achieve that
• Verify that these fields are not "about to be used", but "really obsolete"
• Add all the fields that did have some data left over, but unused now
• Add fields that contain data legal told you to get rid off

The absolute standard approach, of any consultant I have encountered, is to fire up an Excel sheet and track all fields in a list, capture insights in the remarks column and have another column that indicates can be deleted Status. Something like Yes,No,Investigating or "Call Paul to clarify". I would be surprised if there's a different approach in the wild (in theory there are).

Excel as source?

In a current project the consultant neatly created one sheet (that's the page, not the file) per object, labeled with the object name, containing rows for all custom fields. Then the team went off to investigate. In result they identified more than one thousand fields to be deleted.

Now to actually get rid of the fields, you could outsource some manual labor to either go into you org or use Copy-Paste to create a destructivechanges.xml package file for use with the Salesforce ANT tool.

In any case: the probability that there will be errors in transferring is approximately 100%. The business owner will point to: I signed off that spreadsheet and not that XML file! Finger pointing commencing.

There must be a better way

XML and XSLT to the rescue

Since I encounter Excel based list quite frequently, I wrote a small tool that converts Excel to XML. It is specifically designed to cater to lists, and lists only. The result is XSLT friendly, with cells carrying proper row and column properties and computed cells carrying the last result and not the formula.

In case you wonder why, read my previous post or the OOXML specifications (The later might take you a while).

A quick command line generated the XML: java -Xmx4g -jar excel2xml.jar -i deadfieldsreport.xslx -o destructivechanges.xml -s that had:

• Object name in sheet name
• Field name in column 0
• Status that determined deletability in column 5

less than 40 lines XSLT did the job.

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:xd="http://www.oxygenxml.com/ns/doc/xsl"
    xmlns="http://soap.sforce.com/2006/04/metadata" exclude-result-prefixes="xs xd" version="2.0">

    <xsl:output encoding="UTF-8" indent="yes" method="xml"/>

    <xsl:param name="fieldNameColumn" select="'0'"/>
    <xsl:param name="statusColumn" select="'5'"/>
    <xsl:param name="isDeletable" select="'yes'"/>

    <xsl:template match="/">
        <Package>
            <xsl:apply-templates
                select="workbook/sheet[row/cell[@col = $statusColumn and lower-case(text()) = $isDeletable]]"/>
            <version>45.0</version>
        </Package>
    </xsl:template>

    <xsl:template match="sheet">
        <xsl:variable name="oName" select="@name"/>
        <xsl:element name="types">
            <!-- xsl:attribute name="object"><xsl:value-of select="$oName"/></xsl:attribute -->
            <xsl:apply-templates
                select="row/cell[@col = $statusColumn and lower-case(text()) = $isDeletable]">
                <xsl:with-param name="oName" select="$oName"/>
            </xsl:apply-templates>
            <name>CustomField</name>
        </xsl:element>
    </xsl:template>

    <xsl:template match="cell">
        <xsl:param name="oName"/>
        <members><xsl:value-of select="replace($oName, ' ', '')"/>.<xsl:value-of
                select="../cell[@col = $fieldNameColumn]"/></members>
    </xsl:template>

</xsl:stylesheet>

The nice side effect: it is repeatable. Just fetch the latest Excel file and run it through. Since no Excel installation is required, you can make this approach part of your CI pipeline.

As usual: YMMV