Prepare a file for import

Can I do this task?

Follow the guidelines in this topic to ensure that your file imports successfully.

1. Put records for each record type A group of records. For example, all account records are grouped as the Account record type. Also called an entity. in a separate file.

   You can import only one record type at a time. For ease of use, name the file the same name as the record type in Microsoft Dynamics CRM Online. For example, put all your contact records in a file named contacts.txt or contacts.csv.

2. Only put records that need to be assigned to one user in each file.
3. The file must be in delimited text A file that contains data in which individual field values are separated by commas, tabs, semicolons, or other characters. Delimited text file names typically end with a .txt file extension (for example, "my_file.txt"). format (.txt) with columns separated by commas, or in a comma-separated value (CSV) file A data file with a .csv file extension. Typically a CSV file consists of fields and records, stored as text, in which the fields are separated from one other by commas. (.csv) format.

   You can easily create delimited .txt or .csv format files by using Microsoft Office Excel.

   If your file uses only ASCII characters, save the file as a comma-delimited values (.csv) file.

   In Microsoft Office Excel, click Save As, and select CSV (Comma delimited (*.csv).

   If your file uses non-ASCII characters, save the file as a Unicode text (.txt) file and change the field separator to commas.

   1. If your import file contains non-ASCII A character set that defines all characters and symbols needed for English. characters, the import file must be in a Unicode A character set that defines all characters needed for writing the majority of living languages on computers. or UTF-8 A unicode character set that is backward-compatible with the ASCII character set. format.
      1. In Excel, click Save As, and select Unicode text (*.txt).
      2. Use Notepad to open the .txt file.
      3. Search for commas in the file. Enclose any data that includes commas with double-quotes. For example:

         "23 State Street, Apt 2".

      4. Replace all tab characters with commas.
         1. In Notepad, select the tab character with the keyboard or mouse and press CTRL+C to copy it to the Clipboard.
         2. On the Edit menu, click Replace.
         3. In the Find what box, press CTRL+V to paste the Clipboard contents. This pastes the tab character.
         4. In the Replace with box, type a comma (,).
         5. Click Replace All.
         6. Click Save As.

         In the Save as type box, select Text Documents (*.txt), and in the Encoding box, select Unicode.

4. Make sure the first line of the file contains column headings.

   Edit your file and add column headings Text used in the first row of a spreadsheet or file that labels the data in each column. if they are not already present.

   Important

   To use automatic mapping Importing a data file where the column headings exactly match the Microsoft Dynamics CRM Online display names. No data map is necessary. With automatic mapping, you can map columns that establish a relationship with existing Microsoft Dynamics CRM Online records., which is required for importing data that contains references to existing Microsoft Dynamics CRM Online records, column headings must match attribute display name The label that is used by default when an attribute is displayed on a form, in a list, or in a report. The Display Name for each attribute can be changed in the Customization area by a user with an appropriate security role. values in Microsoft Dynamics CRM Online.

   • To identify display name values, open the form for the record type as if you were creating a new record. The labels on the form typically match the display names, although it is possible for your system customizer to have changed the form label values. If you have the appropriate security role, you identify display names in the Customization area:
     1. In the Navigation Pane, click Settings, click Customization, and then click Customize Entities.
     2. Open the entity, and then click Attributes.
   • If you are working in a multi-language environment, automatic mapping of columns uses the display names from the user interface language The language in which Microsoft Dynamics CRM Online displays text for textual elements, such as labels and list values. If your organization has enabled additional languages, you can select the language in which you want Microsoft Dynamics CRM Online to display the user interface., set on the Regional Options tab of the Set Personal Options dialog box. More information: Set Personal Options
5. Make sure the first column heading is not the name of a record type.

   The first column heading in your file cannot be the name of another record type. For example, phone call, task, letter, fax, and appointment records all require a Subject column. As Subject is also the name of a record type, the Subject column must not be the first column in your file.

6. Make sure data exists for all business-required attributes.

   A record will only import if all Microsoft Dynamics CRM Online business-required Attributes that are required before a record can be saved. Required fields are marked with an asterisk. attributes are mapped, and if the data exists in each of the source columns that are mapped to the required attributes. You can either determine the required attributes in advance, or use the Import Data Wizard to identify missing required mappings. In either case, make sure you have required data in each record. To determine which fields are business-required, open the form for the record type as if you were creating a new record, and identify fields that are marked with a red asterisk (*).

   The following list shows default business-required fields for commonly imported record types.

   • Account: Account Name
   • Contact: Last Name
   • Lead: Topic, Last Name, Company Name
   • Product: Default Unit, Unit Group, Decimals Supported

   Campaign Response: Parent Campaign, Subject, Owner

7. If records in your import file are related to an existing Microsoft Dynamics CRM Online record, make sure the column heading and data in the column that connects the records is valid, and prepare your file for automatic mapping.

   Important

   You cannot use a data map A file that contains information about how data from a source system corresponds to data in Microsoft Dynamics CRM Online. to import data if your data is connected to existing Microsoft Dynamics CRM Online records. You must use automatic mapping Importing a data file where the column headings exactly match the Microsoft Dynamics CRM Online display names. No data map is necessary. With automatic mapping, you can map columns that establish a relationship with existing Microsoft Dynamics CRM Online records., which means your column headings must match Microsoft Dynamics CRM Online display names.

   • The value in the column that refers to another record must be unique in the primary attribute The attribute that uniquely identifies a record, typically the name of the record. The following list shows the primary attribute for common record types: (Account: Account Name, Contact: Full Name, User: Full Name, Case: Title, Lead: Name. for the related record. The lookup is not case-sensitive.

     For example, if you are importing a contact that you want associated with an existing account, you must have a column in your source file containing the account name for an existing Microsoft Dynamics CRM Online account record. Because automatic mapping is required, the column heading for this column must be Parent Customer.

     The Parent Customer attribute can be resolved either to an account or contact. The Import Data Wizard looks for a unique account or contact record. If there is a contact record and an account record that have identical values in the Account Name field in the account record and the Full Name field in the contact record, the record will not be imported.

   • The value in the column can also be a Microsoft Dynamics CRM Online globally unique identifier (GUID) A unique 128-bit value that is used to identify a record..
   • If the column in your source file that is used to create the relationship contains multiple values, edit the data to contain just one value.

     For example, if you are importing e-mail activities, and your source data has a record with multiple values in the To column, edit the data to contain just one value.

8. Decide how to import drop-down list values in the file.

   If you have data in columns that will be imported to drop-down lists in Microsoft Dynamics CRM Online, review the data in these columns. If the values do not match valid values in Microsoft Dynamics CRM Online, you will need to perform one of the following procedures:

   • Use a data map A file that contains information about how data from a source system corresponds to data in Microsoft Dynamics CRM Online. to map each value in your source file to a valid value in Microsoft Dynamics CRM Online.
   • Do a find-and-replace in your source file and then replace each value that isn't valid in Microsoft Dynamics CRM Online, with one that is. This avoids the need to do list-value mapping in the data map.
   • Customize the drop-down list attribute in Microsoft Dynamics CRM Online by adding each unique value in your source file. This task requires privileges usually reserved for the System Administrator or System Customizer security role.

   Make sure that values have consistent capitalization.

   Example showing sample leads to import

   The following lines show what the data might look like for a .csv format file containing leads to import. The first line contains the field names, and the remaining lines are imported as data.

   Company,Last Name,First Name,Topic,Email,Mobile Phone

   Designer Bikes,Groth,Brian,Mountain bikes,someone@example.com,555-555-0112

   Major Sporting Goods,Bedecs,Anna,Components,,555-555-0171

   Example showing sample products to import

   The following lines show the minimum amount of data required for a .csv format file containing products to import. The first line contains the field names, and the remaining lines are imported as data. Because Unit Group and Default Unit are record types in Microsoft Dynamics CRM Online, the values for these columns must map to valid values in Microsoft Dynamics CRM Online.

   Product,Unit Group,Default Unit,Decimals Supported

   Front Brakes,Single pair,Unit pair,0

   Vest,Clothing,Single article,2

Note

By default, the maximum size of the file that you can upload is 4 megabytes (MB). This can be changed by your administrator. More information: Microsoft Knowledge Base Article 295626.

© 2009 Microsoft Corporation. All rights reserved.