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
SaaS Market Trends
Webinar
Between the uncertainty of 2022 and the highs of 2021, what will 2023 hold?

December 14, 2022 | 09:00 AM - 10:00 AM PT
Fraud: Current Trends & Hot Topics
Webinar
Don’t let fraud negatively impact your organization.

December 8, 2022 | 11:00 AM - 12:00 PM PT
Year-End Tax Planning for High-Net-Worth Individuals
Webinar
Our tax experts will dive deep into our annual year-end tax planning guide.

December 8, 2022 | 09:00 AM - 10:00 AM PT