1

I have a list of 5K id's for which I need data extracted from Salesforce. Is there any way I can feed this list into SF. The only tool my organization has is DataLoader & am not sure how to feed in the list of id's via dataloader.

2 Answers 2

3

This is a very interesting situation, I would think initially about using ETL tool but on thinking it differently I believe it's possible to do it using data loader.

  1. Create a Field on your object, isReadyForExport__c(Checkbox) , default as unchecked false

  2. Edit your CSV and add a header, for Field isReadyForExport__c and set it as True

  3. Use data loader Update to load the CSV

  4. Now use a query, SELECT YourFields FROM Your Object WHERE isReadyForExport__c = TRUE

Bang on done.

1

Pranay's suggestion is a good one. I just want to add a workaround for situations like I've encountered in the past where making a change to the schema is not permitted. This is an uglier solution.

You can take your 5,000 Ids and convert them into several SOQL queries that you can run through the Bulk API in Data Loader's Export module. The query "template", so to speak, looks like this:

SELECT Id, ... other fields... 
FROM My_Object__c 
WHERE Id IN ('001000000000000', '001000000000001',  ...)

The trick is

  1. You have to turn the Ids into a quoted list.
  2. The WHERE clause has a length limit of 4,000 characters.

You can solve (1) with a regular expression in an editor like Visual Studio Code, and (2) with a bit of math and repetition.

Grab your Ids and paste them into Visual Studio Code. You'll end up with something like this:

001000000000000
001000000000001

and so on. Open the Find and Replace pane, and turn on regular expressions (.*). Search for the regex ^(.*)$ and replace with '$1',

That gets you

'001000000000000',
'001000000000001',

which is ready to be dropped into the query template above.

The next issue is that we have to chunk the Ids so that our WHERE clause is never more than 4,000 characters long. Our base WHERE clause is WHERE Id IN (), which is 14 characters. Each Id consumes either 15 or 18 characters (we'll say 15 here), plus four (two quotes, a comma, and a newline) = 19 characters. That means we have

(4000 - 14) / 19 = ~209

Ids available per query, which means you'll be copying and pasting 24 sets of Ids into the query template and running through the Data Loader for export, then copying and pasting the results into a single spreadsheet file.

Is it tedious? Yes, absolutely. But it works, and it works with no changes to your schema.

If you're comfortable with scripting, this process can be made a lot easier with a bit of Python. I've previously written a couple of answers that summarize how to do this:

You must log in to answer this question.

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