Maintaining key organization data in Excel spreadsheets is a surprisingly common business scenario that we encounter with regularity. One might think that in this day and age most business data would be managed in database driven applications. But often, due to a variety of reasons such as segmented business units, mergers and acquisitions, or rapid business growth, the data management strategies and policies are often skipped, skimmed or abbreviated in an effort to ‘just get it done’ so that companies can perform and concentrate on their core business activity, which most likely is not internal data management.
The result is segmented data, often stored in spreadsheets, that is maintained and managed by individuals and emailed or shared with others when requested. This highlights a host of potential issues, such as:
- Lack of data security
- Potential data loss due to lack of backups and disaster recovery plans
- Duplication of data
- Unstructured data, that is:
- Difficult to quantify
- Difficult to act upon
- Difficult to forecast from and derive business intelligence data
All of the risks outlined above can be mitigated with Dynamics 365. A recent use case that I encountered was solved by leveraging Dynamics 365 online and the native data import and data update capabilities provided by the platform. The following outlines the steps taken to accomplish the data migration task of importing (and updating) data from Excel spreadsheets into Dynamics 365 for Sales.
- Analyze spreadsheet data and ensure that Dynamics 365 has the entity and field structure to accommodate the data with the correct data types.
- Update Dynamics 365 as necessary per #1.
- Create an import data map using Dynamics 365 for Sales Data Management functionality via the Import Data wizard and import the data.
- The wizard allows a user to browse to a supported data file type (.xml, .csv, .txt, .xlsx and .zip) that contains the source data to be imported.
- I like to save my source data Excel files as .csv (comma separate value) format. I have experience problems with .xlsx format in some situations.
- Choose the Default (Automatic Mapping) and then correct any irregularities in the auto-mapping for the entity type. If the source column names match the field names in Dynamics 365, then auto-mapping will find the matches and map the source columns to the destination (Dynamics 365) fields. In my case, Contact data with fields including First Name, Last Name and Email.
-
- One trick I like to do, that will be useful when later updating data, is to earmark the data that I am importing by way of an import source field. This allows me to easily and separately track the data from each data import. A virtual popcorn trail. In this example I created a new field called Import Source and mapped a column from my spreadsheet that contained a description and date of the import.
- After reviewing the data map for accurate mappings and making any necessary adjustments, the data map can be optionally saved for future re-use.
- The data import is then submitted and can be monitored in Settings > Data Management > Imports.
Now we should have our source data from Excel neatly stored into our Dynamics 365 database by entity. Depending on the complexity of the source data, multiple imports may be required to accommodate related data for items such as Account and Contact data, where a Contact is related to a known specific Account record. This process may require additional steps and possibly reorganization and consolidation of the spreadsheet data into a more logical format that will match the target entity structure and fields as built in Dynamics 365.
Assuming our simple use case with just Contact data, let’s say that the source Excel spreadsheet was further updated after our initial import in Dynamics 365 and now we need to update Dynamics with the changes. Several options exist to accomplish this task. The one that I want to outline involves the use of a filtered Contacts view and the Export to Worksheet feature in Dynamics 365.
First, create a new Advanced Find view for the Contact entity that filters the records based on our Import Source field that we added and used during the initial import. This will provide a record-set that will match the data in the Excel spreadsheet, assuming the spreadsheet data has the same records, whose data has only been updated.
Run the query and from the results tab, choose the Data toolbar item and select Export Contacts > Dynamic Worksheet (if using Dynamics 365 for Outlook) or Static Worksheet. Configure the columns to match those from in the spreadsheet. This step can also be done when the view is created and before the Export step. Choose export and open the spreadsheet. Update the data as necessary, i.e., from the data in the updated original spreadsheet and save. Then use the import wizard to import the worksheet and the updates will be processed rather than strictly new inserts. Additional details are available here: https://www.microsoft.com/en-us/dynamics/crm-customer-center/export-to-an-excel-dynamic-worksheet.aspx
As previously mentioned, there are several ways to accomplish these same tasks. I hope this provides an alternative that may not always be the most obvious, but has proven useful to me in the past. The main goal is to get that spreadsheet data into Dynamics and keep it structured, organized and useful to help drive your business! Let me know what you think.
