November 3, 2011 § Leave a comment
Using a Data Loader to migrate data from one Org to another is not that quite simple when there is a Master-detail relationship to be taken care of and maintaining the same relationship in the destination Org. I could not for the life of me remember the steps to follow whenever I am required to use Data Loader is such scenarios. And so I happen to write it down somewhere in a scrap paper and now as I have a running blog to share, I can post this and get a look every time I am going to use it in future and hopefully many will find it useful coming across this post.
Pre-requirement before following the below steps:
- You should have one csv file exported from the source Org which has all field values of the Master Object to be migrated to the destination Org.
- You should have a second csv file exported from the source Org which has all field values of the Detail Object along with the lookup ID field to the Master Object.
So here are the steps:
- Create an External ID field in the Master Object of destination Org
- Import the Master Object into destination Org with the ID field values(Source Org) inserted into the External ID field of destination org.
- Export the Master Object from the destination Org along with the new ID field and the External ID field values as a CSV file.
- Convert the ID values of both the master Object (in the csv file exported from Destination Org) and the detail Object (already exported from the source org) into a string of length 15 and then convert them with Tim’s formula to get the last 6 values of the each string in digits.
- converting into string of 15 : =LEFT(A2,15)
- Tim’s Formula : A3&CODE(MID(A3,13,1))&CODE(MID(A3,14,1))&CODE(MID(A3,15,1))
- Convert any DATE fields with this formula before inserting into the destination Org
- TEXT(A2,”yyyy-mm-ddThh:mm:ss”)&”.000Z” or select the date column and select ‘format’->’custom’ and type = yyyy-mm-ddThh:MM:ss:sssZ and press OK
- Use VLOOKUP on the detail Object CSV file to match with the values in the Master Object CSV file
- vlookup formula : VLOOKUP(value, table_array, match_value, case_sensitive or not(Boolean))
- where value is the column in detail Object csv file to be searched or matched in Master Object csv file
- and table_array is the fields(e.g. A1 to D1 till the last row) to be selected for matching in the Master Object csv file
- and match_value is the no of the column next(usually 2) to the matched column(usually 1) in the Master Object csv file.
- case_sensitive is the value to be given in VLOOKUP as a Boolean value either TRUE or FALSE to match for case-sensitivity.
In order to give these steps a graphical view, I have also created a diagram that can well explain what I am trying to convey in a simple way.
In the diagram above, I have taken a simple example of an Account (Master Object) and Contact Object (Detail Object).
Feel free to comment and make this post more useful. I might not be clear in some of the steps above and it can be simplified more for beginners to understand.