Export Option Set Method in Microsoft CRM 2016

Posted on: March 23, 2017 | By: Craig Thompson | Microsoft Dynamics CRM

    In Microsoft Dynamics CRM, it is generally easy to export data using the built-in export tools. One of the questions that arises occasionally is – Can you export option set data? In general, the answer is – it’s very difficult. CRM does not provide a method to export a list of option set names or associated codes to a csv file like other data.

An option set may need to be duplicated on another entity, or a Global Option set needs to be built. If the option set is minimal- say no more than 20-30 items, it is often easier and quicker to just re-type them in to the new option set. Recently, we had a requirement to convert an option set that had grown to an unwieldy 100+ items, and needed to be converted to a lookup. Retyping all this data would have taken hours. A quick Google search shows that one method is to use the XRMToolBox metadata generator to export this data, but the actual steps are generally not detailed. Below is a detailed method we have used successfully for several years to extract this data in a useable format.

 

How To:

The first requirement is to download the XRMToolBox here. This is a free download. Just follow the instructions for download and install to your local desktop. When you fire this up, you will notice it has many other tools that can be helpful for CRM configuration. The second step in configuring this tool for use is to set a connection to the CRM instance that you want to pull data from. This can be done by entering credentials under the “Manage Connections” Menu choice as below.

Once you are connected, simply Scroll down the page of the XRMToolBox to the Metadata Document Generator, and click on it to open it.

This bring ups a page to retrieve the entity and field that you want to export. Basically, you retrieve the entity and then select the field(s) that you want to export. You can export all fields, but if you want just one field to extract the picklist items, just select the one field using the Generate For Selection – Selected attribute, then scroll down on the left and select the field that you want to export and select generate document. 

 

This will generate an excel document with the option set in one long cell. The trick is to convert this to a multi-line column, and separate the option set name from the underlying values. The easiest method that I have come across is to copy this one cell to a word document. If the cell contains hundreds of lines, the document can be several pages long.

The next step is to simply copy this block of text back to a new excel spreadsheet. Excel will automatically split each line into a cell. At this point we have a more familiar spreadsheet of data, but there is still one step left.

The final step is to split values from the Option name into two columns. This is accomplished by using the text to column data function in excel.

1. Highlight the column with the data to split

2. Go to the Data tab, and select text to column function

3. It will open a wizard- select the delimited file type, hit next

Select other, and insert a colon : as the delimiter, then hit next, then finish.

The column will now be split into two columns that can then be converted to a csv file and used to re-import into CRM.

 

Do you have a question on how to use certain functionality in Microsoft Dynamics CRM? Contact us here at Logan Consulting!

---

---