locked
Dynamic Excel Worksheets RRS feed

  • Question

  • Hi,

    We have a telesales guy working for us who due to his disability, he's blind, uses Microsoft Excel with a voice application that reads out the contents of the cell to him. He's been using Excel for years and we currently provide him with an Excel spreadsheet each month with prospect details on it. 

    We use CRM4.0 for other areas of the business and would like to include his telesales process within this. Now we can generate dynamic Excel worksheets for him from running an advanced find under leads, but this doesn't allow us to get the information he sources from the contacts back into CRM. Short of importing his Excel spreadsheet at the end of the campaign, I can't see how we can allow this to be automated. 

    Ideally I'd like data he puts into the dynamic Excel spreadsheet to find it's way back into the appropriate fields in CRM.

    Any ideas? 

    Best Regards

    Dave C 
    Tuesday, March 31, 2009 8:53 PM

Answers

  • How about using this method:

    1. Export the data that you want to update to excel (you must include the columns that you want to be updated)
    2. You must export with Dynamic Worksheet option
    3. When you open the excel file, you will notice there is a hidden cloumn at the end of columns, make sure unhide it
    4. Save the Excel file as CSV (comma delimited) file
    5. Now you can edit the data (Don't edit the Header)
    6. When you import it back, you will see at the top of the screen the possibility to ‘Enrich data by updating records rather than creating new records.’ is given
    • Proposed as answer by Jeff Loucks Saturday, September 11, 2010 4:16 PM
    • Marked as answer by Jim Glass Jr Monday, September 13, 2010 3:22 PM
    Saturday, September 11, 2010 4:16 PM

All replies

  • Take a look at this tool that recently was added to codeplex http://mscrmbulkupdatetool.codeplex.com/

    it's not exactly a dynamic excel file 'sync' process. But the utility is designed to allow exports, like your current process, and then to re-import that information back into CRM after it's been updated.-

    I hope that helps!
    Scott Sewell, CustomerEffective | http:\\blog.CustomerEffective.com | Twitter:@ScottSewell
    Tuesday, March 31, 2009 9:02 PM
    Moderator
  • Scott,

    Thanks for that, looking at the documentation it looks interesting. Have you, or anyone else had any experience with using this application?

    Not being a developer, should I have any concern about implementing this code into our system? How will it fair should we upgrade in the future or implement the rollups that Microsoft provide from time to time?

    I may have got the wrong end of the stick, is this code installed on the CRM server or on the users PC? I can see a C:\windows\assembly directory on my local PC so I'm guessing this is the right location for it based on the release information below:

  • Extract the zip file
  • copy all the DLL Files from the Assemblies folder (and sub folders) to the Global Assembly Cache (located at : %windir%\assembly)
  • Run OrbitOne.CRMUpdater.Exe
  • Apologies if this is a noddy questions.

    Thanks again

    Dave C

Wednesday, April 1, 2009 8:42 AM
  • Hi Dave - I've used the code only in a development environment, not production. (It's newly released)- I'd encourage you to work with a developer/someone comfortable with working with code to help you test out the solution from codeplex. - (BTW - make sure you have updated windows to use all the latest .net updates.) If you're not a developer or don't have access to one, there might be alternate solutions that could help you retrieve the data from excel into CRM - Importing the data isn't difficult, it's the 'update' that makes it interesting. About 2 years ago, I used scribe to import/update pricing information into CRM from Excel spreadsheets - (Scribe is a commercial data integration tool that connects with CRM - but you need to work with a reseller in order to implement Scribe.)
    Scott Sewell, CustomerEffective | http:\\blog.CustomerEffective.com | Twitter:@ScottSewell
    Wednesday, April 1, 2009 10:55 AM
    Moderator
  • Scott,

    I've finally got around to looking at this application... however I have a question.
    I've downloaded the files and it mentiones copying dll files to the Global Assemblies Cache... under C:\Windows\Assembly. For some reason I can't drag and drop files into this directory, do you know how this can be achieved?

    Thanks again

    Dave C
    Dave C
    Thursday, July 16, 2009 3:55 PM
  • Hi Dave,
    You can also use the out of box Data Import Utility to re-import and update the records in CRM. For example, you export all leads with a defined view of the fields you want to excel; update the data in excel and then re-import that data using the Import Utility. This would update your lead records with the latest data, provided you follow some specific steps.
    You can refer to this link for the explanation.
    http://msdynamicscrmworld.blogspot.com/2009/07/crm-40-import-data-wizard-now-updating.html

    Cheers!
    Ashish Kapoor || ashish.1982@gmail.com http://msdynamicscrmworld.blogspot.com/
    Friday, July 17, 2009 7:34 AM
  • Ashish,

    Thanks for this, I'll give it a go... I came across the mention of this in another blog, but noticed that the functionality had been removed from the released version.

    Thanks again...

    Dave C
    Dave C
    Friday, July 17, 2009 8:35 AM
  • Ashish,

    Looks like this is not going to work... the functionality mentioned in the blog doesn't existing in our installed version of CRM. The filed mentioned in this screen shot, 'Enrich data by updating existing records rather than creating new records.' doesn't exist. 
    http://blogs.microsoft.co.il/blogs/rdt/image_7998282A.png

    Any thoughts?

    Dave C
    Dave C
    Friday, July 17, 2009 9:08 AM
  • Hi Dave,
    That is strange. You are using CRM 4.0 right? What update rollup are you working on? Did you unhide the GUID column and then placed it as the first column of the worksheet? Did you rename the column header as the Entity name, for example rename 'contactid' to 'Contact'. Try following these steps with one record and let me know what you get. Use the Dynamic worksheet export option.

    Cheers!
    Ashish Kapoor || ashish.1982@gmail.com http://msdynamicscrmworld.blogspot.com/
    Monday, July 20, 2009 3:07 PM
  • Hi dave,
    are you using CRM with an IFD deployment? With IFD Authentification, there is no ID field in the Excel File.
    Viele Grüße

    Michael Sulz
    axcentro GmbH
    MVP für Microsoft Dynamics CRM
    Monday, July 20, 2009 3:31 PM
  • Ashish,

    We're using CRM 4.0 Rollup 4 ( haven't had an opportunity to apply Rollup 5 yet )... I followed your instructions, but the issue is that the option isn't a tick box option on the screen when doing the import.

    Dave C
    Dave C
    Monday, July 20, 2009 9:05 PM
  • I'm sorry Michael, I don't know what an IFD deployment is... how would I know or find out?

    Thanks for the feedback.

    Dave C
    Dave C
    Monday, July 20, 2009 9:07 PM
  • Hi Dave,
    An IFD Deployment would mean an Internet Facing Deployment. Yours I believe should be an On Premise deployment right? The 'Enrich data by updating existing records rather than creating new records' check box is coming up fine for me, and I am also using the Update Rollup 4, so I do not see that being an issue. Can you send me a sample of the excel file, with one record that you are trying to import. You are using the Automatic Maps, right? It is really strange, because I have been able to get this to work for me everytime.
    An alternate solution would be develop a simple windows application which will parse through the excel file and update the Leads entity using the CRM Service. Howevr, I feel the Data Import should get working for you, its just a matter of missing something basic over here.

    Do let me know in case I can help out in any other way.
    Cheers!
    Ashish Kapoor || ashish.1982@gmail.com http://msdynamicscrmworld.blogspot.com/
    Friday, July 24, 2009 2:27 PM
  • Thanks, this was just what I was looking for as well. A pity the Help doesn't explain this.

    However, when I go through this the next screen shows "Assign to:" which is me. How can I change this to someone else as the selection icon is not active?

    As I am using this as an administrator for maintenance of the data I don't want to assign everything I enrich to me, but wish to either leave it assigned where it was or assign it to someone else.

    Thanks,
    Tony
    Tuesday, September 1, 2009 10:24 PM
  • How about using this method:

    1. Export the data that you want to update to excel (you must include the columns that you want to be updated)
    2. You must export with Dynamic Worksheet option
    3. When you open the excel file, you will notice there is a hidden cloumn at the end of columns, make sure unhide it
    4. Save the Excel file as CSV (comma delimited) file
    5. Now you can edit the data (Don't edit the Header)
    6. When you import it back, you will see at the top of the screen the possibility to ‘Enrich data by updating records rather than creating new records.’ is given
    • Proposed as answer by Jeff Loucks Saturday, September 11, 2010 4:16 PM
    • Marked as answer by Jim Glass Jr Monday, September 13, 2010 3:22 PM
    Saturday, September 11, 2010 4:16 PM