Dynamics CRM 4.0 tool - Bulk update / export to csv 

Tags: .NET, CRM 4.0

One of our customers recently asked to do an update on their instance of the microsoft CRM 4.0 platform.

The goal was to change the current Country Picklist to a lookup field which points to a new_country entity.

The new_country entity contains a few translations of the countries in different languages like english, dutch and french, and the ISO codes for each country.

Try to update that in CRM 4.0. So we start looking. The first thing I came across was the MSCRM 4.0 Bulk Data Export Tool

This tool allows you to export data generated by an advanced find view to a CSV file which you can later on import in CRM 4.0 trhough an update job. So I started playing around with it.

The major issue for me is that with that tool I couldn’t get the lookup field to be populated. I tried a bunch of different things like “account,E52B43A8-5FC6-4934-A5A3-96B2983EE140″ or just the “E52B43A8-5FC6-4934-A5A3-96B2983EE140″ but nothing seemed to work.

So I decided to create a new tool that basically does what i want, without creating an import job. Everything is done through the CRM SDK.

A few things I thought are very cool:

  • You can save your connection. We are a hosting company and we have 6 different CRM instances on 3 different servers, so this is quite nice.
  • One of our clients has 15.000+ records in their contacts. Because CRM returns only 5.000 records for each request, I can’t update them all in one go. This was changed by making multiple requests if necessary.
  • Access all the records and all the fields. In the MSCRM 4.0 Bulk Data Export Tool you can only use the tool on Advanced find views. I allow you to use the tool on the main entity. Which is really helpfull in some cases.
  • It allows you to choose the Authentication type. You can connect to CRM with Active Directory, SPLA, Passport (this last one I haven’t been able to test so if anybody has a CRM with passport please let me know)

It is still a work in progress but it does the job quite nice already so try it out.
The CRM Bulk Update / Export Tool

I hope it helps some of you in your daily (or not so daily) tasks.
I know it saved a colleague of mine about 80% of his time for only one case, and I consider that a big improvement.

 
Posted by Wim De Coninck on 27-Feb-09
29 Comments  |  Trackback Url | Bookmark this post with:        
 
ab

Comments


Adam commented on Friday, 10-Jul-2009
I'm having issues getting it to connect. I am able to connect to the CRM with the other CRM Import tool from Codeplex, but not with your orbitone. We have the CRM installed on the default instance. I do not suppose that has anything to do with it does it? I am just leaving that field blank. The only error I get is "Failed to log in." No matter what I change, or if I remove the password completely, I get the same error. Any ideas?

Wim De Coninck commented on Monday, 13-Jul-2009
Hi Adam, I need to fill out the instance field... Otherwise it won't work. Grtz

Michael Dodd commented on Thursday, 6-Aug-2009
Can you tell me where I'm suppose to drop in the 2 sdk .dll files? I keep reading the comments on CodePlex, but they aren't very clear. I'm trying to open this from my local machine, not the CRM Server and I have no idea what the gactil is or how to use it. Any documentation would be appreciated... --MD

Wim De Coninck commented on Friday, 7-Aug-2009
Hi Michael, As seen on the download page: "install the microsoft.*.dll Files from the Assemblies folder (and sub folders) in the Global Assembly Cache (located at : %windir%\assembly)". In other words: Open the zip file, drag the microsoft.*.dll files from there to the %windir%\assembly. If your unable to drag drop because of permissions set on the GAC folder you need to use the gacutil. It basically does the same thing as the drag drop. If you need more information on gacutil check out http://msdn.microsoft.com/en-us/library/ex0ss12c%28VS.80%29.aspx Hope this helps.

Fpieters commented on Monday, 10-Aug-2009
Very good tool. 2 possible upgrades: a/ use the display name instead of schema names for entities and field b/ allow creation of new records through "get from CSV" Keep up the good work - FP

Sebastian commented on Wednesday, 12-Aug-2009
Hello, is very very cool because it save so much time! Im not very good in such webservice things and so it helps much! One question: How i can do an export to csv with all records of a view and not only of the frist 5000. I want to export the csv. Change them and then i want to reimport. Thanks

Wim De Coninck commented on Thursday, 13-Aug-2009
Hi Sebastian, Thats the idea of the tool, you don't need to do anything to export more than 5000 the tool does that for you ... I've just retested to make sure I'm not lying and go 19000+ records from my view and exported them to csv. Maybe you need a newer version, but i doubt it 'cause this feature has been present since the start. Maybe you only get 5000 records via the view? If you can't get over the 5000 mark drop us another line with more information about the configuration of your CRM environment and user type you are using. Hope this answers your question.

James @ PEAK 15 commented on Thursday, 3-Sep-2009
Sebastian. This is a fantastic tool. Really appreciate the contribution to the CRM community.

Anony Mouse commented on Friday, 4-Sep-2009
Thanks, this is a very useful tool. Works, too!

David Challen commented on Friday, 9-Oct-2009
I've just started to use this tool to enable us to export a prospect list to .csv for telesales. However, I'm getting quite a lot of errors when updating the .csv back into CRM. There is no explanation why the errors occur, it not on all records, but a good 50%. Without a reason it's difficult to find the fault. Do you have any suggestions on what I can do.

