I'm a huge fan of the Query Editor in the SFDC Developer Console:
Is there a way to export the table of results as JSON/csv/excel or in some format that can be consumed by Excel?
(I know that I can replicate the functionality in DemandTools or other tools, but sometimes when I've thrown up a query for debugging, it would be really handy to be able to export and manipulate the results.)
The Developer Workbench will allow you to, among other things, build SOQL queries and save the results as CSV: https://workbench.developerforce.com/login.php
UPDATE
For faster use add a bookmark, pick a name and paste the following code to the URL:
javascript:(function(){function e(e){var t=new RegExp(/["]/g),n=e.replace(t,"“"),t=new RegExp(/\<[^\<]+\>/g),n=n.replace(t,"");return""==n?"":'"'+n+'"'}for(var t=document.evaluate("//div[@id='editors-body']/div[not(contains(@style,'display:none') or contains(@style,'display: none'))]//table/tbody/tr",document,null,0,null),n=[];row=t.iterateNext();){for(var o=row.getElementsByTagName("td"),a=[],r=0;r<o.length;r++)a.push(e(o[r].textContent));n.push(a)}for(var d="data:text/csv;charset=utf-8,filename=download.csv,",l=[],r=0;r<n.length;r++)l.push(n[r].join(","));d+=l.join("\r\n");var c=document.createElement("a");c.setAttribute("href",encodeURI(d)),c.setAttribute("download","dev_console.csv"),document.body.appendChild(c),c.click()})();
Save it. Now with one click it will download console results into csv file. (Tested in Chrome)
ORIGINAL ANSWER
I have a quick solution which works for me since I do a lot of exports from console. Script opens a new window with raw data in csv format and a link to a downloadable csv file. My script works in Chrome, didn't check it in other browsers.
var o = document.evaluate("//div[@id='editors-body']/div[not(contains(@style,'display:none') or contains(@style,'display: none'))]//table/tbody/tr",document,null,0,null);
var r = [];
while(row = o.iterateNext()){
var cols = row.getElementsByTagName('td');
var a = [];
for(var i=0; i<cols.length; i++){
a.push( formatData( cols[i].textContent ) );
}
r.push( a );
}
// generating csv file
var csv = "data:text/csv;charset=utf-8,filename=download.csv,";
var rows = [];
for(var i=0; i<r.length; i++){
rows.push( r[i].join(",") );
}
csv += rows.join('\r\n');
popup(csv);
function formatData(input) {
// replace " with “
var regexp = new RegExp(/["]/g);
var output = input.replace(regexp, "“");
//HTML
var regexp = new RegExp(/\<[^\<]+\>/g);
var output = output.replace(regexp, "");
if (output == "") return '';
return '"' + output + '"';
}
// showing data in window for copy/ paste
function popup(data) {
var generator = window.open('', 'csv', 'height=400,width=600');
generator.document.write('<html><head><title>CSV</title>');
generator.document.write('</head><body style="overflow: hidden;">');
generator.document.write('<a href="'+encodeURI(csv)+'" download="Sf_export.csv">Download CSV</a><br>');
generator.document.write('<textArea style="width: 100%; height: 97%;" wrap="off" >');
generator.document.write(data);
generator.document.write('</textArea>');
generator.document.write('</body></html>');
generator.document.close();
return true;
}
aside.io, it's kind of browser IDE and they let you export any results and edit / remove query results. Worth a try!
I have a quick and dirty solution :()
First open a console and query some records. Then open a FireBug window (F12, i hope you have it). Select the query results table with a firebug's select arrow. You need to turn off the css styles -moz-user-select: none for the <div id="gridview-1182" to be able to select the result text. Then select all results just with a mouse, copy and paste it to Excel. Works great for me!
Step 1: activate user select
Step 2: select results
Step 3: copy & paste it to excel
<table> that all the data was in, and copied it to a new HTML file. From there, I had an unformatted HTML table that I could easily copy into Excel 🙂
Commented
Jul 13, 2022 at 14:01
x-unselectable and you should be able to select all the text! Something like: <div id="gridview-1506" class="x-grid-view x-fit-item x-grid-view-default" style="overflow: auto; margin: 0px; width: 1914px; height: 611px;" tabindex="-1">
Commented
Feb 22, 2023 at 13:42
Download the SDFC Dev Console Data Exporter Chrome's extension to get the data in CSV format.
Here is a simple way:
I found a tool which I use for just exports from console. It's kind of free browser IDE called aside.io where you can actually export in csv format, bulk delete or do other things with queries.
For me it's much better than custom scripts. Disclaimer: I'm not associated with aside.io in any ways.
What you are looking for is dataloader.io you can select your object, columns and make a custom select query, then export all to CSV.
I see a few others have mentioned external tools. I help make the FuseIT SFDC Explorer, which is free from my current previous employer.
With it you can export the results to CSV or directly to the clipboard.
Another approach is to use the Execute Anonymous Window. Especially useful if a straightforward query doesn't refine your data enough. Here's trivial example finding contacts from a phone area then filtering account names less than 11 characters long.
Note the extra comma before the first field to separate the debug info from the other fields. Also, Excel treats some numeric text as numbers and can corrupt them. Prefix with a letter to avoid this.
Contact[] queriedObjects = [Select ID, Name, Account.Name, MobilePhone, Phone
FROM Contact WHERE Phone LIKE '0161%' ORDER BY lastname];
system.debug(queriedObjects.size());
Integer Count =0;
String strTest;
for (Contact e : queriedObjects) {
strTest = e.Name;
if (strTest.length() <= 10){
Count += 1;
system.debug(','+e.Name + ', T'+ e.Phone + ', '+e.Account.Name);
}//endif
}//next
system.debug(count);
Execute the code; Download the debug log to text; In notepad, remove unwanted debug lines before and after the actual desired field values; Save as csv and open in Excel; Remove the first column (the debug statements).
system.debug(',"'+e.Name + '", "T'+ e.Phone + '","'+e.Account.Name +'"');
Commented
Jul 25 at 15:02
All these solutions point to the same answer: no - Dev Console doesn't support it natively, and HTML or Javascript tricks are simply hacks to workaround this limitation.
The Salesforce supported way of doing this is to use SOQL Builder. When you go to Workbench, it will suggest this approach.
SOQL Builder is a component of Salesforce for VSCode.
You can also generate CSVs directly from the command line via the CLI using the flag -r csv.
For example:
sfdx data:query --query "select name, email, createddate from contact" -r csv
Yields:
Name,Email,CreatedDate
Teddy Cione,[email protected],2023-06-29T21:21:33.000+0000
Milo Deacy,[email protected],2023-06-29T21:21:33.000+0000
Kennith E. Grief,[email protected],2023-06-29T21:21:33.000+0000Kristyn L. Neeson,[email protected],2023-06-29T21:21:33.000+0000
Rosella Bentancourt,[email protected],2023-06-29T21:21:33.000+0000
And for fields that have commas: sfdx data:query --query "select name createddate from account limit 5" -r csv
Yields:
"Wisteria Pumpkin, Inc.",2023-06-29T21:10:28.000+0000
"Pumpkin, LP",2023-06-29T21:10:28.000+0000
"Cantaloupe, LLC",2023-06-29T21:10:28.000+0000
"Melon Cranberry, L.P.",2023-06-29T21:10:28.000+0000
"Mulberry, LLP",2023-06-29T21:10:28.000+0000
the important thing is the query, not the UI. You can get the query right in Dev Console, and then when you want the data, run it from the command line:
command line documentation for SOQL query execution
for example: sf data query --query "SELECT Id, Name, Account.Name FROM Contact"
The CLI includes flags for CSV and JSON, and flag for the local file you'd like to save the data to.
I was trying to do the same with Salesforce REST API, and found that it has limits which will prevent you from pulling all the rows you need if your result set is larger than a few thousands. After some research, I found the Bulk API 2.0 is your friend.
It works in an asynchronous fashion though, and has the basic flow:
Here is a dirty python script I used to do the same.
import requests
import time
import csv
# Define parameters
instance_url = "{YOUR_INSTANCE_URL}"
access_token = "{YOUR_TOKEN}"
query = "select Id, Name, Type from Account " # Replace with your SOQL query
output_file_path = "C:\\my_data.csv" # Replace with your desired CSV file path
def create_bulk_job():
url = f"{instance_url}/services/data/v57.0/jobs/query"
headers = {
"Authorization": f"Bearer {access_token}",
"Content-Type": "application/json"
}
payload = {
"operation": "query",
"query": query,
"contentType": "CSV"
}
response = requests.post(url, headers=headers, json=payload)
if response.status_code != 200:
print(f"Error creating job: {response.status_code} - {response.text}")
return None
return response.json()["id"]
def check_job_status(job_id):
url = f"{instance_url}/services/data/v57.0/jobs/query/{job_id}"
headers = {"Authorization": f"Bearer {access_token}"}
while True:
response = requests.get(url, headers=headers)
if response.status_code != 200:
print(f"Error checking job status: {response.status_code} - {response.text}")
return None
job_info = response.json()
status = job_info["state"]
if status in ["JobComplete", "Failed", "Aborted"]:
return job_info
print("Job is in progress, waiting...")
time.sleep(10)
def download_results(job_id):
url = f"{instance_url}/services/data/v57.0/jobs/query/{job_id}/results"
headers = {"Authorization": f"Bearer {access_token}"}
response = requests.get(url, headers=headers, stream=True)
if response.status_code != 200:
print(f"Error downloading results: {response.status_code} - {response.text}")
return None
return response.text
# Start the bulk query process
print("Creating Bulk API Job...")
job_id = create_bulk_job()
if not job_id:
exit()
print(f"Job created with ID: {job_id}. Checking status...")
job_info = check_job_status(job_id)
if not job_info or job_info["state"] != "JobComplete":
print("Job did not complete successfully.")
exit()
print("Downloading results...")
csv_data = download_results(job_id)
if not csv_data:
exit()
# Write results to CSV file
with open(output_file_path, mode="w", newline="", encoding="utf-8") as file:
file.write(csv_data)
print(f"Query results successfully written to {output_file_path}")
