Mass Upload Documents to Sage CRM

As part of migration from Salesforce to Sage CRM, I needed to upload a lot of documents (attachments) to Sage CRM. I am going to describe the process here.

The first task is to figure out how CRM stores the documents internally, so we can reproduce the process. The physical folder for the library can easily be found in CRM administration area.

Lets have a look at an example. I attached a couple of documents to customer Mr. Ronald Back. Here is how they look in CRM.

And here is how they look in the file system.

Notice there is a folder called Bargain Mart - San Diego too. That's because I uploaded some documents for that customer as well. Also, notice that Mr. Ronald Black is in a subfolder "M", and Bargain Mart is in a subfolder "B", so it looks like CRM groups the customers by the first letter of their company name.

Well, there is no documentation on how CRM structures the directory for the documents, and we cannot rely on assumptions. Fortunately, we don't have to. Let's have a look at how the attachments related information is stored in the database. I am intentionally limiting query results to make analysis easier. Here is the query.

select Libr_LibraryId, Libr_Type, Libr_Category, Libr_FilePath, Libr_FileName, Libr_Global

from Library

where Libr_Global = 'N'

And here are the results.

Notice the Libr_FilePath field. That's our salvation. CRM will handle any attachment as long as the Libr_FilePath and Libr_FileName point to a correct file, so it is not necessary to reproduce the directory structure that CRM generates for documents. Instead, all documents can be uploaded to the same, or a few custom subdirectories that are easier to manage. In my case, I called the folder "Salesforce Uploads".

The Library table also contains the links to companies, persons, opportunities, etc. related to the document. The field names are pretty self explanatory, i.e. Libr_CompanyId, Libr_PersonId, Libr_OpportunityID, etc. The document needs to be linked at least to a company, otherwise it won't be accessible in CRM. The only exceptions are global templates. Libs_Global field can be Y or N. The Y value means the document is a global template and does not have to be linked to a company.

As for the upload itself, I uploaded all attachments to the same folder in CRM, and inserted records into the Library table to point to the new documents. Salesforce provides a CSV export describing the links between documents and other entities, so I used that CSV file to help automatically generate the SQL scripts necessary to insert thousands of records.

Some additional hassle was in cleaning up the file names as there were duplicates, and since I was uploading all files into the same folder, I could not allow that. I cleaned the file names up by sorting them in Excel and writing a formula that would highlight the duplicates so they would be easier to find.

Hope you find this useful. For additional articles related to Sage CRM, take a look at the links below:

Migrating from Salesforce to Sage CRM

Improving Sage CRM Team Calendar