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.

image

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

image

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:

image

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:

image

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

image

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:

image

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.

Prefer to go old-school?

Write to us using the below addresses.

Head Office
CRM Consultancy
61 Oxford Street
Manchester
M1 6EQ

London Office
CRM Consultancy London
Grosvenor Avenue
London

Content © CRM Consultancy.