Undefined value and field were created when value in a CSV row is empty or null

[pskrzybor]

Summary

When using sfdx force:cmdt:record:insert command with CSV file with null or empty value in a row, undefined value and field are being created

Steps To Reproduce:

1. Create a file with empty value:

Name,IsEnabled__c,WhenEnabled__c,WhatTheText__c
NameTest,true,2022-09-14T06:09:00.000+0000,

2. use the command: sfdx force:cmdt:record:insert --filepath dummyMdtToInsert.csv --typename DummyCMDT__mdt

Expected result

<?xml version="1.0" encoding="UTF-8"?>
<CustomMetadata xmlns="http://soap.sforce.com/2006/04/metadata" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <label>NameTest</label>
    <protected>false</protected>
    <values>
        <field>IsEnabled__c</field>
        <value xsi:type="xsd:boolean">true</value>
    </values>
    <values>
        <field>WhatTheText__c</field>
        <value xsi:nil="true"/>
    </values>
    <values>
        <field>WhenEnabled__c</field>
        <value xsi:type="xsd:dateTime">2022-09-14T06:09:00.000+0000</value>
    </values>
</CustomMetadata>

Actual result

<?xml version="1.0" encoding="UTF-8"?>
<CustomMetadata xmlns="http://soap.sforce.com/2006/04/metadata" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <label>NameTest</label>
    <protected>false</protected>
    <values>
        <field>IsEnabled__c</field>
        <value xsi:type="xsd:boolean">true</value>
    </values>
    <values>
        <field>undefined</field>
        <value xsi:type="xsd:string">undefined</value>
    </values>
    <values>
        <field>WhenEnabled__c</field>
        <value xsi:type="xsd:dateTime">2022-09-14T06:09:00.000+0000</value>
    </values>
</CustomMetadata>

System Information

Name                           Value
----                           -----
PSVersion                      7.2.6
PSEdition                      Core
GitCommitId                    7.2.6
OS                             Microsoft Windows 10.0.22000
Platform                       Win32NT
PSCompatibleVersions           {1.0, 2.0, 3.0, 4.0…}
PSRemotingProtocolVersion      2.3
SerializationVersion           1.1.0.1
WSManStackVersion              3.0

{
  "cliVersion": "sfdx-cli/7.167.2",
  "architecture": "win32-x64",
  "nodeVersion": "node-v16.17.0",
  "pluginVersions": [
    "@oclif/plugin-autocomplete 1.3.0 (core)",
    "@oclif/plugin-commands 2.2.0 (core)",
    "@oclif/plugin-help 5.1.12 (core)",
    "@oclif/plugin-not-found 2.3.1 (core)",
    "@oclif/plugin-plugins 2.1.0 (core)",
    "@oclif/plugin-update 3.0.0 (core)",
    "@oclif/plugin-version 1.1.2 (core)",
    "@oclif/plugin-warn-if-update-available 2.0.4 (core)",
    "@oclif/plugin-which 2.1.0 (core)",
    "alias 2.1.0 (core)",
    "apex 1.2.0 (core)",
    "auth 2.2.3 (core)",
    "community 2.0.1 (core)",
    "config 1.4.19 (core)",
    "custom-metadata 2.0.0 (core)",
    "data 2.1.2 (core)",
    "generator 2.0.2 (core)",
    "info 2.0.1 (core)",
    "limits 2.0.1 (core)",
    "org 2.2.0 (core)",
    "packaging 1.6.0 (core)",
    "schema 2.1.1 (core)",
    "signups 1.2.0 (core)",
    "source 2.0.13 (core)",
    "telemetry 2.0.0 (core)",
    "templates 55.1.0 (core)",
    "trust 2.0.3 (core)",
    "user 2.1.0 (core)",
    "@salesforce/sfdx-plugin-lwc-test 1.0.1 (core)",
    "salesforce-alm 54.8.1 (core)"
  ],
  "osVersion": "Windows_NT 10.0.22000",
  "shell": "cmd.exe",
  "rootPath": "C:\\Users\\USER\\AppData\\Local\\sfdx\\client\\7.167.2-93d663e"
}

[github-actions[bot]]

Thank you for filing this issue. We appreciate your feedback and will review the issue as soon as possible. Remember, however, that GitHub isn't a mechanism for receiving support under any agreement or SLA. If you require immediate assistance, contact Salesforce Customer Support.

[github-actions[bot]]

We have determined that the issue you reported exists in code owned by another team that uses only the official support channels. To ensure that your issue is addressed, open an official Salesforce customer support ticket with a link to this issue. We encourage anyone experiencing this issue to do the same to increase the priority. We will keep this issue open for the community to collaborate on.

[PabloRoldan]

We are facing this issue as well. Is there any workaround for this or any solution?

[nebc-jasonfung]

We are facing this issue as well. Is there any workaround for this or any solution?

Same here. This is a real pain as we're having to pre-process the .xml files before they can be uploaded to the org..

[pskrzybor]

@PabloRoldan I put a random string instead of empty value and then using apex script I updated metadata that had this random string by changing it to null. Probably it is not the best nor most performant workaround but works.

[PabloRoldan]

@pskrzybor I am doing something similar. I quoted all the values and the last one I used "null", then I searched for all <value xsi:type="xsd:string">null</value> and I replaced them with <value xsi:nil="true"/>. Finally, other undefined fields and values are created randomly, so I search for them and delete them using the Find and Replace All tool in VSC. Thanks for the help 👍

[pskrzybor]

@PabloRoldan @nebc-jasonfung Here is the apex script I used. Maybe It is going to be helpful. (You need to deploy the metadata first).

Integer counter = 0;
List<DummyCMDT__mdt> DmcdtList = [
        Select id, DeveloperName, MasterLabel, WhatTheText__c
        From DummyCMDT__mdt
];
Metadata.DeployContainer mdContainer = new Metadata.DeployContainer();
for (DummyCMDT__mdt item : DmcdtList) {
    try {
        if (item.WhatTheText__c.equals('AbcxyZ')) {  //The random string placeholder for null value
            Metadata.CustomMetadata metadataRec = new Metadata.CustomMetadata();
            metadataRec.fullName = 'DummyCMDT__mdt.' + item.DeveloperName;
            metadataRec.label = item.MasterLabel;

            Metadata.CustomMetadataValue WhatTheText = new Metadata.CustomMetadataValue();
            WhatTheText.field = 'WhatTheText__c';
            WhatTheText.value = null;
            metadataRec.values.add(WhatTheText);

            mdContainer.addMetadata(metadataRec);
            counter++;
        }
    } catch (Exception e) {
        System.debug(e);
    }
}
if (counter > 0) {
    try {
        Metadata.Operations.enqueueDeployment(mdContainer, null);
        System.debug(counter + ' Metadata records updated');
    } catch (Exception e) {
        System.debug(e);
    }
} else {
    System.debug('0 Metadata records updated');
}

[PabloRoldan]

Thank you very much @pskrzybor I am going to have a look and see how I can integrate it into my script. This will be really helpful to make it automated. I really appreciate it 👍

[pskrzybor]

Salesforce have created a Known Issue page for this problem. Please click on the "This Issue Affects Me" button to keep yourself notified of the future updates on this bug.

[jshackell-sfdc]

This issue is fixed in 2.20.7 (Dec 6, 2023).