Using Talend to Upload Data to SugarCRM (Part 1)
Feb 19, 2013 -
CRM systems are nothing without data. Our users utilize the data in a CRM system to understand and make decisions about their customers and it’s critical to provide these users with complete and accurate information. Whether migrating to a new CRM system or just adding data to an existing system, a data import can often present many challenges.
Like many CRM systems, SugarCRM provides users with tools to import data. Sugar allows users or administrators to import data into any data module by uploading CSV (comma separated value) files. Sugar then allows the user to map the datasource to the appropriate module fields and then loads the records into the database. I have used Sugar’s data import tool many times, and while it works very well, it does have a few limitations:
- Using Sugar’s built-in tools, we can import into almost all of the modules, however, one of the modules it excludes is E-mail Messages
- For custom data, we can only supply CSV files
- While many applications and database engines can output data to CSV, there are often problems with fields that contain free-form text like Notes fields
- SugarCRM has a limit on the maximum size of a CSV file which is easy to exceed when uploading activity history such as meetings and call logs
Don’t worry, all is not lost. While there are limitations in the SugarCRM import tool, there are other applications you can use to import data into SugarCRM that will negate the limitations outlined above. The good news is that some of them (including the one I will cover in the post) are free.
Talend is a company that creates middleware products for data integration. The tool we will focus on today is Talend Open Studio.
Talend Open Studio gives a CRM administrator new tools and choices for importing data into SugarCRM. It allows you to import into and export from any of SugarCRM’s modules – including the E-mail Messages module. It supports a wide variety of data formats such as AS400, Access, DB2, Firebird, JDBC, MS SQL, MySQL, PostgresSQL and XML files. Talend also supports connections to cloud based services such as AmazonRDS, Salesforce (useful when converting to SugarCRM!) and of course SugarCRM. Since Talend is not limited to CSV files, many of the challenges for creating valid CSV files with free form text fields in SugarCRM no longer apply. Talend doesn’t have a file/data source size limitation which makes it very suitable to large scale data jobs.
While Talend removes many limitations of the SugarCRM Import tool, it does add two that should be considered carefully.
Sugar’s built-in tool will actually warn us (or not import) data that have missing required values. Talend will not do this. It will import all records and write the values to SugarCRM. If there is missing data or invalid drop-down supplied for a particular field, the data will still be imported but will blank in SugarCRM. With great power comes great responsibility! Make sure you review your data sources carefully before proceeding.
Where Do I Get Talend?
You can download Talend Open Studio (again, for free) from Talend’s website. The direct link to the download page is: http://www.talend.com/download/data-integration
At the time this post is being written, the most recent version is v5.2.1. While there are other releases, this is the release that I recommend and currently actively use. Note: I’ll take a moment here to pass along some advice. If you end up using Talend for data integration, I recommend against using new versions in production without testing them first; there was a prior build that broke connectivity to a SQL Server which caused me a few headaches. I’d hate to see the same thing happen to anyone else.
Talend works in Windows, OS X, and Linux. As a result, they offer two different installation packages. There is a package for all three platforms (a zip file) and a Windows-only package (an exe file).
The key difference is that the multi-platform package will need to be extracted and installed manually while the Windows-only package will extract the files to a location of your choosing. I’ve used Talend on all three platforms, but this post will focus on the Windows-only package.
From a usage standpoint, Talend works the same way regardless of the platform. So much so, that you can transfer your projects to another platform. This means that if you start in the Windows world and end up in Linux or a Mac, you won’t have to recreate all of your projects. CAUTION: Depending on how you configure things in your Projects, moving them between platforms might require a little extra work. After moving any job to a different OS, I always test the Project before moving it into production.
Once you’ve downloaded Talend, go ahead and install it. The installer will ask for a path where you will install the files. I recommend installing to a location other than Program Files on Windows. On my system, I have Talend installed to C:\Apps\Talend.
Once you click install, the Talend files will be copied to the location you specify. This will take some time depending on the speed of your system.
Once the installation is completed, you will find a shortcut for Talend in your Start Menu. One thing Talend does not do well is name the shortcut to something that makes sense to human eyes. When you click on your ‘Start Menu’ (or Start Screen for Windows 8 Users), you will be able to find it under ‘TOS_DI-Win32-R95165-V5.2.1’.
OK, so that covers downloading and installing Talend. I know, it seems inconsiderate to stop now, and I’m sorry. Part 2 of this topic covers quite a bit of ground and my editors wouldn’t let me put it all into one post. But don’t worry, when Part 2 is published we will dive into creating our first project and then uploading data into SugarCRM!
In the next blog entry in this series we will review how connect Talend to SugarCRM and upload data.