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:
"a046E0000042ZgCQAU","Modern City Living","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.