Import Examples

When looking at the four volunteers that have been imported into our CRM system we notice that none of them have had their gender set, because the two fields aren’t compatible and no transformation has been made.  In Microsoft CRM Online, the gender can be set to either “Male” or “Female”, but in our data source the gender is set to either M or F and therefore this setting has not been updated.  By doing the correct data transforming, when importing, we can rectify this issue with an easy little transformation named conditional replace.  First we look at the data field mappings from our previous example and select the gender mapping and once it’s selected, we click the “Transformations” button. In the Transformations window that opens up, we choose the “Conditional Replace” from the drop down menu and click “Add transformation”.  Use the screen shots below to follow our actions.

The next thing to do is to choose what values should be replaced and with what.  In our case we know that the only two values that will show up in the data source are M and F, so we start by setting the “Original value” field to M and the “New value” field to Male and then we click “Add”.  As Import Manager knows what values fit in a CRM field, it knows that the gender can only be either Male OR Female and therefore these are the only choices. We do the same for F and Female and the Replace values transformation window should look like the screen shot below on the left. Transforming is easier than one would think.
Now imagine a different kind of field, where no limitations have been set on its content in CRM, where we would like to change the data source values from Green, Yellow and Red to Harmless, Neutral and Danger.  In such a case Import Manager would let you put in the new value manually and it should look something like the screen shot below to the right.
Conditional Replace can be a truly powerful tool in the right hands when transforming data.

You can download Import Manager free of charge and try it out before you buy.

As with any Import Manager Light project, you start by opening the application and then you choose “add new rule”, e.g. By right clicking the “RuleSet” in the left side under Settings.
This will lead you to the choice of which entity to import into, as well as what data source you wish to import from. You should choose “ODBC datasource” from the drop down menu under Datasource and the “Contact” entity from the long list of entity types displayed above. See the screen below on the left as an example.
Once you click next, you’re led to the Configure datasource window, where you have to choose a connection string under DSN, a connection timeout in seconds and an SQL query in the SQL field.  Here you must be more careful, though the process is fairly straightforward, as long as you know what connection string to use and as long as you have the necessary credentials to log in, if needed be.
The standard (standard security) ODBC connection string for an Access 2007 database is as follows:
Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\mydatabase.accdb;Uid=Admin;Pwd=;
You will find it in the DSN drop down menu.  It is pretty straight forward to see what must be done with the connection string above. You simply exhange your database file location and name with the location and name of your database file and then you provide a user ID (Uid) and a password (Pwd).
In our local example, that ends up as the following connection string:
Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\database files\OlssonPlumbing.accdb;Uid=Admin;Pwd=plumbing55;
Use the “Test DSN” and the “Test SQL” buttons to check if your connection is OK and if it is, if your SQL query is OK as well.  In most cases you will want to simply leave the SQL window as is, but in others you may want to specify more precisely which information is returned.  However, that does require some basic SQL knowledge!

As you can see, it’s all fairly straightforward and easy to import from an Access database and into CRM online.
If you wish to check other examples of connection strings you can use to connect to an ODBC, see www.connectionstrings.com.

We will go through this migration, step by step, explaining in details as we go along.
The installation and setup of Import Manager will not be covered here, however, nor will we go into details with regards to setting up an account with Microsoft CRM Online 2011.

To start our data migration, after logging into Import Manager v5, we start by setting up a new server with the correct credentials to log into CRM, when asked what type of server, CRM 2011 Online is chosen and an appropriate title with a small description is added. We choose the Khmer Kids’ NGO organization from the drop down menu and provide the license file.
Once the server has been registered, Import Manager should look like the picture below to the right.

To import from an Excel file, the file must first be uploaded to the server, so we start by right clicking “Data Files” and selecting “Upload New” and in the browser we locate and select the Excel file in question.
We then need to create a new package, by simply right clicking “Packages” and selecting the option “New” – we choose to call this package Volunteer Migration.
The main Import Manager window should now look like the picture below, with a second window open that contains the details of our Volunteer Migration package.  This window automatically opens on the Overview page. Here it is possible to select what kind of logging you want, as well as whether or not the import should be run with multiple threads. We start by clicking the “Data Sources” view in the top middle, to set up a new data source. We name this data source “Volunteer Import” and under plugin, we chose “Excel Datasource” and click create (see the right picture below).

This brings us to the next window where we have to provide data source details, such as the file name and whether or not the file has a header row. Once you’ve made your selections, you can click the “Validate data source” button, to check if everything has been set up as it should.  Once you click “OK”, you’ll be returned to the “Data Sources” view in the “Volunteer Migration” package.
The next step is to set up the import rules, which in our case means giving the rule a name, choosing to import into contacts and selecting the “Volunteer Import” as our data source.  In this example we stick with the Entity Import Rule.
We now dig into the details and choose what actions must be taken and which cultures should be used.  For the purposes of this example, we stick with the default date and numbers culture. On no match, we choose create, on unique match we choose update and on multiple matches we choose ignore.

We need to map the various fields under “Mapping”, a simple drag and drop exercise where we match our fields in the excel source with fields in the CRM contact entity.
Since we’re importing the volunteers list into the contacts of CRM and since it is initially empty, one could argue that no match criteria is needed, but we will still set a match criteria up, using emails, since they’re unique and we might want to do the import more than once, in case of errors or the like.

So, we’ve set up the migration from Excel to CRM and after double checking everything, we run it – plain and simple.
Import Manager really is easy to use, intuitive and fast.