How to use sfdx force:data:bulk:upsert on an object without an external ID?

Question

I have a custom object (CustomObject__c) which is a child of another custom object. I want to import some records (CSV files) of CustomObject__c via SFDX (force:data:bulk:upsert).

These are the custom fields of the CustomObject__c

• Name (Text (80))
• Parent_Object__c (Master-Detail)
• Locale__c (Lookup)

The problem is, CustomObject__c does not have any external ID (I cannot assign any relationship field to become the external ID) and sfdx force:data:bulk:upsert needs an external ID.

I know there are Data Loader and Data Import, but I want to create a script and automate the process of creating a new scratch org later. I also know that there is force:data:tree:import but it requires JSON format and what we have right now is CSV.

Is there any workaround for this?

EDIT:

I have imported the parent object records before executing this command.

Command:

sfdx force:data:bulk:upsert -s CustomObject__c -f csv/CustomObject.csv -i Id

CustomObject.csv:

Parent_Object__c,Name,Locale__c
tv,TV,de_DE
tv,TV,en_US
phone,Phone,en_US
phone,Handy,de_DE
car,Car,en_US
car,Auto,de_DE

Result:

=== Batch Status
jobId:                   7501X000001ABCDE
state:                   Completed
createdDate:             2019-08-06T13:29:37.000Z
systemModstamp:          2019-08-06T13:29:46.000Z
numberRecordsProcessed:  6
numberRecordsFailed:     6
totalProcessingTime:     91
apiActiveProcessingTime: 2
apexProcessingTime:      0

Error:

=== Upsert errors
MALFORMED_ID:Parent Object: id value of incorrect type: tv:Parent_Object__c --
MALFORMED_ID:Parent Object: id value of incorrect type: tv:Parent_Object__c --
MALFORMED_ID:Parent Object: id value of incorrect type: phone:Parent_Object__c --
MALFORMED_ID:Parent Object: id value of incorrect type: phone:Parent_Object__c --
MALFORMED_ID:Parent Object: id value of incorrect type: car:Parent_Object__c --
MALFORMED_ID:Parent Object: id value of incorrect type: car:Parent_Object__c --

EDIT 2:

CustomObject_rev1.csv

Child_relationship_Name__r.Code__c,Name,Locale__c
tv,TV,de_DE
tv,TV,en_US
phone,Phone,en_US
phone,Handy,de_DE
car,Car,en_US
car,Auto,de_DE

I got an Error:

ERROR running force:data:bulk:upsert: InvalidBatch : Unable to find relationship: Child_relationship_Name__r

Answer 1

Have you tried using Id as external ID field?

The documentation uses it in an example:
$ sfdx force:data:bulk:upsert -s MyObject__c -f ./path/to/file.csv -i Id -w 2

In addition to that, if you want to use external ids from related object like you do in your example, you need to reference them in the csv column headers:

Parent_Object__r.ExternalIdField__c,Name,Locale__r.AnotherExternalIdField__c
tv,TV,de_DE
tv,TV,en_US
phone,Phone,en_US
phone,Handy,de_DE
car,Car,en_US
car,Auto,de_DE

Answer 2

I know of at least three options for you.

sfdx force:data:tree:import should work fine for your situation if you're willing to switch to JSON format for your data storage. The easiest way to do this would be to load up your sample data in an org and retrieve it using sfdx force:data:tree:export, rather than generating the data by hand.

If you really want to stick with CSV format, you can use Amaxa to load multiple objects with relationships. I wrote Amaxa for this specific use case, and its native storage format is CSV. It is free and open source. You'd use a simple YAML definition file like this:

version: 1
operation:
    -
        sobject: Parent_Object__c
        fields:
            - Id
            - Name
        extract:
            all: True
    -
        sobject: Custom_Object__c
        fields:
            - Id
            - Name
            - Locale__c
            - Parent_Object__c
        extract:
            descendents: True

That would load up Parent_Object__c records from Parent_Object__c.csv, then load Custom_Object__c records from Custom_Object__c.csv and map Parent_Object__c Ids to preserve relationships.

Another option if you're willing to switch formats is Salesforce.org's CumulusCI (CCI) continuous integration/orchestration tool, which is also free and open source.

CCI works with Salesforce DX and provides configurable bulk data load and extract tasks, which use a simple YAML mapping format to convert data to and from a SQLite data store.

The nice thing about CCI is that it works as part of an end-to-end scratch org build automation process that you can define in markup and run repeatably. (Disclaimer: I am on the team that builds CCI at Salesforce.org).