Skip to content

Hands-On Exercise – Export Prospects from SugarCRM

Overview

This exercise simulates the extraction of Prospects from the SugarCRM CRM, data transformation, and export to CSV files, followed by final upload to SFTP.
The goal is to understand how the SugarCRM connector works, manipulate data with Polars, and learn best practices for file naming and management in ConnectX.

Note

The same exercise can be performed with other CRMs supported by ConnectX (HubSpot, Salesforce, Zoho, etc.).

Objectives

  • Extract Prospect records from SugarCRM.
  • Apply data transformations (column renaming, numeric formatting).
  • Export the data to a CSV file with a timestamp in its name.
  • Automatically upload the file to an SFTP server.
  • Archive processed files into a backup directory.

Sources

  • SugarCRM → Accounts module
  • Required fields: id, name, phone, addresses, email, account_type, etc.
  • Only records with account_type = Prospect and date_modified >= last_start_date.

Destinations

  • CSV File named as:
    yyyy_mm_dd_hh_mm_[suffix_defined_in_config]
  • Saved in the directory defined in write_csv.prospects.dir.
  • Automatic upload to SFTP (sftp.prospects).
  • Local backup of processed files, deleting older files according to best practices.

Workflow

1. Extract

  • Read Prospect records from SugarCRM (Accounts module), filtering only those modified since the last job run.
  • Store the results in a DataFrame called read_sugar_prospects.

2. Transform

  • Prepare the final DataFrame load_sugar_prospects for export.

2.1 Optional Transform Data

  • Convert numeric values to use a comma as the decimal separator.
  • Rename columns to match the desired output schema or naming conventions.

3. Load

  • Generate a CSV file with a timestamp-based filename.
  • Export the data using connectx.write_csv.
  • If export succeeds:
  • Upload the file to SFTP.
  • Move processed files to a backup directory.

Recommendations

  • Use the SugarCRM connector: 
sugarcrm = SugarCRM(**etl.get("sugar"))
  • For file handling:
.rename()

.with_columns()
  • Per la gestione file:
connectx.write_csv()

SFTP.upload()

connectx.move_and_delete_files_backup()