Blog

talend

Export any data of SugarCRM with Talend Open Studio for Data Integration

The problematic

You have SugarCRM as a CRM and you wish for example, export only some users’ information (lastname, firstname and email) to import them to your favorite/shiny newsletter system. By using the export function of SugarCRM, you get a CSV file with all users’ fields and which could be theoretically imported into Excel to remove the columns that you don’t need for your newsletter system importation. Unfortunately, a user field “description” has line breaks which prevent a clean import into Excel because each lines is recognized as a new row/entry which is wrong.

Solution

Use Talend Open Studio for Data Integration. A free ETL (Extract Tool) provided by Talend and which will help you to extract exactly the source data wished and export it to any target (example: csv file, database, other web service, etc).

It’s an eclipse-like which works with a Job Designer. You create a job, add input component (file, database, webservice, else), output component (files, database, webservice or else) and other interactions (data filtering, data sorting, etc).

Talend applied to our SugarCRM Scenario

The procedure is quite easy to do and accessible to non-developers (see screenshots below):

  1. Download Talend Open Studio for Data Integration if not already done.
  2. Open it
  3. Create a new project
  4. Create a new job and give a name
  5. On the Palette side panel, select Business > SugarCRM > tSugarCRMInput as Input component and drag’n drop it into the main window (Job Designer)
  6. Double click on the newly created component, it should display in the Component Panel at the bottom of the software the Basic Settings
  7. In the Basic Settings, provide the SOAP URL of your SugarCRM installation. Exple: http://www.mydomain.com/crm/soap.php
  8. Fill in the Username/Password (administrator account)
  9. Important: let all values between double quote ” as it is provided in the default value, otherwise errors occur while running the job
  10. Choose which SugarCRM data module to export. Exple: Contacts
  11. You can take a look of the schema by clicking on the button “Edit Schema ‘…’“. You will see a list of all predefined fields recognized by this component. You can edit it but you don’t really need it. The output component will do the job for you
  12. You can also set a Query Condition if you want only a set of result. Exple: “lastname=’smith’”
  13. On the Palette side panel, select File > Output > tFileOutputDelimited as Output component and drag’n drop it into the main window (Job Designer)
  14. Right-click on the component tSugarCRMInput and choose the menu “Row > Main” and move the line until the tFileOutputDelimited component. You linked both components.
  15. Double click on the newly created component, it should display in the Component Panel at the bottom of the software the Basic Settings
  16. Provide the file where to output the result. In my case, the file must already exist otherwise the job doesn’t create it
  17. Fill in the options as you wish for the separator, break lines for the row, etc.
  18. If you wish only some columns/fields, click on the button “Edit Schema …“, a new window will appear with the schema of the input  on the left side and the schema of the output on the right side. The output schema should be similar to the one on the left side. Remove all unnecessary fields/columns. In our example, we juts keep “lastname, firstname and email
  19. You can also fill in the Advanced settings tab if you wish to set CSV options or delimited characters
  20. When you are finished, select the tab “Run” and click on the button “Run”
  21. You could get an error message that the schema are not the same. Don’t care, keep going.
  22. You should find the result of your exportation into the target file you defined.