Support Portal ContactGet in touch

Importing New Records into Dynamics via Spreadsheet

   Words by CRM Consultancy

   on 04/10/2018 10:00:00

It is a common requirement to need to take data into Dynamics from an external source such as an Excel Spreadsheet – fortunately Dynamics allows us to do this out of the box using the standard Data Import Functionality.

However Dynamics has two ways this can be implemented as we can either export a Template to populate with entirely new records, or export a list of existing records to then edit and re-import.

Essentially either performing an Import for Creating entirely new data from Rows in Excel, or instead importing the Rows in Excel as a series of Updates to existing records in Dynamics.

In this article, we are going to first look at the Template to Import new records – and then move onto using Excel for a series of Updates.

Use a Template to import new Records into Dynamics

For each Entity in Dynamics we have the option for ‘Download Template for Import’ – this will download an Excel Spreadsheet containing all the Fields for that Entity.


Exporting a Template for the current Entity in Dynamics

Our CRM Team or Users can then populate this Spreadsheet with the details of records they want to add into CRM in bulk


Populating the resulting Template in Excel

Each row here is then imported as a new Record in the selected Entity – we can do this by clicking the IMPORT DATA in Dynamics and supplying our Spreadsheet:


When our Template is ready and populated, we can import back into CRM

Each Import performed will then be tracked as an Import Job in the Data Management area of Dynamics – which we can use to view the success or failure of the Job:


As the Template is importing into CRM, we can tracks its progress in Settings –> Data Management –> Imports


All being well, we then see our New Records imported into Dynamics

The main ‘gotcha’ in this process is Lookup Fields in CRM – to correctly supply values for these fields in the Spreadsheet, we need to exactly specify the Name of the Record in CRM that we want to point the Lookup to.

This makes for a slightly fiddly process of ensuring the Name of each Lookup is correct but then imports without us seeing the following error:


All NOT being well, we can drill into the list of Failures from the Import and see why a Row failed to import from Excel to Dynamics

In the above example, we can see the value of the Currency Field in our Import Spreadsheet is ‘C1’ and Dynamics cannot match this to an existing Currency Type in CRM – when we correct this to ‘USD’ or ‘GBP’, then this imports correctly and inserts the right value into the Lookup Field.

This is great for Data Migration or pulling external data into Dynamics – but not great for Cleansing Activities or modifying existing data in CRM itself.

For this we want to look at a different kind of Export and then Re-Import which we will look at.

Share this Article

Search Articles

Filter Articles

CRM Tech DocMan

Recent Articles

CRMCS Quick Start Guide: How To Produce a Microsoft Teams Live Event Dynamics 365 Marketing: Lead Scoring and Sales Acceptance Designing and Developing Microsoft Power Apps Portals Thank You for Attending CRMCS’ Webinar - Achieving B2B sales excellence with Dynamics 365 & Microsoft Teams Thank You for Attending Our Webinar - Achieving B2B sales excellence with Dynamics 365 & Microsoft Teams Webinar: Discover How CRMCS Have United Dynamics 365, SharePoint and Microsoft Teams To Create Sales Excellence Ignite your workflow by adding DocDrive365 to Office 365 The CRMCS guide to everything you need to know about integrating Teams with Dynamics 365 Saving Time By Keeping Documents In One Place TDE Database Encryption with On Premise Dynamics The Key to Successful Compliance in 2020 Part 2: Let’s get GDPR Compliant with Microsoft Power Automate Top 3 Essential Tips for Remote Working Dynamics 365 Marketing: Top 5 Best Features Dynamics Day in the Life - Puma Investments Can you use Teams to amplify collaboration in Dynamics? Part 1: Using a Scheduled Power Automate to Trigger Expiry Date Reminders The secrets of successful document collaboration in Dynamics CRMCS launches new AppSource approved DocDrive365 Dynamics Day in the Life - Moneypenny Release Management Add the App to Dynamics DocDrive365 Security: Day One - Getting Started with Dynamics to SharePoint Permissions Building a New Scheduled Process using Flow
  • "Paul has made a real difference to how my team of 24 people record and store valuable customer data and sales opportunities. Highly recommended."

    James, Operations Director

  • "Understanding your business allows us to advise when to implement aspects of CRM and, likewise, when not to."

    Paul McQuillan, Managing Director

  • "Dynamics 365 and CRMCS have made a real lasting difference to our business, allowing us to replace older systems that were holding back our performance."

    Grahame, Chief Operating Officer

  • "James worked well with us to help connect CRM with Outlook and relate how this might benefit our team using CRM for Property Care."

    Natalie, Property Care Supervisor

  • "Matt was really good with helping us run User Testing on the new Compliance Module of our CRM System."

    Tom, Compliance Administrator

Prefer to go old-school?

Write to us using the below addresses.

Head Office
CRM Consultancy
61 Oxford Street
M1 6EQ

London Office
CRM Consultancy London
Grosvenor Avenue

Content © CRM Consultancy.