Customer experience | Sales, Customer Insights,...

List Comparison - Excel Contacts

(0) Share

Report

Posted on by skoups212

Is it possible to compare an excel list of contacts to the contact collection in Dynamics and then export data on any matches?

I have the same question (0)

All responses (5)

Answers (0)

cloflyMao 25,210 on at

Like (0)

Report
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.

Title

Excel reader:

Choose file

Ouput

Regards,

Clofly

Was this reply helpful? Yes No
skoups212 5 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
cloflyMao 25,210 on at

Like (0)

Report

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:

https://arunpotti.wordpress.com/2018/02/03/step-by-step-to-connect-dynamics-365-crm-online-v9-x-using-c-console-application/

Regards,

Clofly

Was this reply helpful? Yes No
Suggested answer

ShravanSuri 1,255 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Suggested answer

d_radulova 2 User Group Leader on at

Like (0)

Report

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

Was this reply helpful? Yes No