Migrating data into Sugar with file attachments using Talend

bsoremsugar —  August 25, 2008 — 13 Comments

As a member of the Professional Services team here at Sugar, one of the services that is often requested of us is migrating a client’s data from their existing CRM into Sugar. In the past, our team would write custom migration scripts that read their data, performed some basic cleansing, transformed it into Sugar format, and then imported it into the database. This was a laborious task given the disparate data formats out there and varying levels of data quality–a task which none of us looked forward to doing.

Recently, however, some of us began experimenting with a tool called Open Studio, which is provided by one of Sugar’s technology partners, Talend. Open Studio is an open source data integration suite licensed under GPLv2 and provides a variety of tools for bridging the gap between disparate applications.

Using Talend Open Studio 2.4, the following are the steps that I followed to setup a migration of Note records from a Salesforce.com CSV file, including copying and renaming the associated attachments. Theser steps will work for Sugar Community Edition, Sugar Professional Edition and Sugar Enterprise Edition.

Note that the copy and rename step isn’t necessary for migrating SFDC Note data since their file attachments are already uniquely named with the record id (as it is in Sugar).  However this step highlights some of the additional functionality provided by Talend and is a useful exercise as other CRM systems don’t save their attachments with a corresponding GUID.

First download and install SugarCRM. Then download and install Talend Open Studio.  Next request a backup of your data from Salesforce.com.

Now open Talend Open Studio and follow these steps:

  1. Setup a tFileInputDelimited object that points to the SFDC Note.csv file
  2. Create a tFileList object that points to the directory that contains your attachment files (setting up the proper filters if more than the attachments exists within the directory)
  3. Create a tFileProperties object
  4. Right click the tFileList object and select Row->Iterate, and drag the line over to the tFileProperties object
  5. Click on the Component tab for the tFileProperties object and click in the File textbox. Press ctrl+space to bring up the contextual selection menu, and select the CURRENT_FILEPATH property for the tFileList object.
  6. Create a tMap object
  7. Right click the Notes tFileInputDelimited object and select Row->Main, and drag the line over to the tMap object
  8. Right click the tFileProperties object and select Row->Lookup, and drag the line over to the tMap object.
  9. Create a tFileCopy object
  10. Create a database output object (i.e. tMysqlOutput)
  11. Right click the tMap object and select Row->*New Output* (main), and drag the line over to the tFileCopy object. Give this output object a unique name (i.e. file_copy).
  12. Right click the tMap object and select Row->*New Output* (main), and drag the line over to the database output object (tMysqlOutput). Give this output object a unique name (i.e. notes)
  13. Double click the tMap object to bring up the map editor.
  14. On the left hand side of the map drag the column that contains the file name of the attachment down to the basename column on the tFileProperties schema. Select Inner Join.
  15. Drag the columns from the Notes schema on the left hand side over to the corresponding notes schema on the Right hand side. Make sure that your schema types are setup properly to avoid runtime errors. (i.e. date fields are set to Date, Boolean fields are set to Boolean, etc)
  16. Create a new column called new_file_name on the file_copy schema.
  17. Drag the column containing the id from the left hand side notes schema over to the new_file_name column on the file_copy schema
  18. Click OK to close the map editor.
  19. Click the Component tab for the tFileCopy object.
  20. Enter the CURRENT_FILEPATH property for the tFileList object into the File Name text box as you did for the tFileProperties object (step 5).
  21. Select your destination directory (for Sugar attachments, this is cache/uploads)
  22. Select the Rename checkbox and enter the new_file_name property for the file_copy object into the Destination filename textbox (for the above scenario it would be file_copy.new_file_name)
  23. Click the Run tab.
  24. Select the Statistics checkbox if you want to see where in the migration the job currently is, plus how fast the data is being migrated over.
  25. Click Run.

This should get you going to handle a basic migration of Notes data with copy and rename of the attachments. Hopefully, you will be able to take this and extend it to handle migration of data from any source into Sugar.

13 responses to Migrating data into Sugar with file attachments using Talend

  1. 

    Hey thanls for the article; A little outdate today but interesting.By the way do you know if we can do that using the SugarCRM SOAP layer and not going directly to the file system and the DB ?

  2. 

    Vince:It is possible to do this using Sugar's SOAP API. Since Talend supports interfacing with Sugar via SOAP, all you should have to do is change the output mechanisms from the instructions listed above to the SOAP interface. The SOAP methods that you would need to look at would be set_entry() to create the Note record, and set_note_attachment() to handle uploading the attachment.

  3. 

    Anyone used this process for migrating from FileMaker into Sugar?

  4. 

    You should grab new version of Talend component, supports latest API and has many additional features: http://cloudbee.com/sugar-crm-talend-component/

Trackbacks and Pingbacks:

  1. CRM Outsiders » Blog Archive » Cross Marketing (the Blogger Way) and SugarCRM Integration - December 3, 2008

    […] Blog and saw an interesting post from one of our own, Brian Kilgore. Brian is a bright guy and just posted about using Talend to integrate CRM data more […]

  2. GPS Humano » Blog Archive » Talend and SugarCRM - June 2, 2009

    […] bugs with Eclipse, though. I was introduced to Talend with an example from Brian Kilgore about Migrating data into Sugar with file attachments using Talend without reading any documentation at all and would like to share some thoughts with […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s