Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics CRM (Archived) / How to update data wit...
Microsoft Dynamics CRM (Archived)

How to update data without the 3 system column from the excel file

(0) ShareShare
ReportReport
Posted on by Clem 2,541

Hello everyone,

We recently moved to CRM Online 2016. I've seen that now when we export data from the CRM itself, there are 3 new hidden columns used to match the record to the record to update. I got it.

I have a file with let's say 5000 contacts in it. I don't know yet if i have some of them in the CRM already or not. So i don't have the Guid, Checksum and Modified On. 
Question is : How do i manage to push the CRM to understand that the line 405 from my excel already exist and shouldn't be created without the 3 hidden columns filled ?

Today, it's blocked becasue those records which are not catch as existing records to update, are considered as Creation and our duplicate rules is blocking it.

Am i missing a point here or this is how it works ?

Thanks a lot,

Clément

*This post is locked for comments

  • HurleySk Profile Picture
    HurleySk 20 on at
    RE: How to update data without the 3 system column from the excel file

    I like this approach better, personally.

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: How to update data without the 3 system column from the excel file

    Alternative approach:

    Create a custom entity called "Contact Updater" (say) which has a lookup (N:1 relationship) to Contact.

    Add fields to the custom entity which match the fields you are trying to update on the Contact record.

    Build a simple Workflow that runs on create of the new entity and updates the parent Contact record to copy the data from each field on the Contact Updater.

    Now import your spreadsheet as "Contact Updater" records and let them update the Contacts for you.

  • Clem Profile Picture
    Clem 2,541 on at
    RE: How to update data without the 3 system column from the excel file

    This "Mark for re-import" needs to be checked while user is exporting data from the CRM. (By the way, where is this option when we export a file on the CRM Online ?)

    So if i sum up, the user would need to export data from the CRM and then use this file to add additional contacts here right ?

    Because my users are usually doing the opposite : Creating a file from excel let's say an old one and they are not using the brand new export with all guids in it.

    But i will tell them to do so :)

    Thanks,

    Clément

  • ScottDurow Profile Picture
    ScottDurow 50,177 on at
    RE: How to update data without the 3 system column from the excel file

    Hi Clement,

    If it would help to get the 3 columns with the Contact ID in it- you can export and check the 'mark for re-import' - this adds the 3 hidden columns.

    Hope this helps

  • Clem Profile Picture
    Clem 2,541 on at
    RE: How to update data without the 3 system column from the excel file

    Hello Scott,

    Thanks for your answer, which join my point of view based on a developper view i would say.

    Of course, it's much more easier to know if it needs to be updated on created based on an existing guid or not.

    But the thing is, on an OnPrem environment when we were exporting data there are no extra columns like in the online version.

    So i'd expect to have the same behavior more or less which save time to my user.

    The idea with running the duplicate job is a good one but since i have a lot of users who are doing imports i'm afraid a duplicate jobs could catch records not created by the user who ran the duplicate job and he won't know what to do.

    So yeah i'm a bit disappointed for this. :)

    Clément

  • Suggested answer
    ScottDurow Profile Picture
    ScottDurow 50,177 on at
    RE: How to update data without the 3 system column from the excel file

    Hi Clement,

    This is how it is meant to work if you import a file that doesn't have the CRM ID columns - you enable duplicate detection rules and the import will not import the rows that are duplicates. You will then need to export the rows that are already in CRM and mark for re-import, update the excel and then re-import to update the data.

    Alternatively you can import all of your records without duplicate detection - then run a duplicate detection job to find the duplicates - and use the merge company/contact function to combine the data for each record.

    Alternatively you can use the amazing upsert action in Kingswaysofts SSIS toolkit - www.kingswaysoft.com/.../ssis-integration-toolkit-for-microsoft-dynamics-crm

    Hope this answers your question

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,269 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,198 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans