Microsoft Dynamics CRM 2011 provides the Data Import Template for entities. The template is in XML Spreadsheet 2003 format, with an .xml extension. You can download it by going to Settings | Data Management | Templates for Data Import. Alternatively, to download the Data Import Template for Crew Members, go to ACM homepage | Crew Members entity. On the ribbon bar you will see a button called Import Data. Click on the text Import Data below the button, and then click Download Template for Import:

Open the XML file using Microsoft Excel; you will see that each column corresponds to a field on the Crew Member main form. It also indicates (via bolded column text) all mandatory fields for the record.

You may notice that all mandatory fields are bold text. So make sure that these fields have valid data for importing.

You also see a unique identifier data type (lookup) on the Spreadsheet call: Owner.

Owner is a system mandatory field that appears in all user-owned entities. The owner data can be populated by the data import process—we don't need to fill in this field—so remove the Owner column.

The next screenshot shows the basic information for Crew Member. Save it to XML Spreadsheet 2003 format, and name it Crew Member.xml.

There is something we should know about the template:

As we mentioned, we are going to import a photo as an attachment for each Crew Member, in one data import process.

Attachment, in Microsoft Dynamics CRM 2011 is mapped to the Note entity which has a relationship to the related parent entity (Crew Member). In order to import both entities in parallel, we need to create a separate XML Spreadsheet 2003 workbook for Note.

The next screenshot shows the basic information for an Attachment (Note); save it to XML Spreadsheet 2003 format, and name it Note.xml.

In this demo, all crew members have different names, so we can import Crew Member and Notes together using the Automatic Mapping option in CRM 2011.

The next step is to create a folder called Attachments, put all of the pictures into that folder, zip it along with Crew Member.xml and Note.xml, and call it Crew Members.zip. Note that the zip file can contain either CSV files or XML Spreadsheet 2003 files, but not both. See the next screenshot as an example:

Microsoft Dynamics CRM 2011 has an out-of-the-box duplicate data detection function, which provides the capability to detect and handle duplicate data. To avoid importing duplicate data into the ACM system, we need to take a look at the duplicate detection settings: Go to the ACM homepage| Data Management | Duplicate Detection Settings.

Make sure that the Enable duplicate detection option is enabled and that the During data import option is selected. Then click on OK to save your changes and return to the previous screen.

Now we need to create a rule that tells the system how to identify a duplicated record. Double-click to open the Duplicate Detection Rules; we can see that there are four rules out-of-the-box. Because none of these will be used in our system we can select all and then delete them.

Click the New button to set up the rule as shown below, save it, and then click the Publish button to activate the rule.

The above rule tells the system if an importing Crew Member has the same E-mail as any existing Crew Member in the system. This is a duplicated record and therefore should not be imported. CRM prompts users before saving or importing potential duplicate records:

By default, duplicate detection is available when a record is created or updated. This includes when a record created with the Microsoft Dynamics CRM for Outlook is tracked.

Duplicate Detection has some limitations that we should be aware of:

To work around the above limitations, we may schedule Duplicate Detection Jobs. (A job that runs in the background; you can request e-mail notification when the job completes, with a link to the page where it can be resolved.) To schedule a Duplicate Detection Job, go to ACM Settings | Duplicate Detection. It's very straight forward, and you can try it yourself.

Go to ACM Workplace | Crew Member and click the Import Data button to start the Import Data Wizard. Browse to select the Crew Members.zip file, and then click Next. The Wizard will find two files in the ZIP file; click Next.

Select the Default (Automatic Mapping) option, and then click Next.

Because the columns are exactly mapped to the field Display Name, this saves our time to map it manually. You will see that both Crew Member and Note are successfully mapped to the target record types:

Click Next again. This will bring us to the Review Settings and Import Data screen. Select No on Allow Duplicates, select an owner for the imported records, and then click the Submit button:

