Import and Export to Excel and CRM 2011

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:

Post to Twitter Post to Yahoo Buzz Post to Delicious Post to Digg Post to Facebook Post to MySpace Post to Post to Reddit Post to StumbleUpon

Comments are closed.