Sign in
Microsoft.com
 
Microsoft
 
 
 
Microsoft > Dynamics Forums > CRM > Import a csv file with a lookup field inside in CRM 4.0

Answered Import a csv file with a lookup field inside in CRM 4.0

    •  
      Friday, March 07, 2008 10:05 AM
       
       
      Sign In to Vote
      0
      Sign In to Vote
      Hi all,

      I'm trying to import some data within the Import Manager included in CRM 4.0, but I'm stuck with this message "Source Data is not in the required format".

      The file contains 2 columns (a lookup type, a nvarchar). The first column stuck wheither I give the Guid in the csv or the label.
      Here my csv :
      Code Snippet

      "My_LookUp_Field_Column" , "My_Label_Column"
      "BB51BC3D-98EB-DC11-9C73-0002B3C73EB0" , "AAA"
      "BB51BC3D-98EB-DC11-9C73-0002B3C73EB0" , "BBB"
      "BB51BC3D-98EB-DC11-9C73-0002B3C73EB0", "CCC"
      "BB51BC3D-98EB-DC11-9C73-0002B3C73EB0", "DDD"
      "BB51BC3D-98EB-DC11-9C73-0002B3C73EB0", "EEE"


      Now, I don't know how to write the data in the first field. Should I give the Guid or a label ? And how do I write ? I mean the correct syntaxe that let me to import the data for my entity.

      The Entity inherits from another Entity, that's explain my Lookup field.

       

    Answers

    • Friday, March 07, 2008 3:58 PM
       
       Answered
      Sign In to Vote
      1
      Sign In to Vote

      Hi all.

       

      In order to import lookup fields you need to render the CSV source attribute in the following format:

       

               " [ Related Entity Type Name ] , [ Related Entity Instance GUID ] "

      For example

               "account,{BB51BC3D-98EB-DC11-9C73-0002B3C73EB0}"

       

      Here is a complete contacts.csv example

       

      FirstName,LastName,ParentCustomer

      Adi,Katz,"account,{BB51BC3D-98EB-DC11-9C73-0002B3C73EB0}"

       

      Notice the " " wrapping the lookup value. This is because a comma (,) is used to separate the Related Entity type and value.

       

      There is also a great import tool built for CRM 4.0. Follow this link to download :

      http://blogs.msdn.com/crm/archive/2008/01/25/data-manipulation-tool.aspx

       

      Cheers,

      Adi

       

       

       

    All Replies

    • Friday, March 07, 2008 12:06 PM
      Moderator
       
       
      Sign In to Vote
      0
      Sign In to Vote

      You should give the Guid, but I think it might need to be in curly braces {} - e.g.

       

      "My_LookUp_Field_Column" , "My_Label_Column"
      "{BB51BC3D-98EB-DC11-9C73-0002B3C73EB0}" , "AAA"

       
    • Friday, March 07, 2008 12:42 PM
       
       
      Sign In to Vote
      0
      Sign In to Vote
      I did it, too but I've got the same error. So I don't have any further solution. But if I let the field empty, it works.
       
    • Friday, March 07, 2008 3:58 PM
       
       Answered
      Sign In to Vote
      1
      Sign In to Vote

      Hi all.

       

      In order to import lookup fields you need to render the CSV source attribute in the following format:

       

               " [ Related Entity Type Name ] , [ Related Entity Instance GUID ] "

      For example

               "account,{BB51BC3D-98EB-DC11-9C73-0002B3C73EB0}"

       

      Here is a complete contacts.csv example

       

      FirstName,LastName,ParentCustomer

      Adi,Katz,"account,{BB51BC3D-98EB-DC11-9C73-0002B3C73EB0}"

       

      Notice the " " wrapping the lookup value. This is because a comma (,) is used to separate the Related Entity type and value.

       

      There is also a great import tool built for CRM 4.0. Follow this link to download :

      http://blogs.msdn.com/crm/archive/2008/01/25/data-manipulation-tool.aspx

       

      Cheers,

      Adi

       

       

       
    • Friday, March 07, 2008 4:26 PM
       
       
      Sign In to Vote
      0
      Sign In to Vote
      Great !! it works. Thanks a lot Adi Katz. I was stucked for a day -_-
       
    • Tuesday, March 11, 2008 3:14 PM
       
       
      Sign In to Vote
      0
      Sign In to Vote
      I am having some issues, maybe I am missing something small (more than likely ). I am looking to import data for a child entity that I created off the contact entity.

      Contact entity (I renamed to Client but it still holds the client name attribute)
      Family entity (I create a N:1 relationship with the Contact as the Primary Entity).

      Here is the csv file I used to import to contact:

      ClientID,FirstName,LastName
      {34irut905tu309jgdfnogkn},Joel,Test

      This worked fine using the import wizard in CRM


      Here is the csv for the family entity:
      ClientID,Name
      "contact,{34irut905tu309jgdfnogkn}",ChildTest

      I mapped the ClientID field to the lookup field that was created when I set up the N:1 relationship in the Family entity. When I try to load the file it comes up with an error.
      Description: "The source data is not in the required format"
      Column Heading: "ClientID"
      Column Value: "contact,{34irut905tu309jgdfnogkn}"
      Error Code: "0x80040356"

      Not sure why this isn't working, any ideas?

      Thanks,

      Joel

       
    • Tuesday, March 18, 2008 6:19 AM
       
       
      Sign In to Vote
      0
      Sign In to Vote

       

      Hello I have been tring this tecnique with other entities
      like products that have a few necessary lookup fields

      uom uomschedule ...
      does not seem to be working I get a non existing entity error messege

       

      product name,ID,currencie,salesunit,defaultunit,decimals
      CRMprod1,CRM-CA-PRO,"transactioncurrencies,{F425745A-67EF-DC11-9FB3-00104BD27F1C}","uomschedule,{7C1BA26A-47FD-DB11-A8E4-00104BD27F1C}","uom,{7D1BA26A-47FD-DB11-A8E4-00104BD27F1C}",2

      does this mean that I cannot create new products with the csv import?
      I am using a crm 4 upgraded italian version done with the trial italian version i could see quite aa few busgs.

      BR
      Claudio

       
    • Tuesday, March 18, 2008 8:48 AM
       
       
      Sign In to Vote
      0
      Sign In to Vote
      Seems weird. If yours parent id is defined in your CRM, the import should work and create your new products. And be carefull, when you have special character put a double quote.

      It would give this:
      CRMprod1,"CRM-CA-PRO","transactioncurrencies,{F425745A-67EF-DC11-9FB3-00104BD27F1C}","uomschedule,{7C1BA26A-47FD-DB11-A8E4-00104BD27F1C}","uom,{7D1BA26A-47FD-DB11-A8E4-00104BD27F1C}",2

      Puting ' " ' is not required but necessary in some case.


       
    • Thursday, May 01, 2008 10:20 AM
       
       
      Sign In to Vote
      0
      Sign In to Vote

      hi,

       

      I´m trying to import data from a txt file(Tab Delimited) to CRM 4. How can I link a primary contact to their account using the GUID?

       

      I need an eg.

       

      Somebody help me?

       

      cheers

       

      Csousa

       
    • Monday, May 05, 2008 4:54 PM
       
       
      Sign In to Vote
      0
      Sign In to Vote

      hi adi,

       

      i don´t use .csv file, because the data file for import contain non-English caracters. i use a text file (tab delimited) for file.

       

      In this case how can I link a contact to an account using the GUID?

       

      Any ideia? i need an eg. to understand.

       

      cheers,

      csousa

       

       

       

       

       

       
    • Monday, June 02, 2008 1:51 PM
       
       
      Sign In to Vote
      0
      Sign In to Vote

      Small remark,

       

      it worked for me only when I provide mappings.

       

      Automatic mappings - allways error - lookup reference cannot be found.

       

      The import tool is really piece which has to be improved. I do not understand why Microsoft does not support the simplest way - direct Excel file.

       

       
    • Friday, July 11, 2008 8:53 AM
       
       
      Sign In to Vote
      0
      Sign In to Vote

      Thanks...this is useful.  I now know what I need in my import file in order to import lookup values.

       

      However, how do I find the GUIDs of the lookup values of the lookup tables that are contained in my CSV import file?

       

      Please advise.

       

      Note that I do not have access to the CRM server by RDP or equivalent - only browser access

       

      Thanks in advance,

       

      Danny

       
    • Monday, July 14, 2008 3:29 PM
       
       
      Sign In to Vote
      0
      Sign In to Vote

      Hi Adi

       

      I am completely new to this and very confused. Here is where I am at:

       

      1. I have imported a list of account from a csv file.  The file had just once column called accounts. 

       

      Account
      Mikes Bikes Ltd
      Johns Cars Company

       

      So I now have all both my accounts with no further information.  This is ok.

       

      2.  I now want to import the contacts for those accounts.   These are in a seperate csv file with two columns

      Account Contact
      Mikes Bikes Ltd Mike
      Mikes Bikes Ltd Tim
      Johns Cars Company John
      Johns Cars Company Lucy

       

      How do I set up the mapping in Dynamics and do I need to change my csv file

       

      I am a really basic user so don't know where to get info on GUIDs etc

       

      Thanks in advance


      Ed

       
    • Wednesday, July 16, 2008 5:19 PM
       
       
      Sign In to Vote
      0
      Sign In to Vote

      When using a tab delimited file, use the same rule, but remove the quotes so that the value is a comma separated tupple with the entity name and the guid, for instance:

       

      custom_dummy \t parentaccount

      Dummy value 1 \t account,{B38003FA-3C8F-423c-AA48-43C36DC5CC74}

      Dummy value 2 \t account,{B38003FA-3C8F-423c-AA48-43C36DC5CC75}

       

      where "\t" stands for tab.

       

      Hope it helps

       

      RR

       
    • Friday, July 18, 2008 10:06 AM
       
       
      Sign In to Vote
      0
      Sign In to Vote

       

      i'm also encountering the same issue. please enlighten us on this. thanks so much!

       

      (:

       
    • Friday, August 29, 2008 4:21 PM
       
       
      Sign In to Vote
      0
      Sign In to Vote
       Claudio77 wrote:

      Hello I have been tring this tecnique with other entities
      like products that have a few necessary lookup fields

      uom uomschedule ...
      does not seem to be working I get a non existing entity error messege

       

      product name,ID,currencie,salesunit,defaultunit,decimals
      CRMprod1,CRM-CA-PRO,"transactioncurrencies,{F425745A-67EF-DC11-9FB3-00104BD27F1C}","uomschedule,{7C1BA26A-47FD-DB11-A8E4-00104BD27F1C}","uom,{7D1BA26A-47FD-DB11-A8E4-00104BD27F1C}",2

      does this mean that I cannot create new products with the csv import?
      I am using a crm 4 upgraded italian version done with the trial italian version i could see quite aa few busgs.

      BR
      Claudio

      Hi,

      Have you find a solution for this issue? I have the same problem.

       

      When I import a list of products with this column values:

      ID,product name,decimals,salesunit,defaultunit

      ProdID1,CRMprodA,2,"uomschedule,{F262071A-6870-DD11-8C44-001E4F3B172F}","uom,{F362071A-6870-DD11-8C44-001E4F3B172F}"

      Everything works fine, but when I try to import another list of products with currencie column:

      ID,product name,decimals,salesunit,defaultunit,currencie

      ProdID1,CRMprodA,2,"uomschedule,{F262071A-6870-DD11-8C44-001E4F3B172F}","uom,{F362071A-6870-DD11-8C44-001E4F3B172F}","transactioncurrencies,{A7FC9AA4-8A47-DD11-89C1-001E4F3B172F}"
      I get the non existing entity error message.

       

      There is something wrong with currencie value?

       

      Any ideas?

       

       
    • Friday, August 29, 2008 4:28 PM
       
       
      Sign In to Vote
      0
      Sign In to Vote

      ...,"transactioncurrencies,{A7FC9AA4-8A47-DD11-89C1-001E4F3B172F}" should be transactioncurrency.

       

      Adi

       
    • Tuesday, September 02, 2008 3:01 PM
       
       
      Sign In to Vote
      0
      Sign In to Vote
       Adi Katz wrote:

      ...,"transactioncurrencies,{A7FC9AA4-8A47-DD11-89C1-001E4F3B172F}" should be transactioncurrency.

      Great! it works. Thanks a lot Adi

       
    • Tuesday, September 02, 2008 8:32 PM
       
       
      Sign In to Vote
      0
      Sign In to Vote
      I have also had success importing data using the built in CRM import tool by ensuring the Field names in my CSV file exactly match the Field display names in CRM.
       
    • Thursday, September 04, 2008 8:10 PM
       
       
      Sign In to Vote
      0
      Sign In to Vote
      Does anyone know how to find out the GUID of a lookup field entry?

       

       
    • Friday, September 05, 2008 3:29 PM
       
       
      Sign In to Vote
      0
      Sign In to Vote

       

      http://www.microsoft.com/dynamics/crm/using/configure/importtips.mspx#EK3AE

       

      have a look at the link above..

       

      I thought things were going to be simple....CRM v 3 consultant. not worked on v4 before, and took on my first installation and config (9 months after I last touched CRM 3) .... challenge.

       

      J

       
    • Wednesday, September 24, 2008 12:39 AM
       
       
      Sign In to Vote
      0
      Sign In to Vote

      Many thanks to Adi and Rui, I almost got this complicated import working... 

       

      I have three GUIDs I need to import into this intersection entity.

      One FK back to the AccountID (which works)

      and two to custom entities (which don't)

       

      I'm using Rui's tab delimited format, and using the entity name as suggested by Adi, and I'm not getting any errors on import, but the data is not appearing when I look into the CRM record, and the GUIDs are NULL in the SQL table...

       

      This works:

      account,{CC7E0B76-BC89-DD11-B03D-0003FF410D80}

       

       

      But these two don't work:

       

      new_product,{197579C0-1F5F-DD11-B03D-0003FF410D80}

      new_payorcattype,{1B53B790-1F5F-DD11-B03D-0003FF410D80}

       

      Any clues greatly appreciated...

      Thanks,

      Dave

       
    • Monday, October 06, 2008 6:23 PM
       
       
      Sign In to Vote
      0
      Sign In to Vote

      OK, I might as well update this thread with the findings I've made:

      1) According to MS Support, you CANNOT use the Tools \ Import Data functionality in CRM to import GUIDS from related Custom Entities!  For that, you have to use the Data Migration Manager...

      2) Fear not, the DMM is a good tool, it took me only an hour or two to download and install it, read some help, and get my first import sample to work.

      3) While the DMM can even create entities, I kept things pretty simple.  In my CSV file, I created columns for the FK GUIDS to the entity I was importing into.

      4) The format of the GUIDs when using the DMM is to NOT use the curly braces, so in my Excel file, I just have the GUID.  Then when you create the CSV, it puts quotes around the GUIDs, and that works.

      5) To get the GUIDs into my Excel spreadsheet, I:

      a) Used SQL Server queries to retrieve the NAME and GUID from the linked entities, and then import that as a new sheet in the same Excel 2007 workbook where the data I am importing resides.

      b) I then used the VLOOKUP functionality of Excel to match the NAME from my imported data to the GUID on the data exported from CRM.

       

      So, for example, I had product names and GUIDs in one sheet:

       

      Name

                                                         GUID
      1.25" Single Sided        17579C0-1F5F-DD11-B03D-0003FF410D80
      2" Square for Sensitive Skin  (etc).
      2.75" Round Electrode

      3.5" Square Garment

      I then selected the entire Name/GUID data area, and created a name for it (ProductGUIDTable).

       

      Then, I had this type of formula in a new column in the Excel sheet with the rest of the data to be imported:

      =VLOOKUP(AR2,ProductGUIDTable,2,FALSE)

       

      For those unfamiliar with this great Excel function, what this does is take the Product Name from column AR2, look it up in my ProductGUIDTable, and return the GUID (column 2).  (The FALSE requires the name to exactly match.) 

       

      Finally, because all of the data didn't match, or the source data was blank, I added a little error checker to that function, so the final result in that new Excel column of GUIDs was:

       

      =IF(ISNA(VLOOKUP(AR2,ProductGUIDTable,2,FALSE)),"",VLOOKUP(AR2,ProductGUIDTable,2,FALSE))

       

      This is one IF statement that says:  If the Lookup returns "N/A" (didn't find a match), then the value is spaces (""), otherwise (it DID find a match), give me the GUID that was found...

       

      I then mapped the GUID to the FK on the custom entity, and voila, I imported the new custom entity data, linked to the existing FK GUIDs in the other custom entities...

       

      Not too bad, actually, and way cheaper than the alternatives...

      HTH,

      Dave

       
    • Thursday, October 30, 2008 4:51 PM
       
       
      Sign In to Vote
      0
      Sign In to Vote
      I used this approach to importing a basic KB Article.  Here is my sample file.  The articles imported (by default) as Draft. 

       

      Here is the sample.

       

      subject,title,articlexml,keywords,articleid,kbarticletemplateid
      "subject,{61EE445F-42AB-4072-9D28-C197DE70568A}","Test Import Articles","<articledata><section id='0'><content><![CDATA[<P>This is a test Article Import,Article Text without html</P>]]></content></section></articledata>","import, article","KB-09001","kbarticletemplate,{BA618F0E-99A6-DD11-A3C4-0003FF39BE6A}"

       

      The complexity I see in importing Articles from outside source, is wrapping the content in a basic template xml wrapper.  In this case, the template I used just had one section.  Essentially, the ArticleXML should match the template framework (at least this is what I am suspecting).

       

       
    • Sunday, February 08, 2009 12:10 PM
       
       
      Sign In to Vote
      0
      Sign In to Vote
      Hi Adi,
      I tried the new tool  and I don't see an option to import there a csv to crm.
      It does export with GUID but this not much help.
      The problem is to import a csv file which is large in schema and records.
      I tried the source and the wizard and don't see there option to load file.
      The manual update suggested there is not applicable.
      Thanks.
       
    • Friday, May 08, 2009 5:47 PM
       
       
      Sign In to Vote
      0
      Sign In to Vote
      Hi, TB
      I am so confused as to how best to import articles into CRM 4.0 from a csv file.

      I tried data map in CRM 4.0 but I can't figure out how to get the set up the csv file to accommodate specific attributes....article, article xml, base template, import sequence number. Can you guide me? I will be getting the data from a Lotus Notes database base view, then exporting into csv.

      Thanks
      Bernardina
      Apps
       
    • Monday, May 11, 2009 5:41 PM
       
       
      Sign In to Vote
      0
      Sign In to Vote
      Hi, Adi
      I replied below, but wonder if you can also assist me?

      I tried data map in CRM 4.0 but I can't figure out how to get the set up the csv file to accommodate specific attributes....article, article xml, base template, import sequence number. Can you guide me? I will be getting the data from a Lotus Notes database base view, then exporting into csv.

      Thank you
      Apps
       
    • Thursday, June 04, 2009 12:49 PM
       
       
      Sign In to Vote
      0
      Sign In to Vote
      This is What i have written in the CSV file , but i am not able to IMPORT as the Says,

      Source data not in the expected in the Format  0X80040356

      is this because of the lookup filed that i am trying to import ..
      kindly please help me...

      createdon,fslr_accepteddate,fslr_approveddate,fslr_FrameworkagreementId ,fslr_minpercentagefrommax,fslr_pricevolumetableid,fslr_revisionno,fslr_signaturedate,fslr_signaturename,importsequencenumber,owneridname,statuscode,fslr_accountid

      '2009-03-19 11:52:27.000','2006-04-10 22:00:00.000','2006-04-10 22:00:00.000','2C9B105D-1EBD-DD11-BFE9-005056B07FC9 ','90','18CE5D6A-7C14-DE11-BFEA-005056B07FC9','1.0','2006-04-10 22:00:00.000','Stephan Hansen','','Nicole Hirschmann','1',''

      Thanks
       
    • Monday, October 18, 2010 6:42 AM
       
       
      Sign In to Vote
      0
      Sign In to Vote

      The simplest method I've found is not use GUID's at all.  too complicated.

       

      Instead, create your own unique dummy value (nvarchar or integer) for each account record in your .csv file prior to import.

      in your contacts (or linked record) be sure to add a column in your .csv file that maps the contact record to the account dummy value.

      Launch Data Migration Manaager

      Import the accounts with the unique dummy value field for each account

      then import the contact record defining the contact dummy value to the account dummy value field (must be the same).

      DMM takes care of it.

       
    • Monday, November 15, 2010 9:35 PM
       
       
      Sign In to Vote
      0
      Sign In to Vote

      Hi - I seem to be able to 'enrich' my Account data in CRM 4.0 by using a csv file with 3 columns - Account (the hidden GUID), Account Name (it seems to need this) and then Primary Contact, with the Import Wizard.  The import looks like it's working and even reports the number of records that were successful but when I go to the Accounts, the Primary Contact is blank.

      Any suggestions would be greatly appreciated.

      Thank you.

      Positively
       
    • Friday, April 01, 2011 6:48 PM
       
       Proposed Answer
      Sign In to Vote
      1
      Sign In to Vote

      Please refer this website.

      http://blogs.msdn.com/b/crm/archive/2008/03/07/importing-data-in-crm-4-0-made-easier.aspx

      Thanks,

       Israel.

      • Proposed As Answer by Kumar G Friday, April 22, 2011 8:07 PM
      •  
       
    • Wednesday, July 06, 2011 3:35 PM
       
       Proposed Answer
      Sign In to Vote
      1
      Sign In to Vote

      Well, after a long time trying to fix this issue on my 4.0 system.  I entered the GUID as follows 

      first name, last name, account name

      john, doe, 2BC190DA-5181-DF11-81A6-005056881E8B

       

      This is the only way it worked for me, so if the above doesn't work please try this.

      • Proposed As Answer by bburmeister170 Wednesday, July 06, 2011 3:35 PM
      •  
       
    • Monday, April 09, 2012 7:24 AM
       
       
      Sign In to Vote
      0
      Sign In to Vote

      Hello Metal,

      I same thing but it is not working. In my Project both are custom entities and after giving schema name and record GUID of target entity it is not updating lookup value. It is updating Text field but not updating look up value.Please help

      Thank you

      Harsha

       
    • Monday, April 09, 2012 1:11 PM
       
       
      Sign In to Vote
      0
      Sign In to Vote

      Hi Harsha,

      to get the quick and proper response please open a new thread since the existing thread has so many replies.

      Thanks, - Israel Pradeep, - Software Engineer & PMP & B.I - Entrepreneur. - I am glad I could help!!! - If this post answers your question, please click “Mark as Answer” on the post and “Mark as Helpful”. For More Information, please feel free to visit our website- http://xrmxtensibles.wordpress.com/