locked
Importing related data from CSV into CRM RRS feed

  • Question

  • I found a thread on exactly this:

    http://social.microsoft.com/Forums/en/crm/thread/574c81b9-c0d4-4a88-91ee-7b22ff29a12f

    But sadly the link no longer works.  Does anyone have any info on this procedure?  Basically, we currently have a lot of data in an old Access db that we'd like to bring into CRM, but it's all related, so there's GUIDs to consider, etc...

    Thanks!

    Monday, October 17, 2011 4:50 PM

Answers

All replies

  • Oh, we're using CRM 4.0, and we want to import the data into custom entities.
    Monday, October 17, 2011 4:53 PM
  • To import records of multiple record types that are related to each other you will need to use the CRM 4.0 Data Migration Manager. A csv file is needed for each record type.  There is no issue or special procedure required for custom entities.

    If your records are unique then you won't have any problems and I don't see that you need to be concerned with GUIDs.

    Are you importing all new data or are some of the records to be linked to existing records?

     

    Monday, October 17, 2011 4:58 PM
    Moderator
  • I thought GUIDs were how relational links are managed?

    I'm looking at the DMM now, but from that previous thread (I managed to track the text down through the Wayback Machine), it claimed that there were extra steps involved to deal with the GUIDs, and they linked to software to do that (but that link also didn't work).  That's why I thought I'd need to do something special.

    To give some names to help this make sense - we want to import data for two main entities, project and time activity.  (A list of projects people are putting time towards, and what activities make up those projects).  Each project will have a number of associated time activities, but each time activity might also be a part of several different projects.  Also, the project entity includes fields for company name and department, which are lookups into existing entities/data.

    Does that make sense?  :-)  Thanks!

    Monday, October 17, 2011 5:12 PM
  • It does make senses. The complicating part is where you say that one time activity may be associated with several projects. If I understand you correctly that suggests that a many:many relationship is needed between Projects and Time Activities.

    If I have misunderstood and it is simply a 1:n relationship between projects and time activities then I think you can import the data quite simply (without using the DMM).

    Do two imports, first the projects and then the time activities.

    Create a csv file of Projects (with no duplicate project names). The fields (columns) would include Project Name, the lookup fields for company name etc and anything else (you need to ensure that there are no duplicates for the names of any of the lookup fields). Import this file to create the Projects.

    Then create a csv file of time activities, include a column for the parent project (again there must be no duplicates) and all other fields and then import the time activities.

     

    Monday, October 17, 2011 5:19 PM
    Moderator
  • It's a many:many, yes.  I'm not the one that implemented it, but I believe this was done by creating some sort of interim entity called projects_timeactivities that does the association.  This works when adding/assigning things through the CRM GUI, but that will be a painfully slow process for all our data.
    Monday, October 17, 2011 5:31 PM
  • So I've been playing with DMM.  I was able to import in timeactivities, as they don't reference anything else (they only get referred to).  But I'm not at all understanding I'd use the DMM to import the projects, for example, as they have relationships with other things (departments, etc...).  You mention " The fields (columns) would include Project Name, the lookup fields for company name etc" - how do I include the info of the lookup field?  For example, if different projects might be associated with different departments (which are already in CRM) how do I map those?  In our Access system they just had IDs of 1, 2, 3, etc...so that's what's in the project table.  But in CRM they've presumably had GUIDs automatically generated for them.  Would I have to go in manually through SQL or something, determine those GUIDs and which one maps to which ID we use, and then manually change my csv file to have those GUIDs instead, or is there some way I can do this through the DMM or something else?  In the link above (when I followed it using the wayback machine) he references using a program to extract/map the GUIDs, so I assume he's referring to this process...?

    Thanks!

    Monday, October 17, 2011 10:33 PM
  • I still think there is no need to bother with GUIDs, use the primary name of the entity in the import data. For example, let's say you have the following departments:

    • Marketing
    • Sales
    • Accounts

    The names of the departments are the primary fields and the entity is called department. An import file for projects might look this:

    Project Name, Duration, Department
    CRM Mgiration, 12, Marketing
    New CRM System, 20, Sales

    The first line contains the name of the fields on the project entity. The Department column is the name of the lookup field in Projects that connects a record to a department. In the data rows, I've just used the name of the department record. For this to work there must be no duplicate departments otherwise the import row will fail because CRM can't resolve which department to connect the project to.

    With regard to timeactivities, don't they have lookups field connecting them to a project or the interim entity, so I wonder whether you should import them last?

     

    Tuesday, October 18, 2011 7:09 AM
    Moderator
  • My worry with it that way is that it kind of hardcodes the names in, which I don't usually do.  So if the "Sales" department changes their name to "Sales Support" or something, suddenly things don't connect.  That's why I prefer to stick with IDs.

    There's not a lookup from timeactivities to projects; it goes the other direction.  So I think I should be okay.  Regardless, I'm just playing around - if I have to remove the timeactivities and re-import them, it's not a big deal.  :-)

     

    Tuesday, October 18, 2011 3:17 PM
  • Once a record has been imported and connected to another record , it doesn't matter if the record name changes. CRM maintains the connection.

    Tuesday, October 18, 2011 3:37 PM
    Moderator
  • Ah - so basically we'd have to change the relationship in the entity from being on the GUID to being on the name.  And once we did that, and links were established on those names, the links stay even if the names change.  Is that right?

    (Sorry - I've only ever worked with straight SQL dbs before, not CRM, so I'm trying to get my head around all the differences...Thanks for your patience!)

    Tuesday, October 18, 2011 3:40 PM
  • Yes. When a relationship is created between two entities, CRM manages everything. For example, I have two entities in CRM in a 1:n relationship Projects and Contacts. When the relationship is created CRM adds a field (of type lookup) to Contacts that references the Primary Name field of Projects. A user links a contact to a project by selecting a project using its Primary Name (but this is not a primary key). If the Primary Name changes then all the connected contacts remain connected.

    I can see that it may be tricky to avoid thinking at the db level.

    Tuesday, October 18, 2011 3:45 PM
    Moderator
  • Allison,

     

    I would have to agree with Feridun. I don't think you have to worry about the GUID if you are trying to INSERT new data in the CRM database. I would suggest that you do the following. As DMM seem to work perfectly well, you can also use the Import Wizard that appears either in Outlook or in the Web Browser, which ever you prefer. In any case, I would advise to look at the exact display name from CRM and match it on the column headers of your CSV file you are trying to build. Then, try to use either method and you should not have to worry about the relationships between database tables.


    Sincerely,

    Anooka
    Tuesday, October 18, 2011 5:18 PM
  • Also,

     

    Regarding DMM, check out this How To video from Youtube, that should help. I think the guy even talks about a problem similar to yours ...

     

    http://www.youtube.com/watch?v=ZUTF4ENUe-s


    Sincerely,

    Anooka
    Tuesday, October 18, 2011 5:21 PM
  • Okay, I think I've got my head around it now.  However, the linking entity we're using to handle the many:many between the projects and time activities doesn't appear in DMM under the "map this file to an existing Microsoft Dynamics CRM entity" list.  The entity exists - I can see it in the metadata browser - but the DMM won't let me map to it...:-(
    Tuesday, October 18, 2011 6:49 PM
  • That is strange. I don't know why it is not appearing in the list.

    Does it appear in the list of Record Types if you use Tools | Import in the CRM web client?  You'll need to have a csv file to use as the data file on the first page of the wizard; but that file can contain dummy data. I'm just interested in whether the link entity is in the list on the next page of the wizard.

    Wednesday, October 19, 2011 11:42 AM
    Moderator
  • No - that list is the same as in DMM, and the entity I need isn't in either place.  :-(
    • Edited by Teleute00 Wednesday, October 19, 2011 3:49 PM clarification
    Wednesday, October 19, 2011 3:39 PM
  • I guess that the linking table is a result of a native n:n relationship created in CRM. If so, then bad news. With a native many-to-many relationship the intersect entity is hidden. You can't use DMM or Tools | Import to bring in data to this hidden table.

    You might like to investigate this tool: http://crmmanytomany.codeplex.com/ (which requires GUIDs).

    Also, review this topic: http://msdn.microsoft.com/en-us/library/ff681553.aspx

     

    • Marked as answer by Teleute00 Wednesday, October 19, 2011 3:54 PM
    Wednesday, October 19, 2011 3:53 PM
    Moderator
  • Ugh - that's pretty ridiculous.  I miss just working straight in the DB - I'd be done by now.

    Anyway, thanks for your help.

    Wednesday, October 19, 2011 3:54 PM
  • Agreed. But CRM is more than just a db, it is a platform :)

     

    Wednesday, October 19, 2011 3:55 PM
    Moderator
  • Yeah - but with the advantages of built-in functionality come the disadvantages of built-in limitations.  Always a trade-off.  I just prefer full control - that's why I drive a standard, use text-editors for HTML editing, and run Linux.  ;-)
    Wednesday, October 19, 2011 3:57 PM
  • FWIW, I have stopped using n:n relationships and instead build a custom entity with two 1:n relationships to the original entities. That way I can see exactly what links to where. I hide the linking entity to users don't realize it's there.

     

    _mark 

    Wednesday, October 19, 2011 4:07 PM
  • That is an acceptable approach that I've also used.
    Wednesday, October 19, 2011 4:08 PM
    Moderator
  • FWIW, I have stopped using n:n relationships and instead build a custom entity with two 1:n relationships to the original entities. That way I can see exactly what links to where. I hide the linking entity to users don't realize it's there.

     

    _mark 

    That's what exists now, except I guess it's CRM that made that linking entity.  So basically we'd scrap what CRM did and make the same entity by hand?
    Wednesday, October 19, 2011 7:04 PM