The Microsoft Dynamics CRM Blog
News and views from the Microsoft Dynamics CRM Team

R4 Import Data Wizard - Tips and Tricks

R4 Import Data Wizard - Tips and Tricks

  • Comments 4

The new Microsoft Dynamics CRM Online “Import Data Wizard” supports .csv, .txt and .xml file format for source data input. It also supports upload of multiple files in one Import session in compressed file format (.Zip). A .zip file can include files of .csv, .xml, or .txt file types. All files in a single compressed file must be of the same file type. For more information on supported file formats please see the following link: http://blogs.msdn.com/crm/archive/2009/11/10/supported-file-types-in-data-import-wizard-for-microsoft-dynamics-crm-online.aspx

By default, the maximum overall size of the files that can be imported is 8 megabytes (MB). This means:

  • Any .csv, .txt, or .xml file must not exceed the limit of 8 MB.
  • Any individual file in the .zip file, including files in attachment folder, must not exceed the limit of 8 MB.

If you have a source data file that is over 8 MB, split the data into several files (making sure to include the header row in each file) and import them separately.

Because multiple users in an organizations can submit import jobs at a single point, if two or more imports are happening simultaneously, the .zip files containing files with file size smaller than 1 Megabytes (MB) are imported immediately and the .zip files that contain files with file size larger than 1MB are queued and imported after the smaller .zip files are imported.

For example, two .zip files, A.zip and B.zip, are being imported simultaneously. If A.zip contains files larger than 1 MB and B.zip contains smaller files, then B.zip will imported immediately but A.zip will be queued and imported after B.zip is imported.

Importing Records with Notes

No column in your source file must be mapped to the file Name field of the Note record type in MSCRM when notes are being imported. Doing so can cause the import to fail.

If any column of the source file is mapped to the Document Body field of the Note record type, then the source file is considered to include information about Attachments. The value in source column that is mapped to the Document Body is the file name that is used to search a file in the Attachments folder in the .zip file.

Note: We recommend a Test run with lesser number of records before proceeding for actual data import, so that you can check if there is any issue with the map or data or environment.

Importing Lookup fields

If the record types being imported have cyclical dependencies and the lookup fields for these record types are not valid for update, the import may fail.

If you are importing data for two different record types and if these have cyclical dependencies, that is, record A has a lookup to B and record type B has a lookup to A, then usually Import Wizard does the following:

  • Creates records for record type A without lookups
  • Creates records for record type B without lookups
  • Updates the records created for both record types with the lookup values pointing to the records created

However, if any of these record types have lookup fields that are not valid for update, that is, the values for these fields must be provided during record creation and it cannot be updated later, then the import of such records may fail.

If CRM cannot create records for record type B for some reason, the records created for record type A remain in CRM without lookups to record type B). Similarly, if CRM cannot create records for record type A, the records created for record type B remain in CRM without lookups to record type A

Importing Date/Time fields

If the date/time format of your source data differs from the one set by you in CRM settings, then consider changing the CRM format settings accordingly before starting Import. It is advisable to take a small number of test records and verify your expectations before starting import.

Microsoft Dynamics CRM 4.0 Data Import Manager validates the date data in your source data during import. In cases where the date data is not in the format specified by your date format setting, DMM displays an error.

The new Import data Wizard understands the date time settings of the user in whose context it is running. To change the user timezone in CRM go to Tools à Options and change your time zone. To change the date format settings go to Tools à Options à Format àCustomize à Date, change and save the settings. These settings will be used by Import Wizard to import the dates and times.

Roles and Privilege

If you share or assign a single import file, all files imported in the same import session will also be shared/assigned to the selected user or team.

Privileges required in order to submit report:

  1. Data Import: Create, Read, Write, Delete, AppendTo
  2. Data Map: Create, Read, Write, AppendTo

Users created during the import will be assigned a salesperson role and these users may not have the privileges on custom entities which may result in records of these custom entities to be owned by the user selected in the Select Owner for the Imported Records.

The Import user must have a Create and a Read privilege on the record type s/he is importing. Because user record type is used for resolving owners and creating users in CRM, the Import user must have the following privileges:

  • A Read privilege for the record type to be displayed in the record type drop down.
  • A Create privilege for creating users.

Create field option will appear only if the target record type is customizable and the user has the following privileges :

Privileges.CreateAttribute, Privileges.PublishCustomization, Privileges.ReadRecord type,

Privileges.WriteRecord type, Privileges.ReadAttribute, Privileges.WriteAttribute, Privileges.ReadRelationship,

Privileges.WriteRelationship, Privileges.ReadCustomization, Privileges.WriteCustomization, Privileges.ReadQuery, Privileges.WriteQuery

These privileges are required to finally publish the customizations. The same privileges are checked if you try to add fields through System Customization flow.

Importing Users

CRM uses the records in the users.csv to:

  • Resolve users
  • Create users

CRM performs the following validations for resolving the ownership of imported records. If the source data contains a source file for users and if the data map has the metadata for owner resolution:

  1. The ownership is resolved based on the metadata information specified in the data map.
  2. If the required information for a user is present in the source file containing the user data, but the owner cannot be resolved, then the user is created in deactivated state and is used to resolve owners.
  3. For the records whose ownership could not be resolved using Steps 1 and 2, the user selected in the wizard becomes the owner for the imported records.

When creating users, the following fields in CRM must be mapped to some columns in your source file:

  • First Name
  • Last Name
  • Primary Email

The newly created users are imported with data in only these fields.

