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
48 Comments  |  Trackback Url | Bookmark this post with:        
 

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!

Xin Zhong commented on Thursday, 14-Oct-2010
hi, this tool works great. but a problem I am having with it right now is that the entities in it do not correspond with the entities I have in my CRM, it has some extra entities and lacks some entities. Is there anyway to change it? Thank you

Ashley Claire commented on Monday, 1-Nov-2010
I seem to be having the same problem as some other users. I cannot log into the system. How do I know what Instance I am using? (I am just tying "crm" like the example) What is the domain? Is that the same as the server? How do I pick an Authentication Type? I would really like to get the benefit that others are seeing with this tool, but I cannot even connect!

Mel Gerats commented on Tuesday, 2-Nov-2010
Hi Stacy, The instance is the the unique organisation name of the crm you are using. To view it you van type javascript:alert(ORG_UNIQUE_NAME) in the browser bar in CRM, or ask the person that installed it. The authenticationtype is the way you authenticate ("Active Directory" if you get a popup to log in, "On Premise" if you go to a login page). Domain is the active directory domain you are using.

Camille commented on Tuesday, 23-Nov-2010
I also am attempting to deactivate records using this tool (which I LOVE by the way) without any luck. Is this possible to do with your tool?

Eugen Twickler commented on Friday, 3-Dec-2010
We have a problem starting the tool - allways get a System.TypeInitializationException after the login. We are using CRM 4.0, Windows 7 and the assembly SDKs are already preinstalled (because of Outlook plugin?) Release 4.0.7333.2747 instead of download releases 4.0.7333.3.

Robin commented on Friday, 17-Dec-2010
Does this work with IFD? When yes, how?

Kirt commented on Wednesday, 16-Feb-2011
I am trying to use this with a Demo release version of CRM on a VPC but am continually getting the message that it is unable to connect to the remote server. The organisation on the VPC is CONTOSO. Please could you advise? Thanks

Wim De Coninck commented on Thursday, 17-Feb-2011
Hi Kirt, When i started developing the tool i also had issues connecting to the vpc. I could only get it to work on the vpc itself, not on a pc hosting the vpc. And for the settings I used the Active directory choice. Also check the unique org name of the CRM. to be sure go to the deployment manager and . In my vpc the organisation is LitWareInc, but the crm name was MicrosoftCRM. Let us know if this helped.

Greg commented on Friday, 25-Mar-2011
I'm having issues connecting too, but in a rather different way. The tool crashes with the following message as soon as I hit "OK" http://screencast.com/t/cKacaEmk I'm using a 64bit Win7, maybe this could cause some issues? Tried running it as an administrator too, made no difference at all. Thanks for hints how to get my data out of the crm with the tool ;-) BR, Greg

Wim De Coninck commented on Friday, 25-Mar-2011
Hi greg, The tool should run fine on a 64bit win7. Did you install the gac assemblies needed ? check the instructions on http://mscrmbulkupdatetool.codeplex.com/releases/view/33865 Hope this helped. Let us know

Greg commented on Tuesday, 29-Mar-2011
Wim, thanks for your reply. With the help of a colleague i finally succeeded with the installation of the GAC-assemblies and the tool works fine now! What was a bit confusing to me at first was that you actually have to download gacutil.exe (it exists as a stand-alone-exe - so you don't have to download the whole SDK where it's originally contained) and install the assemblies with this tool, drag and drop won't work. After that it was easy! You just have to remember that on a 64-bit client you have to install the 32 and the 64-bit files, if not already there.

Kia commented on Wednesday, 18-May-2011
Excellent tool!...

Alex commented on Tuesday, 5-Jul-2011
Hi Wim, I am currently trying to figure out a way to run a mass data import. My problem: I have a column labeled Company which is full, and a column labeled Account name which is empty. I need to get all of the Company names into the Account name column but when I try to upload it back into CRM it will not allow me to do so because I says that it is a duplicate since these people are already in the system. Can Orbit One solve this duplicate problem? Thank you.

Wim De Coninck commented on Tuesday, 5-Jul-2011
Hi Alix, We sure can help you :). The steps to do what you want could be the following: Create an advanced find view with only the column labeled Company, start the bulk update tool, select the entity (i presume account), select the advanced find view in the query field and do a get from crm. open the downloaded csv file and change the 'account' in the first line to 'company' (use the internal names: ie:new_company) and save the file. Next you do get from csv in the tool, and export to crm. That should do the trick. Hope this helps

Alex Marinelli commented on Tuesday, 5-Jul-2011
Sorry, just to clarify.. . I am taking a free form data field and trying to mass populate a lookup data field using your tool. I was unsure of what you meant "change look up fields" under your features list. Thank you!

Wim De Coninck commented on Wednesday, 6-Jul-2011
Alex, If you want to populate a lookup you'll need to pass the entityname and id of the entity comma seperated in the field: ie: account,CFB82935-BA09-43E8-8854-E491093FDF34. The steps become a bit more complex since you'll need to use VLOOKUP in excel or smth like that to get the correct value. If you need more help, could you drop me a line at wimdc (at) orbitone.com ? thx

M K commented on Tuesday, 19-Jul-2011
Is this tool updated for CRM 2011, or is there not a need for it because of the vast improvements to the built-in import tools?

Alex Marinelli commented on Monday, 25-Jul-2011
Hi Wim, We spoke a couple weeks back regarding the Orbit One tool. It has been working extremely well thus far. I just had a question: We are trying to mass update a radio button using orbit one. We have a list of about 1200 that are currently check off as “Allow” and we would like to change that to “Do Not Allow.” This is in regards to not sending this list of 1200 contacts our bulk e-mails. We set up the spread sheet with a donotbulkemail column and placed 1’s throughout the entire list of 1200 contacts (thinking that 1 would be the trigger for do not allow and that 0 meant allow.) Essentially what I am asking is, is there a way to switch the radio button with a mass update using Orbit One. Thanks, Alex Cactus Commerce

Wim De Coninck commented on Wednesday, 27-Jul-2011
M K, The update tool works with CRM 2011 on-premise if it is not installed with IFD... This just works because CRM 2011 still contains the old (CRM 4) webservices for backwards compatibility. The tool will probably not get an update because of the improvements on the out of the box import tools (as you hinted in your comment). If we feel the need arises to have additional capabilities for importing/updating records we will probably look into it, but it will most likely be a new tool since the CRM 2011 API has had quite some nice changes towards cleaner/more maintainable code. I hope this answered your question, and if you have a requirement that is not in the default import tools please let us know :-) Greetings Wim
Your name  *
Email  *
Your URL 
Comment  *

Please enter the text from the image


Orbit One on LinkedIn
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