Data Management Part 3: Cleaning Up Export for Re-Import Issues

As mentioned in Part 1: Bulk Edit and Part 2: Export for Re-Import, this material is based off my presentation done on CRMUG: The Advanced Basics: Where DO I Go Next as a CRM Admin? In that session, we covered a few topics including out of the box data management techniques. I wanted to dedicate a few blog posts to these topics to help more new CRM administrators!

Even when you are very careful there are always times that you make mistakes. Mistakes are okay as long as you know how to fix them (better if you can fix them before your boss finds out!). So here is some guidance on dealing with issues with Export for Re-import.

Failures on Import

A good kind of problem with Export for Re-Import is import failures. This just means some of your items may not have imported. This generally happens for two reasons:

1. The data was modified since you exported it. If the record changed since your export you cannot import it as you might overwrite data. To resolve, simply do a new export and update that row.
2. Lookup could not be resolved. If one of the fields you are updating is a lookup field there is a possibility that it couldn't find the record you wanted to link to or there were duplicates. With Export for Re-Import you cannot control how it searches for the correct lookup (different from a import of new records where you build the data map). It will always just look at the primary key and name. 

You can review these failures from opening the Import job and navigating to the Failures area. There is also a button here that allows you to Download Error Rows. This will give you a list of the full data row that failed so you can review and determine the best way to resolve.

Wrong Data Imported

The bad kind of problem with Export for Re-Import is when the wrong data is imported. This could mean you did some sorting and somehow misaligned the hidden GUID column and the data. Or this could even be because data was removed from the file that shouldn't have been.

I recommend the following tips when doing your export to prepare for failure:

• Eliminate unnecessary columns from your export. Then if a mistake happens you have less data to clean up.
• Eliminate unnecessary rows from your export. If possible, only export the items you expect to change.
• Save copies of your file along the way. I save a version when I export, after I do some work, and immediately before import. This allows you to have the data to fix your mistakes or see where it went wrong.

So let's say you do an export and you realize that the data imported was incorrect. You do not want to restore your database so you want to resolve with a new import. Here are the steps I would follow.

1. Start with a few deep breaths, no time for tears (flexible depending on amount of data problems)
2. Export the data that was modified with the same columns changed (remember you will be the Modified By user). We will call this NEW Export
1. Save a backup copy of this as well! Then if you get confused somewhere in this process you can quickly jump back to square one
3. Open the "Export" version you saved for the initial change. We will call this OLD Export
4. In both files expose the 3 hidden columns (A-C). A is the GUID of the record. We are going to use this to match data between the two sheets
5. For each column where data was changed, create a new column in NEW Export and do a vLookup using the GUID to get the data from the OLD Export
1. I recommend doing your vLookup in a fresh column. This will be a bit easier to manipulate (without the enforced data validation) and keeps your work separate
2. Again this is a reason to keep your columns to a minimum. You only want to do this for a few columns or it becomes hard to manage
3. Your vLookup will look something like =vLookup([NEW Export top GUID]A2,[Old Export GUID Column]A:A, [Column you want to return]4, FALSE]. The FALSE is because you want an exact match on GUID
6. Once your vLookup is complete for all columns, copy the data and Paste As Values into the correct place in the worksheet. Be careful if you have any rows that should not be changed!
1. Save this version as your Work in Progress version
7. Delete the additional columns you created. Save this version as your Import version.
8. Import this new version of the file
9. More deep breaths while it processes
10. Review the results and check for failures

It's as simple as that! Moral of the story - be very careful! Double check your changes and check the GUID you change is the correct GUID of the record you want to change before doing the import the first time.

Any better methods for cleaning up import mistakes? Any Export for Re-Import horror stories to share?