Armanino Blog
Article

Import and Export to Excel and CRM 2011

April 18, 2012

I just updated CRM contact records for changes in addresses I received from one of our data providers. I decided to use the export/import function in CRM 2011 by exporting the contact data to Excel, copying the changes into the Excel spreadsheet, and importing the changes back into CRM 2011.

These are the steps I used:

  • Created a view to select the contacts I wanted and the columns I wanted to update
  • Exported to Excel, indicating in the export screen that I would be re-importing the data
  • I used VLOOKUP to identify the records that had updated information in the source file from my data provider
  • I then copy and pasted the changes into the Excel spreadsheet
  • I saved the Excel spreadsheet (XML format - default)
  • I imported the Excel spreadsheet into CRM

Easy to do. And I only had one problem. And it took awhile for me to resolve; only because I'm so darn busy that any blip in the process gets immediately de-escalated on my priority list.

So the error was that none of my records updated. That's a big error. The error message was, "The following column indexes have data types that have changed since the file was exported: 15"

So I looked at the XML data in Notepad and determined that the 15th column was the Zip code. Really, a Zip code didn't import? You're kidding me, right? No. It was the zip code. I checked to see that the cell formats were 'Text' and I even copied and pasted "Special >> Format". Didn't work.

So I looked at the object in CRM and determined that the standard field size is 20; plenty of room for a Zip code; unless there's extra stuff that I can't see in Notepad. Could be.

I resolved the problem by trimming the Zip code values in the source file using the "TRIM" function in Excel, and then copy and pasting into the Excel spreadsheet for import back to CRM.

It worked. Geez!

Done. On to the next task.

I still like the export/import function. Here's a video of it in action: http://youtu.be/e6UAnSvuQHs

Stay In Touch

Sign up to stay up-to-date with the latest accounting regulations, best practices, industry news and technology insights to run your business.

Resources
Related News & Insights
Costco Travel Webinar
Webinar
One of the world’s top retailers reveals their cloud upgrade process.

May 19, 2022 | 11:00 AM - 11:30 AM PT
Overview of Sage Intacct R2 Release Webinar
Webinar
Learn the newest features and enhancements in Sage Intacct from the 2022 Release 2.

May 18, 2022 | 01:00 PM - 02:00 PM PT
Foundation Cuts Manual Data Entry, Empowers Staff With Robotic Process Automation
Case Study
RPA tech enables lasting growth, boosts savings and frees up staff to redirect their time to meaningful strategies.

May 18, 2022