Be careful while preparing excel import files

Hi,

my colleague Lars had some serious problems with the CRM offline sync and solved it.

Here is the solution:

Excel imports in CRM 2011 are a great way importing or changing mass data in your system. Especially when your client starts working with Microsoft CRM and wants to migrate all his data.

Please consider the following comments while preparing data for excel import.

Example:

We want to import Account data using an import template created within CRM 2011. After opening the template in Excel you can see all available columns and when selecting a table cell the information appears which data type the field has and how many characters are allowed to be entered.


SC1

While entering the data manually in the table cell Excel is validating your data. You are not allowed to enter more than 40 characters in the example below.

sc2

While preparing import data you usually do not enter all data manually. Often the data are provided in excel sheets and must be copied into a template. When using Copy & Paste please  be careful. The pasted data will not be validated by excel. In the example below I pasted more than 40 characters in the field where the exception was thrown before.

sc3

We could assume while importing the data an exception would be thrown by CRM as the imported data are validated.

No! Far from it!

The data will be imported without an exception and even worse the field length of the attribute will be extended in the database. As the customizing was not changed connected Outlook Clients won’t get the information about changed field length.

As result the CRM 2011 client for Microsoft Outlook is not able to import the data in the local database while going offline. Instead you will face an exception like

“An error occurred during Offline Synchronization.  Try going offline again, or restart Microsoft Outlook. Insert failed for entity ‘Account’, batchRows=0, downloadOnly=False with exception System.Data.SqlClient.SqlException (0x80131904): The bulk load failed. The column is too long in the data file for row 24, column 198. Verify that the field terminator and row terminator are specified correctly.”

Therefore please doublecheck the provided excel file before importing as they can cause database changes in a way you do not want.

No comments yet.

Leave a Reply