Skip to main content

Modify Salesforce Data from the Command Line

When doing Salesforce data loads, how often are you putting the same value in an entire column? I find this happening when I need to:
  • Move all accounts from one user to another
  • Assign a set of leads to a specific user
  • Set a field to a specific value
Let's use the example from my other post: Salesforce Data Loads from the Command Line - Move all accounts owned by one user to another user.

Magic

Let's break down each piece of this:

soql

This is an alias I set for sfdx force:data:soql:query --query. It saves me from having to type this out each time and let's me query from the command line by just typing soql and my query.

This is a popular StackExchange post that talks about how to set an alias.

cat

This command outputs the contents of a file. It is common to use this command with other commands by piping the output into another command. In our example, we are piping the output into the cut command.

This post gives more examples of how to use the cat command.

cut

This is a command line utility to extract parts of a file. It can be used with csv files by setting the delimiter to a comma. In the example above, I am redirecting the output to a new file so we can maintain a clean backup file.

This command has many capabilities and this post talks about many of them. My most common usage follows this pattern:

cat file.csv | cut -d "," -f 1 > newFile.csv

sed

This is a command line utility to perform find and replace operations on files. In our example, we are appending the OwnerId to the end of each row because our file only contains the Id of each Account. We are using the $ symbol, which tells sed to find the end of the line.

This command has a lot of options and this post covers a lot of them. I commonly use it like this:

sed -e '1 s/$/,Field_To_Append__c/' -e '2,$ s/$/,NewValue/' > outputFile.csv

sfdx force:data:bulk:upsert

This command allows us to upsert data via the Bulk API. It is a very flexible command and I have been using it for most of my data loads ever since I discovered it. In our example we are updating the Accounts in our file by their Ids.

Continued Learning

Here are a few links that will help further your knowledge of manipulating Salesforce data from the command line:

Comments

  1. Thanks for sharing this article here about modify data using the very basic linux commands. Your article is very informative and useful to know more about the process of modify sales force data from the Command Line. Keep sharing this type of articles here.

    ReplyDelete

Post a Comment

Popular posts from this blog

Salesforce Lighting Migration: Plan for Success

In April 2018, I led the Salesforce Lightning Migration at Postmates. This is how I would do it over again if I could.

Define Goals The goals of a Lightning Migration are: Enable Lightning ExperienceConfigure Lightning ExperienceDocument all Salesforce processesCreate training and support materialsLightning Experience is adopted Easy enough right? Let's go though the plan to see how we accomplish each one. Enable Lightning Experience Just turn it on right? Well, kinda.
Although it may be controversial, I recommend turning it on and hiding the option from all users. This can be done by removing the Lightning Experience User permission from your custom profiles. 
And yes, turn it on in production
Why? You're going to turn it on anyway. Salesforce has declared this as the path forward, so you either turn it on now or wait until they flip the switch for you.
Plus, turning this on ahead of time will make deploying from a sandbox much easier. Trust me.
Goal #1 Enable Lightning Exp…

SOQL from the Command Line

SOQL and the Command Line go together better than peanut butter and jelly. Here's why.
Once upon a time, I got tired of running Salesforce Reports to get miscellaneous data. I found Workbench and started using their interface to run SOQL queries. This was great for a while, but I knew there was a better way.

Pro-tip with Workbench: If you set a browser bookmark after your query runs, you can return to that bookmark and it will re-run your query!

Enter the SFDX Command Line Interface (CLI). Get ready to take your productivity into hyperdrive! 🚀🚀🚀
sfdx force:data:soql:query If there's one command you learn, this is the one. All you have to do is pass a query string into the -q parameter and you're in business. Check it out:
-r | --resultformat This is my 2nd favorite thing. How often do you need to export a csv from Salesforce? And how annoying is it to make throwaway Salesforce reports each time? Yeah, it's the worst.
-r to the rescue! Just add "-r csv" to …

Why should I use SOQL?

You need data, fast!
Whether it's exporting a csv or answering a question, SOQL can get you the result much faster than creating a Salesforce report.

My top 3 reasons to use SOQL are:
Get data fastGet answers fastFlexible access Get Data Fast How often do you find yourself making a throwaway report to check what is happening in the system? For example, you updated your Lead routing and want to see if it's been assigning Leads as you expect. Well you could make a report, run it and review the results. Or you could run a SOQL query.
The benefits of running a SOQL query in this case is that you can retrieve different data sets much quicker. In a Salesforce report, you are stuck with the Report Type you selected when creating the report. With SOQL, you can traverse object relationships in a much more natural way. Get Answers Fast Have you ever been in a meeting and someone asks a data question that no one has the answer to? Well SOQL comes in handy here because you can run a quick …