Data migration with case sensitive IDs (was: Data migration help please!)

[Hayli]

The easiest thing to do when migrating data from one system to another is to put in the keys in the new system. For all our data migrations (either from prod to empty sandbox or one system to another) we create an external key on each table, this key can that be referenced when you do an upsert via data loader.

V-lookup is another way, just a little more time consuming. I think everyone else had it right with v-lookup, you need to have the reference column in column A. 

If you want more help with creating the extra external keys on the tables, let me know.

Thanks,

Hayli

[JoyD]

Wow, I am happy to hear that the workaround I was extremely proud to figure out, is actually a recommended tool from SF.

Here is my code if anyone is interested (slightly different but works as well).  B2 would be whatever cell your SF ID is in.  In this case, I stick a pipe (|) between the numbers to separate them out so I am able to do a quick manual check to make sure things are accurate.  I also leave off the boring stuff at the beginning of the ID, just because it's always the same for my org - I imagine if you have a ginormous dataset, that you'd need to add a few more characters, this only does 6:

=CONCATENATE(CODE(RIGHT(B2,6)),"|",CODE(RIGHT(B2,5)),"|",CODE(RIGHT(B2,4)),"|",CODE(RIGHT(B2,3)),"|",CODE(RIGHT(B2,2)),"|",CODE(RIGHT(B2,1)))

Example:

New ID	ID
48|57|85|118|49|84	00650000009Uv1T
48|57|85|118|50|51	00650000009Uv23
48|57|85|117|115|109	00650000009Uusm
48|57|85|117|112|67	00650000009UupC
48|57|85|117|115|77	00650000009UusM
48|57|85|116|120|71	00650000009UtxG