Now the data has been successfully submitted for Import, and we will see a "Congratulations!" message. However this just indicates that the data has been submitted for import; that doesn't mean the import has succeeded. It's just half way finished. The system will parse the import file, then transform the parsed data, and finally it will upload the transformed data to the target Microsoft Dynamics CRM server. Fortunately, these processes (parse-transform-upload) are done by the asynchronous jobs that run in the background.

To know whether everything has been successfully imported, click the Imports link:

If you open an Import Source File, you will see what has been imported, what has been partially imported, and a list of the failures:

To remove the imported data, select the import source file, and click the drop-down icon for the Delete button on the ribbon. Here we can see three options, which are all intuitive and meaningful.

The Import Data Tool doesn't know that we want to create two types of records by using a single CSV file unless we provide a Data Map (a file that contains information about how data from a source system corresponds to data in Microsoft Dynamics CRM) for it.

You can create a Data Map by using any XML editing tool. As a starting point, we want to see what a Data Map looks like, and will work from there. Go to ACM Settings | Data Management | Data Maps. Select Active Data Maps for the view, then select For Generic Map for Contact and Account, and click the Export button on the toolbar.

Use Microsoft XML Notepad 2007 to open the Data Map XML file. It looks like this:

As we can see here, this Data Map is being used for Account/Contact data import. Now we need to update and simplify it so that the Data Map can be used for Airport/Flight Route data import:

The following screenshot shows the final result:

Now that we have a minimal Data Map for Airport and Flight Route, the XML looks like this:

<Map Name="Data Map for Airport and Flight Route" Source="Generic Source">
<EntitiesPerFile>Multiple</EntitiesPerFile>
<EntityMaps>
  <EntityMap TargetEntityName="acm_airport" SourceEntityName="Airport" Dedupe="Eliminate" ProcessCode="Process"/>
  <EntityMap TargetEntityName="acm_flightroute" SourceEntityName="Flight Route" Dedupe="Eliminate" ProcessCode="Process"/>
</EntityMaps>
</Map>

Save the data map file. Click the Import button on the Data Maps toolbar, upload the Data Map to CRM, and activate it. See the following screenshot for Data Maps:

Air-X provides a single CSV file that contains both Airport and Flight Route information. Our task is to import it into CRM and create two types of entity records: Airport and Flight Route. We may split them and then import them separately. However, in Microsoft Dynamics CRM 2011 you can import multiple entities using a single data source, with Data Map.

Export the Excel Spreadsheet to a CSV (comma delimited) (*.csv) file and then open it via Notepad, just to make sure you know what the delimiter is.

Click the Import Data button on the ribbon, and then navigate to the CSV file that we created with both Airport and Flight Route information.

Make sure that you choose the correct Field delimiter and Data delimiter for the import file.

Select the customized map that we just created: "Data Map for Airport and Flight Route", and then click Next.

Because we didn't set the attribute maps in our XML file, we can do it here:

At this stage, we can save this data map for future imports, so that we don't have to map the attributes again.

Submit this, and after a while both the Airport records and the Flight Route records are created in CRM:

General speaking, the Import Data Wizard doesn't support data update, so each time the Import Data Wizard will create new records in CRM.

A simple way to update CRM data is: export them using the XML Spreadsheet 2003 format, and then update using Excel, then import the XML into CRM.

You can use the Advanced Find function to select records you want to edit, then click on the Export Data to Excel button on the toolbar, the following dialog box will pop up. Select the Static worksheet with records from this page option and check Make this data available for re-importing by including required column headings. Next time when you import using this XML file, the corresponding data in CRM will be updated:

You might have noticed that there is a limitation of the ..records from this page, so if you have lots of data that you want to export and import, this method isn't an ideal solution.

Following example of how to programmatically insert/udpate (if data exist) Flight Routes with a CSV file.

/*
 * Update Flight Route if it exists in CRM; Otherwise Insert. * 
 * 
 * FlightRoutes.csv
 * Name;From;To;Miles
 * FR001;JFK;LHR;20000
 * FR002;JFK;SEA;30000
 * 
 * */

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.ServiceModel.Description;
using System.Net;
using System.IO;
using Microsoft.Xrm.Sdk.Client;
using Microsoft.Xrm.Sdk;