If the source data contains a source file for users but metadata information is not available in the data map:

  1. CRM first tries to resolve the ownership based on the following fields:
  • Primary Email
  • E-mail2
  • Mobile Alert E-mail
  • First Name
  • Last Name

    2. If the required information for a user is present in the source file containing the user data but the owner cannot be resolved using step 1, then the user is created in deactivated state and is used to resolve owners.

    3. For the records whose ownership could not be resolved, the user selected in the Import Data wizard becomes the owner for the imported records.

If the source data does not contain a source file for users:

  1. CRM tries to resolve records with the existing CRM users, using lookup transformation for the primary field (Full Name) of the user record type.
  2. For the records whose ownership could not be resolved, the user selected in the wizard becomes the owner for the imported records.

Note: Whenever duplicate CRM user found on owner resolution, set ownership (ownership is set) to the CRM user selected in the wizard.

Users created during the import will be assigned a salesperson role and these users may not have the privileges on custom entities which may result in records of these custom record types to be owned by the user selected in the Select Owner for the Imported Records.

Stand-alone import of user data is not supported. User data is necessary to determine ownership of the records that you are importing. Therefore, importing only user data without importing associated records, is not supported. Always import user data with the other record types that the users own.

CRM always displays the success and error counts for the import file associated with user records as "0" in the Imports grid.

To make a user an owner of the imported records, you must have a privilege to create records on behalf of that user. If you do not have the sufficient privileges, regardless of whether you specify a user in Select Owner for the Imported Records, the imported records for which you do not have privileges, will be owned by you.

More details on User/owner mapping can be found in the following link:

http://blogs.msdn.com/crm/archive/2009/11/20/owner-mapping-user-creation-in-import-data-wizard-for-microsoft-dynamics-crm-online.aspx

Limitations

Creating Custom Record/field Type

• Custom record type created during import is always user-owned.

• Custom record types created during import are created with certain default properties that cannot be changed after the record types are created. The custom record types created during import are created with the following properties

    • Ownership set to User-owned
    • Activities turned on
    • Notes turned on

These fields cannot be changed once you create a custom record type. So, if you need certain record types to be created with properties different than this, we suggest that you create the record type manually in CRM.

  • Custom Fields with only the following data type can be created: Text, Picklist, Bit, Integer, Decimal, Datetime and Lookup
  • If a field used in a data map for mapping data is no longer available in CRM, an error is displayed. You will not be allowed to continue until you either map the source column to a different field or ignore the column.

For more information on creation of new record types and fields using Import Data Wizard please visit:

http://blogs.msdn.com/crm/archive/2009/11/23/creation-of-new-record-type-and-fields-with-import-data-wizard-for-microsoft-dynamics-crm-online.aspx

Mandatory columns set changes depending upon some field value:

For some record types, the mandatory fields are different for different types of record types. If you fail to provide the information for all the mandatory fields, the records may not import.

For example, here are the first few rows of the Quote Product record type:
 
image 

In the data import template for the Quote Product record type, only Quote and Quantity columns are marked as mandatory. However, due to some business logic, based on what you select in the Select Product column, the mandatory fields change for the Quote Product record type. For example, if you select Existing in the Select Product column, the Product and Unit columns become mandatory.

This may be true in case of other record type also. If you do not provide the mandatory information in the source files, the records will not be imported. However, in such cases, you may not get a specific error. Thus, to understand the exact error try creating similar records through Microsoft Dynamics CRM Online and identify the mandatory fields for that record type that you need to fill in the CSV file for importing records successfully in Microsoft Dynamics CRM.

Data Truncation during Import:

Numeric data with very large values, or many digits after the decimal point, may be partially truncated during import.

Microsoft Dynamics CRM 4.0 includes default limitations on the precision of very large numbers, and numbers with many digits after the decimal point. Although these limitations can be modified through customization in the Web application, importing data by using Import Data wizard with default precision settings in the Web application can sometimes result in truncation of numeric data that falls outside the range of these settings. Even if truncation occurs, the Import Data wizard will not display an error message and will import the data.

If you are importing numeric data to Decimal, Float, or Money fields, used by entities in Microsoft Dynamics CRM 4.0, verify that your source data does not contain data that falls outside of the expected range. If your source data contains very large numeric values, or values with many decimal places, and truncation is not acceptable during import, you can customize the target fields through the Settings area of the Web application before starting the import.

Similarly, if you choose to create custom fields during the import process, there are limitations on the precision supported by Import Data wizard, as shown in the following table.

     image

Important

Any data that exceeds these precision values will be truncated during import, and the Import Data wizard will not display an error message.

Even if the data falls within the maximum precision values for these three data types, for very large numbers, there may still be cases in which truncation occurs. The following table includes examples of truncation during import of very large numbers and numbers with many digits after the decimal place:

image

I hope this will be very helpful when you use New Import Data Wizard for importing records into the Microsoft Dynamics CRM platform.

Thanks,

Priyesh



  • Hi,

    The record type picklist displays all the entities(both system as well as custom). But I want to display only a select few. In my case I want to display only 'contacts' as an option in record type. How can I do that?

  • I need my imports to update existing records instead of erroring out as duplicates - how do I achieve this ? any tools available ?

  • MM - there is no way to update a record during data import that i have found in CRM online 4.

  • You need to subscribe to crm metadata webservices to update records in CRM

Page 1 of 1 (4 items)
Leave a Comment
  • Please add 4 and 1 and type the answer here:
  • Post