Generate Salesforce object entries in XML based on a FileMaker "schema" and database export. Expects a CSV file with columns "Field Name" and "Field Type", plus a CSV export of the database.

GenerateObject.py

#!/usr/bin/env python

import unicodecsv

templates = {}

templates["Checkbox"] = """
    <fields>
        <fullName>%(fullName)s__c</fullName>
        <defaultValue>false</defaultValue>
        <externalId>false</externalId>
        <label>%(label)s</label>
        <type>Checkbox</type>
    </fields>
"""
templates["Date"] = """
    <fields>
        <fullName>%(fullName)s__c</fullName>
        <externalId>false</externalId>
        <label>%(label)s</label>
        <required>false</required>
        <trackTrending>false</trackTrending>
        <type>Date</type>
    </fields>
"""
templates["LongTextArea"] = """
    <fields>
        <fullName>%(fullName)s__c</fullName>
        <externalId>false</externalId>
        <label>%(label)s</label>
        <length>%(length)s</length>
        <trackTrending>false</trackTrending>
        <type>LongTextArea</type>
        <visibleLines>3</visibleLines>
    </fields>
"""
templates["Number"] = """
    <fields>
        <fullName>%(fullName)s__c</fullName>
        <externalId>false</externalId>
        <label>%(label)s</label>
        <precision>18</precision>
        <required>false</required>
        <scale>0</scale>
        <trackTrending>false</trackTrending>
        <type>Number</type>
        <unique>false</unique>
    </fields>
"""
templates["Text"] = """
    <fields>
        <fullName>%(fullName)s__c</fullName>
        <externalId>false</externalId>
        <label>%(label)s</label>
        <length>%(length)s</length>
        <required>false</required>
        <trackTrending>false</trackTrending>
        <type>Text</type>
        <unique>false</unique>
    </fields>
"""

def generateFullName(label):
    # Generate a Salesforce-safe API name for this field.
    fn = "".join([x if x.isalnum() else '_' for x in label])

    # No double underscores
    while (fn.find("__") != -1):
        fn = fn.replace("__", "_")

    # No leading or following underscores
    return fn.strip("_")

schema = unicodecsv.DictReader(file("AMSchema.csv", "Ur"))

generatedSchema = {}

for line in schema:
    # FIXME: If we had any duplicated API names, we'd need to cure that here.
    # (in Active Master, we don't).
    generatedSchema[line["Field Name"]] = {"fullName": generateFullName(line["Field Name"]),
                                          "label": line["Field Name"],
                                          "originalType": line["Field Type"]}

# Now, infer the data types and maximum lengths of the fields based on the export.

dr = unicodecsv.DictReader(file("Export.csv", "Ur"))

# Sanity check: do the export and the schema contain the same fields?
# We're deliberately omitting since we've chosen to drop many calculated fields
# This is a useful verification to run once.

#discrepancy = set(dr.fieldnames) ^ set(generatedSchema.keys())
#if len(discrepancy) != 0:
#    raise Exception("Field name sets do not match between schema and export: %s" % str(discrepancy))

for line in dr:
    for key in generatedSchema:
        # Default to 1 to preserve fields that don't have any data in them
        # (Salesforce doesn't allow zero-length fields).
        generatedSchema[key]["length"] = max(generatedSchema[key].get("length", 1), len(line[key]))

        if (not generatedSchema[key].has_key("isBoolean") or generatedSchema[key]["isBoolean"]) \
            and len(line[key]) > 0:
            generatedSchema[key]["isBoolean"] = (line[key].lower() in ["yes", "no"])

for key in generatedSchema:
    if generatedSchema[key]["originalType"] == "Date":
        generatedSchema[key]["salesforceType"] = "Date"
    elif generatedSchema[key]["originalType"] == "Number":
        generatedSchema[key]["salesforceType"] = "Number"
    elif generatedSchema[key]["originalType"] == "Text":
        # Determine which Salesforce type to generate based on
        # the maximum length of the field and whether we think it is a boolean

        if generatedSchema[key].get("isBoolean", False):
            generatedSchema[key]["salesforceType"] = "Checkbox"
        else:
            if generatedSchema[key]["length"] <= 255:
                generatedSchema[key]["salesforceType"] = "Text"
            else:
                generatedSchema[key]["salesforceType"] = "LongTextArea"

    print templates[generatedSchema[key]["salesforceType"]] % generatedSchema[key]