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.

  • "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.