I have a csv file which has 20000 email addresses in it. I need to update some fields on these contacts. the dataloader is not suitable as I don't have the ID field of these email addresses. query in small groups will probably need to repeat more than 100 times.
What is the easiest way to query and update the contacts if I only have a large number of email address as query condition?
Problem is that the email field is NOT necessarily unique. So even the VLOOKUP may end up in matching many ids for one email.
If you would have the email in an external unique id field, you could utilize the smart-lookup feature of workbench https://workbench.developerforce.com/login.php - from all those dataloader-like tools I allways found workbench as very comfortable.
Another idea would be to have a look into a more sophisticated ETL tool like TalenD http://www.talend.com/products/talend-open-studio - there tMap (Palette > Processing > tMap) will be your best friend. It can do way more than VLOOKUP. TalenD comes with excellent Salesforce connectors.
Btw. Workbench and TalenD are both open source and FREE
Not the answer you are looking for but you will need to export the data with email, Contact ID , and the fields you want to and do a vlookup on your CSV. Tip: Use Data Loader so that you get the 18 digit Contact ID.
BTW Demands Tools while not cheap can help with tasks such as these: https://www.crmfusion.com/demandtools/
Well worth it for anyone that does a lot of data quality and data loads.
If you have ms access you can store the contact Ids(18 digit) and email addy in one table and the list you need to update in another table. Join on email address to get the right id.
Demand tools is a great tool however it's find id module does not work well with large data sets. E.g 2 million records in your example
Thanks for all your contributes. I found that if you create a external key in the object and use upsert in dataloader will do the trick.
