RSMUS.comI recently had to import a series of email records into Microsoft Dynamics CRM which became a little more challenging then want I intended when taking on the task. I few of the items should have been things I had thought about, but did not. All in all, 7 hours later I was able to import the file of 7500 records.
The first challenge to overcome was the fact that the records were in an xlsx file. This cannot be imported as is, so I converted it to an xml file. Unfortunately, the size of the file increases dramatically when it becomes a xml file. The 2 meg file quickly became 25 megs as it was converted. This took me over the 8 meg maximum allowed for an import. I found that breaking the file into sets of 2500 records took me below the 8 meg limit. I kept the header and deleted rows 2501 and higher in the first file, kept rows 2501 – 5000 for the second file and put all rows 5001 and above for the third file. With the competition of this work, I thought I would be good to go.
This was not the case. I ended up having to break the files down by whether the record represented an incoming email, where the to record contained system user, or a from record where the to record contained a contact. Squaring this away got me one step closer to finishing. I needed to set the status to completed and had to set the status reason to either sent or received. By dividing up the files as inbound or outbound, I was easily able to create 2 additional columns in each file to represent the status and status reason.
The final hurdle for this import was the description field. I was receiving many records that were written in a foreign language. I was finding that the English records were importing successfully, but the records containing foreign text were just not completing. The failure was happening on the upload of the file, so I was not getting a good error message from the system. After working with a colleague and fellow blogger John Annunziato, we dug into the xml files and found a number of unprintable characters. I started to remove them manually and quickly found the number was in the thousands. It was not feasible to manually fix these records, so I was back to the drawing board. After doing a number of searches on the web, I found that there is a function in Excel called Clean. This function removes all traces of non-printable characters. I wrote a process that would take the description of the email, apply the clean function to it and replaces it with the scrubbed description.
This fixed my issues. I was happy to get it done, but did not expect the time it took to process.
If you are looking for Dynamics CRM support, RSM can help. We can answer basic support questions or help configure your Dynamics CRM system. Contact our professionals at crm@mcgladrey.com for more information or leave a comment in this post.
By: Bob Kanzler – Microsoft Dynamics CRM partner in New Jersey
Bob Kanzler
Principal of RSM's Dynamics 365 CE focused on our east coast clients and non profits.