Blogs

Dynamics 365 Sales data import. Anomalous materials and unforeseen consequences

Inkey, October 28, 2021 1014 Views

We all have imported data into CRM at some point in our life. And a part of the process of importing data is cleaning up the data the is to be imported. I am sure almost all of us have encountered some unusual data that we had to clean up and some unusual data that looks usual but becomes unusual once imported.  

While the CRM does give good information about what went wrong, sometimes understanding why it went wrong can leave us completely baffled. Recently we had two cases in data import that had us scratching our heads trying to figure out both what and why. Both of these dealt with option sets with one of them being a multi select option set. 

Anomalous material #1, unforeseen consequence #1 

The first unusual thing that we noticed is how excel changes data of certain fields of a column according to what it thinks should be the format. 

One of the fields that was present in the source file had data like 6-26, 8-15 and so on. These signified ranges like 6 to 26. We can’t change the data. So, we can’t change 6-26 to 6 to 26. Now, we export this xlsx sheet to a csv file, because legend has it that csv files are best used for data import. The csv file is created and opened and shows the data the way we want it to. So far, so good. 

Now, here is where things change. If you open that csv file again, there is a good chance the field that contains 6-12 might get converted 6 December. How did that happen, you would ask? Why did that happen? If you look closely, you would realize that 6-12 can also be interpreted as 6 December. 6 for day, and 12 for the month of December. So, 6-12 becomes 6 December. So, this is what happened. As to why? Well, we found that Excel is over-smart. When it sees 6-12, it decides this is a date and converts it to date. Even if the column is set to text. Even if other cells in this column might have something like A2-A6, or B5-F10. These values will not change. Just the ones like 6-12.  

Very annoying. Very, very annoying. And so, the solution?

Solution: Don’t open the csv file in Excel. And if you do, make sure you don’t save it. If you want to check, just open the csv file in good, old, ever dependable the Notepad.

Anomalous material #2, unforeseen consequence #2

Now, we come to the next unusual thing. You remember CRM has option sets? You remember these very options sets can behave as multi select option sets? You do? Great! I didn’t.

Anyway, the googly (or the curve ball, whatever rocks your boat) comes when you try to import values for these. According to the legend, values for multi-select option set should be separated with a semi colon (”;”). For example, “You; Me” in the image below.

According to another legend (referenced in the earlier point) csv files are ideal for data import. Great! So, you put the appropriate semi colon separated data in the field, and save the file as a csv file. Awesome!

And, now you import the data. Or you try. Aaannnd…..

The system will ask you to map the field, understandable. But then, it will ask you to map the value from the guilty cell of the csv file to the accusing value of the option set.

Okay, lets map the value. The value coming in is a multi-select option set value, meaning it will have more than one values in a single cell. It will have no corresponding values in CRM. Like in image below.

Curiouser and curiouser! So, what to do? How to map? How to import? Why map? Why import? What is going on? Who am I? What am I? Why am I?

Well, the answer to the first three question is annoyingly simple. For the rest of the questions, you will have to figure out the answers by yourself.

Solution: What you need to do is not convert the xlsx file to csv file. Just import the xlsx file. It works. And you do remember that we can use xlsx file to import data, right? Great! So, that one is also done.

Hope this helps!

ATM Inspection PowerApp to ease ATM inspection and report generation process.
https://www.inkeysolutions.com/microsoft-power-platform/power-app/atm-inspection

Insert data into Many-to-Many relationship in Dynamics CRM very easily & quickly, using the Drag and drop listbox.
http://www.inkeysolutions.com/what-we-do/dynamicscrmaddons/drag-and-drop-listbox

Comply your Lead, Contact, and User entities of D365 CRM with GDPR compliance using the GDPR add-on.
https://www.inkeysolutions.com/microsoft-dynamics-365/dynamicscrmaddons/gdpr

Create a personal / system view in Dynamics CRM with all the fields on the form/s which you select for a particular entity using the View Creator.
http://www.inkeysolutions.com/what-we-do/dynamicscrmaddons/view-creator