Share
ReportIs it possible to compare an excel list of contacts to the contact collection in Dynamics and then export data on any matches?
You’re offline. This is a read only version of the page.
5
Is it possible to compare an excel list of contacts to the contact collection in Dynamics and then export data on any matches?
Hi @skoups212,
One more vote from me for using Power Autome.
We have done something similar when validating customer details they give us while on the phone to the record we have in the backend. It works nicely.
Your scenario is even easier because you don't need it so real time.
Good luck,
-DD
Hi,
Have you investigated using PowerBI for this? Both dynamics data and Excel data can be used as sources for a PowerBI report
Alternatively, another way would be to use Power Automate (flow) to load an excel sheet, iterate over each row, verify with Dynamics if a contact is found (and optionally set a flag on the contact)
Hope this helps, please reply for any further questions.
Thanks,
Shravan
Hi skoups212,
Thanks for sharing details of your requirement, I think you could use organizationService in a C# console application to do it.
*. Define an array to save matching records.(Optional)
1. Create a function to retrieve matching records in Dynamics, if current record can match to record in Dynamics, then return true. (Let's define the function name is retrieverFun(col1, col2) )
2. Import Microsoft.Office.Interop.Excel library into application to read excel file
3. In iteration over a row, pass columns value you want to retrieverFun, if the result equals true, then push current row into array,
or change current row background color by using Microsoft.Office.Interop.Excel library.
4. Create a new excel from data in array, or when iteration ends, the background color of matching rows have changed.
You could take tutorial below as reference for start of using organization service:
Regards,
Clofly
Thank you for the response. To clarify the request - we would be looking to compare a list of names in excel to the dynamics database of names. The excel file would have several hundred contacts vs the dynamics database containing roughly 1/2 million contacts. We are interested in identifying any matches in dynamic to the excel than producing a report on the matching contacts with dynamics data.
A real world example would be receiving a list of conference attendees and wanting to validate which of those attendees are in Dynamics and produce a report outlining certain dynamics data.
Thank you again.
Hi skoups212,
We couldn't do comparison during export, because the exported excel file will start to download immediately when click "Export to Excel" button, the process is not customizable.
As far as I know, here are some available operations for data import & export.
1. We can export existing contacts and change their information, then upload the modified list for bulk update.
2. We can download excel template, it will still contains existing contacts, but we can delete these rows then copy record rows from other excel list of contacts as new rows.
For your requirement, a workaround is that you could do comparison locally by code, if any of row matches, then create another excel file to save matching rows.
I built a javascript version for this purpose, it'll convert all rows in excel file to JSON object with sheet.js library, which is easy to customize, you could take it as reference to check whether it could meet your requirement.
All responses (
Answers (
Title
Excel reader:
Ouput
Regards,
Clofly
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,214
Most Valuable Professional
#3
nmaenpaa
101,156