Wim De Coninck commented on Friday, 9-Oct-2009
Hi David, I don't really know what the problem might be, what you could do is limit the number of fields when you do the import. Some fields just don't work due to limitations of CRM SDK. These are mostly the memo fields, but sometimes the nchar fields (like description). Make sure you dont have any \n (newline) characters in the CSV (besides the next record of course) which tend to occur on description fields. And make sure you use version 2.4. It's the most recent one where a rather large bug was fixed. Let us know if this worked for you.

Dynamics CRM Consultant - Gary Kellett commented on Sunday, 11-Oct-2009
Wim - this is awesome. It saved me so much time. I am sure it will save my clients a huge amount of time too! Thank you very much for sharing. Feel free to drop me an email so we can catch-up off-site. Gary

Sebastian commented on Thursday, 29-Oct-2009
Hello, i must repeat: It is awesome! I use it for the last 2 month and it is fantastic!! Thanks! Question: At special format of my csv and if the GUID is not found the tool create the record. Today i try to want to figure out what i must do that the tool do a create! I try: empty id - no non exisiting id - no id from another entity - no But belive me some csv i and no id match the tool create. In footer i read [NEW ENTITY]... Can the tool create?

Wim De Coninck commented on Friday, 30-Oct-2009
Hello Sebastian, You can indeed create new entities. The trick is to just remove the entire id column. ie for contacts you only need firstname, lastname as columnheaders. Don't include the contactid header and the records will be created.

Sebastian commented on Friday, 30-Oct-2009
Fine! Thanks!

dunkelgruen commented on Tuesday, 3-Nov-2009
Nice! Is it planned that the tool support command line feature for scripting?

Wim De Coninck commented on Tuesday, 3-Nov-2009
Dunkelgruen, It is planned for the near future ... I can't say when I'll get around to coding it, but it's coming :)

Peter Moss commented on Friday, 27-Nov-2009
Hi, When i export a list of contacts I would expect (as per the view in crm) to see the account name that the contact is part of however this column is blank. Is there a reason for this? p.s nice tool other than that :) Thanks

Sebastian commented on Sunday, 3-Jan-2010
Hello, i have problems to update a bit Filed. I try 0/1 and true/false... Nothing happens... Check all finde no problems and i have no error. Thanks Sebastian

Wim De Coninck commented on Monday, 4-Jan-2010
Hi Peter, This is normal since the account name is from a different entity. The only thing the tool displays are the attributes of the selected entity. To get the account name where you want it you'll need to export accounts and make a lookup list in Excel.

Tom commented on Thursday, 11-Feb-2010
Hello, I can use this tool fine for another client. But for another, I receive a message that the organization name does not exist. I am using crm online and can access the application fine through the internet. Any ideas? Btw .. great tool otherwise!

Wim De Coninck commented on Tuesday, 16-Feb-2010
Hi Tom, You can check the Organisation name (just to be sure) by hitting the F12 button in IE8 (when in the crm web interface). Select the script - tab (below the menu) and look for line 35 or surrounding. There should be something like ORG_UNIQUE_NAME. This is what you need to use. Let us know if it worked for you.

Ryan commented on Wednesday, 24-Feb-2010
Hi, wondering if this tool works for Web-based CRM 4.0 or just the on-premise version? If it does, what would the proper Authentication type be? Thanks, hoping I can use this great tool.

Wim De Coninck commented on Thursday, 25-Feb-2010
Hi Ryan, You can use the tool for Web based CRM 4.0. You need to specify the type Passport for the Authentication. Hope this helped.

Staci Nisbett commented on Friday, 26-Feb-2010
I am trying to use this tool to load NEW contact to company and contact to contact relationships and I followed instructions to leave the primary key blank to load new and I keep getting an error that it must be in the proper format. How can I upload new relationships for existing records using the tool?

Wim De Coninck commented on Monday, 1-Mar-2010
Hi Staci, If you want to create a new entity you should just remove the entire column. not just leave it blank. The tool will pick this up as a bad format. And don't forget to use the following format for your lookup fields: "account,DAD753FD-CB31-4932-9736-F06BC1000BD8". Hope this helped. Let us know. Wim.

Staci Nisbett commented on Monday, 1-Mar-2010
That worked, Wim! Thanks for the tip.

Staci Nisbett commented on Monday, 26-Apr-2010
I am trying to activate and deactive accounts using the import tool and getting an error. I am using Active and Inactive as the options for the Statecode field but it is not changing the status in CRM. When I export the contacts and get the "statecode" field it does not give me numerical values...just the words which is different from all other fields. Is there something else I should be doing to accomplish this task?

Paul Bergen commented on Wednesday, 7-Jul-2010
Love this tool, it has saved us days of work! Well done! Question: We hit an error when importing to customeraddress... even though we have the parentid,objecttypecode, addresstypecode and line1 populated... unexpected error 0x80040216. I searched Google for: "crm 4 unexpected error 0x80040216" and the top article may (or may not) be relevant. Any suggestions are appreciated!
Your name  *
Email  *
Your URL 
Comment  *

Please enter the text from the image


Contact us - Raas Van Gaverestraat 83, 9000 Gent, Belgium - Tel. +32 (9) 330.15.00 - Privacy Statement - Sitemap - Sign In Developed with Microsoft Office SharePoint Server 2007