11

I've got a automated Cron-like job that exports some data from Salesforce on a regular basis. The goal is to regularly export SFDC data to csv or tsv.

I'm using the sfdx tool to perform this query - I had originally used the Force CLI, but had issues automating Ouath2 Login, so I switched to the sfdx tool.

It works great, except for one issue: the data it exports to stdout isn't neatly delimited. I can export it to a text file that looks visually clean, but because it uses inconsistent delimiters per row, I can't neatly export to csv or tsv.

Is there a way to export to csv or tsv from the sfdx CLI?

2 Answers 2

16

There is --resultformat on query.

sfdx force:data:soql:query -q "SELECT Id,Name,Status__c FROM Property__c" --resultformat csv`

I got that from the command help. See below.

-> sfdx force:data:soql:query --help
Usage: sfdx force:data:soql:query -q <string> [-t] [-r <string>] [--
perfloglevel <string>] [-u <string>] [--json] [--loglevel <string>] 

execute a SOQL query

Flags:
 -q, --query QUERY                    (required) SOQL query to execute
 -r, --resultformat RESULTFORMAT      query result format emitted to stdout; --json flag overrides this parameter (human*,csv,json)
 -u, --targetusername TARGETUSERNAME  username or alias for the target org; overrides default target org
 -t, --usetoolingapi                  execute query with Tooling API
 --json                               format output as json
 --loglevel LOGLEVEL                  logging level for this command invocation (error*,trace,debug,info,warn,fatal)
 --perfloglevel PERFLOGLEVEL          get API performance data. (basic*,detailed)

When you execute this command in a project, it executes the query against the data in your default scratch org.

To get data on API performance metrics, specify both --perfloglevel and --json.

Examples:
   $ sfdx force:data:soql:query -q "SELECT Id, Name, Account.Name FROM Contact"
   $ sfdx force:data:soql:query -q "SELECT Id, Name FROM Account WHERE ShippingState IN ('CA', 'NY')"
   $ sfdx force:data:soql:query -q "SELECT Name FROM ApexTrigger" -t
   $ sfdx force:data:soql:query -q "SELECT Name FROM ApexTrigger" --perfloglevel "basic" --json`
3
  • Nice! I completely missed they added this output format.
    – pchittum
    Commented Apr 5, 2018 at 15:07
  • 1
    It's insanely annoying that if zero rows are returned, you get a CSV file with the words "Your query returned no rows" in it, instead of just the header row :(
    – Chris KL
    Commented Aug 8, 2019 at 8:19
  • If you are running the sfdx command from a bash terminal, to get the data into a file, end the command with a redirect operator & a file location. Maybe everyone knows that but if you aren't as familiar with shells, here's an example: sfdx force:data:soql:query --query "SELECT Id FROM Account LIMIT 10" --resultformat=csv > data.csv The key being the '>' to redirect the output of the command into "data.csv". You could also use '>>' if you want to append the output from the command onto an existing file.
    – torpy
    Commented Jan 20, 2023 at 20:18
7

You're right that you cannot output to to CSV, but every command on the Salesforce DX command line has a JSON output option by using the --json option or setting a parameter to make DX default to JSON output as can be seen in the docs.

Once you've done this, you have a well structured predictable data structure to convert to CSV, and there are a lot of ways to do this.

If you're using bash or some other Unix shell, the jq package is a pretty well known JSON utility. I've not sat down to tackle it all yet, as it has a pretty hefty syntax to grok. It's meant for complex JSON manipulations, but can do JSON to CSV.

For flat result sets (no relationship queries) if you want a utility a bit more straight forward, I've been using the jsonv package, which I have found a good basic JSON-to-CSV option. Let's say I have the following query:

$ sfdx force:data:soql:query -q "SELECT Id,Name,Status__c FROM Property__c" --json

By using the --json param, I get output to JSON format.

I can then take the output, and either write to a local JSON file and then call jsonv on the file, or I can pipe the JSON directly to jsonv then write to file.

$ sfdx force:data:soql:query -q "SELECT Id,Name,Status__c FROM Property__c" --json | jsonv Id,Name,Status__c result.records > properties.csv 

This looks like the following:

"a046E0000042ZgAQAU","Stunning Victorian","Available"
"a046E0000042ZgBQAU","Ultimate Sophistication","Available"
"a046E0000042ZgCQAU","Modern City Living","Available"
"a046E0000042ZgDQAU","Stunning Colonial","Available"
...

Here's where you can see the downside of jsonv which is there is no header. My work around has been to append a static header.

$ sfdx force:data:soql:query -q "SELECT Id,Name,Status__c FROM Property__c" --json | jsonv Id,Name,Status__c result.records | cat ../data/header.csv - > properties.csv 

This works for quick and dirty. For a more flexible option, the jq utility can actually read the headers from the JSON and use those directly in the CSV. [I'll try to get back with an example in a bit.]

In my efforts to acquaint Salesforce developers and admins with command line tools, I've been giving talks to share about core command line usage. One of the examples I show is precisely this process. (Caveat: Although mine shows a data transformation and immediate write back to my org, please don't do that in production!)

All of the above should work just fine in Windows 10 using Windows Subsystem for Linux. However if you're not using that, this post on another StackExchange has some recommendations for how to do JSON-to-CSV conversion on Windows, including a standard PowerShell utility, and using a python script.

For all the nodejs fans out there, it is also worth mentioning that you could use a nodejs JSON utility, like json2csv. Or maybe you could write your own Salesforce CLI plugin, if you were so motivated.

JSON is definitely your friend, and while it's not an exact fit if the destination of your data is expecting CSV, JSON is so widely supported and uses a nice clean structure making it pretty straightforward to shape it into CSV when needed.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .