How to migrate attachments from one Salesforce.com org to another

We were recently asked to split a client’s Salesforce.com Org into three separate Orgs and migrate/improve the associated business processes and data into the appropriate new Org. When it came time to migrate the attachments, we ran into some obstacles not covered by the basic Salesforce Online Help and Training catalog. The challenges we faced were based around the number of attachments stored in the Org (over 1.3 million attachments) and the size of the attachments (over half of the attachments were larger than 2MB and ranged in sized to up to just over 4 MB). The first step in the attachment migration process is to export the attachments. Salesforce recommends using the Data Export feature in Salesforce Data Management Settings to do this. When the Data Export was scheduled, the Export Attachments checkbox was selected as directed. Due to the amount of data in the original Salesforce Org, the export took four days and exported 1442 generically named CSV files.  This tsunami of data was not optimal to work with so we looked for an alternative way to export only the data we needed. We found the best way to filter the attachment object to only the attachments we needed was to use a free AppExchange tool called FileExporter.  Since there are only a few fields to use as a possible filter in the attachment object, we chose to filter by OwnerID. Then we created a “MasterUpload.csv” using the files exported using the FileExporter app.

Using FileExporter

 We downloaded FileExporter and followed the instructions. In order to filter on OwnerID we edited the “beans.xml” document to read:

"SELECT Id, Body FROM Attachment WHERE OwnerId = '005700000018ano' ORDER BY Id"

"SELECT Id, Name FROM Attachment WHERE OwnerId = '005700000018ano' ORDER BY Id"

"SELECT Id, ParentID FROM Attachment WHERE OwnerId = '005700000018ano' ORDER BY Id"

FileExporter exports three files, Attachment.Body, Attachment.Name, and Attachment.ParentID, into a subfolder named “CSV” and exports all the attachments in their distinct format into another subfolder named “Exported Attachments”.

Note:  In the New Org we had added a field called OldSalesforceId to each object before we migrated the data. Then we migrated the original Record ID of each record into the OldSalesforceId field of the New Org along with the data. This is important because when the MasterUploadFinal.csv is mapped to the Attachment Object in the New Org, the MasterUploadFinal.csv ParentID must be mapped to the ParentID in the New Org using the OldSalesforceID as a cross-reference. This is explained below.

To create the “MasterUpload.csv” to be used to upload the attachments:

• We opened the Attachment.Name.csv and added a column for Path_Prefix and Body. We typed in the Path_Prefix column “C:\Program Files (x86)\salesforce.com\FileExporter\Exported Attachments\” . This is where our exported attachment files were located.

• In the Body column we concatenated the Path_Prefix,   the ParentID field, “#”, and the Name field. (When the attachments were exported they were named ParentID#Filename.xls for example. We concatenated all of this so that the file name specified in the Body field was the full file name of the attachments as they existed on our computer. The Body of each record ended up looking something like this:

C:\Program Files (x86)\salesforce.com\FileExporter\ExportedAttachments\00P70000006zsxEEAQ#Numbers By City.xls

• The last step in creating the MasterUpLoad.csv was to associate the OldSalesforceID with the New ParentID in the New Org. To do this we exported all the RecordIDs and associated OldSalesforceIDs from the New Org for the Objects we wanted to Upload the Attachments into. In this case it was just Accounts and Opportunities. We imported the New Account and Opportunity IDs, OldSalesforceIds and the MasterUpload.csv into Access. Then we created a query to match the MasterUpLoad.ParentId to the NewOrg.OldSalesforceID in order to get the NewOrg.ID. We exported this query information to MasterUploadFinal.csv.

We found DataLoader the best tool to use to insert the attachments into the new Org. To upload attachments using DataLoader three fields are required:

• ParentId - the Salesforce.com ID of the parent record.

• Name - the name of the attachment file, such as myattachment.jpg.

• Body - the absolute path to the attachment on your local drive.

Ensure that the values in the Body column contain the full file name of the attachments as they exist on your computer. For example, if an attachment named myattachment.jpg is located on your computer at C:\Export, Body must specify C:\Export\myattachment.jpg. Your CSV file might look like this:

ParentId,Name,Body 50030000000VDowAAG,attachment1.jpg,C:\Export\attachment1.gif 701300000000iNHAAY,attachment2.doc,C:\Export\files\attachment2

The CSV file may also include additional fields like OwnerId and Description. In this case we also uploaded the OwnerId. We proceeded with an insert operation, see "Inserting, Updating, or Deleting Data Using the Data Loader" in Salesforce.com Help and Training.  At the Select data objects step, we made sure to select the Show all Salesforce.com objects checkbox and the Attachment object name in the list. We mapped the MasterUploadFinal.NewOrgId to the ParentId in the Attachment object. All other fields were mapped as named. At last, the attachments were migrated from one org to the other.