namespace InsertUpdateFlightRoute
{
  class Program
  {
    static void Main(string[] args)
    {
      Uri OrganizationUri = new 
                            Uri("http://bps:5555/ACM/XRMServices/
                            2011/Organization.svc");
      Uri HomeRealmUri = null;
      ClientCredentials Credentials = new ClientCredentials();
      Credentials.Windows.ClientCredential = 
                          CredentialCache.DefaultNetworkCredentials;

      try
      {
        //Initializes a new instance of the CRM 
          OrganizationServiceProxy class.
        using (OrganizationServiceProxy serviceProxy = new 
               OrganizationServiceProxy(OrganizationUri, 
               HomeRealmUri, Credentials, null))
        {
          serviceProxy.EnableProxyTypes();
          IOrganizationService service = 
                               (IOrganizationService)serviceProxy;

          //Read the csv file into the routes array.
          var routes = 
                      File.ReadAllLines("FlightRoutes.csv").
                      Skip(1).Select(line =>
          {
            //Split columns by ;
            string[] fields = line.Split(";".ToCharArray());
            return new
            {
              fRoute = fields[0],
              fFrom = fields[1],
              fTo = fields[2],
              fMiles = fields[3]
            };

          }).ToArray();

          foreach (var route in routes)
          {
            //Query record in CRM: update the record if exist; insert 
              the record if doesnot exist.
            acm_flightroute fr = getRoute(service, route.fRoute);

            if (fr != null)
            {
              //Record exist - update the Flight Route.
              fr.acm_name = route.fRoute;
              fr.acm_From = new 
                       EntityReference(acm_airport.EntityLogicalName, 
                       getAirport(service, route.fFrom));
              fr.acm_To = new 
                       EntityReference(acm_airport.EntityLogicalName, 
                       getAirport(service, route.fTo));
              fr.acm_Miles = int.Parse(route.fMiles);

              fr.EntityState = EntityState.Changed;

              try
              {
                serviceProxy.Update(fr);
              }
              catch (Exception ex)
              {
                errorHandler(ex);
                continue;
              }
            }
            else
            {
              //Record doesnot exist - insert the Flight Route.
              acm_flightroute newFlightRoute = new acm_flightroute()
              {
                acm_name = route.fRoute,
                acm_Miles = int.Parse(route.fMiles),
                acm_From = new 
                       EntityReference(acm_airport.EntityLogicalName, 
                       getAirport(service, route.fFrom)),
                acm_To = new 
                       EntityReference(acm_airport.EntityLogicalName, 
                       getAirport(service, route.fTo))
              };
              try
              {
                serviceProxy.Create(newFlightRoute);
              }
              catch (Exception ex)
              {
                errorHandler(ex);
                continue;
              }
            }
          }
        }
      }
      catch (Exception ex)
      {
        errorHandler(ex);
      }

      Console.ReadLine();
    }

    //Display Errors.
    private static void errorHandler(Exception ex)
    {
      Console.WriteLine(ex.Message + ex.InnerException);
      //Console.ReadLine();
    }

    //Return the Airport ID.
    private static Guid getAirport(IOrganizationService service, 
                                  string airportcode)
    {
      using (OrganizationServiceContext orgContext = new 
             OrganizationServiceContext(service))
      {
        var query = from a in orgContext.CreateQuery<acm_airport>()
              where a.acm_name.Equals(airportcode)
              select a;
        if (query.FirstOrDefault() != null)
          return query.FirstOrDefault().acm_airportId.Value;
        else
          return Guid.Empty;
      }
    }

    //Return the Flight Route.
    private static acm_flightroute getRoute(IOrganizationService 
                                   service, string routename)
    {
      using (OrganizationServiceContext orgContext = new 
             OrganizationServiceContext(service))
      {
        var query = from r in 
                    orgContext.CreateQuery<acm_flightroute>()
              where r.acm_name.Equals(routename)
              select r;

        if (query.FirstOrDefault() != null)
          return query.FirstOrDefault();
        else
          return null;
      }
    }
  }
}