Michael Shaheen

How to import a .csv via SFTP to create records in Sugar

Blog Post created by Michael Shaheen Employee on Jul 20, 2020

A consistent challenge for integration developers is managing flat file integrations with systems where direct access is not natively provided by existing functionality or permitted for security reasons. In this guide, you'll see how to take an existing CSV file in an SFTP location and upload/stream its content to Sugar products — a necessary step before you can quickly transform the contained data with a common resource transformation.

 

Overview

In this example, we'll do the following:

  1. Take a .csv file from an SFTP location (using the SFTP adapter)
    1. This file will contain rows of records made up of appropriate Sugar Integrate field names (for Account records: billing_address_street, industry, email1, etc). Any fields included in the CSV that are not in Sugar or not in the Sugar record type that you are creating will be skipped and not imported.
    2. Here is a CSV with sample data or you could use a data generation utility like mockaroo.com to generate your own. Unzip the file before uploading to your SFTP server - you only need the .csv file.
  2. Upload file data to a CRM system (Sugar Sell)
    1. The data will be parsed so that a record can be created for each line in the csv file. These data will be mapped appropriately in Sugar Sell. When completed, you should have new records in Sugar Sell in the module that you specified.

Although this tutorial specifically utilizes a source SFTP location and uploads to Sugar Sell, both the source resources and endpoint can be changed or extended to suit your use case. For example, you could select top-level sources from nearly any bulk-supported endpoint and upload the resulting downloaded data to any other endpoint that can take in data from a CSV file.

 

Prerequisites

In order to follow along with and perform this use case, you'll need the following:

  • Sugar Integrate account (request a trial account here)
  • Sugar Sell admin account (Sugar Enterprise or Serve will work also)
  • Configured SFTP server and valid credentials
  • Authenticated instances of the SFTP and Sugar Sell adapters
  • A csv file with data to be imported into Sugar Sell on your SFTP server. Remember to actually upload the file to your SFTP server before trying this process! Here is a csv with sample data of Accounts
  • The JSON configuration file of the sample procedure we created for this article

 

Creating and Configuring the Procedure Instances

With Sugar Integrate, you can create procedures from scratch, use existing procedures as a starting point for new ones, or import JSON files of an existing procedure. Here, we'll import the JSON you downloaded above and configure the instances to work properly with each other.

 

Importing JSON to Create the Procedures

Your existing procedures are available from the Procedures tab on the left-hand side of our platform's interface. Click Build New Procedure and then select Import to upload the JSON for the procedure we're going to use (see our docs for more details about importing JSON).

 

 

After importing the JSON for the procedure, it's time to create the procedure instances.

 

Creating the Procedure Instances

Remember to configure your SFTP and Sugar Sell adapter instances before starting this step. Once you have those instances, navigate to the Procedure catalog, hover over the procedure, and click Create Instance.

 

 

On the Instance Creation page:

  1. Enter a name for the procedure instance. Here, we use "Instance 1".
  2. Click the plus sign under targetInstance and select your Sugar Sell adapter instance.
  3. Click the plus sign under sourceInstance and select your SFTP adapter instance.

It should look something like this:

 

 

After completing the configuration, click Create Instance.

 

Triggering the Procedure

Now that the procedure instance is created and configured, we'll manually trigger the procedure. We will do this in the Edit screen for the procedure. Navigate to the Procedure catalog, hover over the procedure, and click Open.

 

 

On the console that opens, click Try It Out and then click Select Instance.

 

Select Instance 1 (or the instance with the name you set up above).

 

Now click Select Trigger on the console.

 

In the Chooser window, specify the input JSON to use as the trigger to kick off the procedure. Your trigger should contain the path to the .CSV file you want to upload to the CRM system and the destination object as shown below:

 

The pathToFile field should be relative to the root of your SFTP server (ex: "/home/Public/test-docs/MockData-Accounts.csv"). The destinationObject is a string that represents the module type you would like to import this data into. Each CRM will have a different set of possible destination objects. So how do you know what value to put for this parameter? I suggest using the API docs for the CRM adapter instance. You can invoke the GET /objects endpoint to retrieve a list of all possible object types (and their spelling and capitalizations) from the response. Remember to select your instance from the left side list so that you may use the Try It Out button. 

 

Since we are looking to create Account records from our data, I see from the response of the GET /objects call that we need to set destinantionObject equal to "accounts" (all lowercase and plural). Now that the input JSON for our trigger is all set, all that's left is to click Run.

 

Verifying the Procedure Execution

After giving the procedure some time to complete, we can view details of the procedure executions to ensure they were successfully completed.

Navigate to the Procedures instances page, hover over and click Executions.

 

In the Procedure Executions column, click your procedure execution to view its steps.

 

Here, we see all three steps of the procedure were successful. You can also click on an individual step to see more details. Once the procedure is executed, the data has been uploaded to Sugar Sell. Log in to Sugar Sell to see your newly created records.

 

That's it! The process is complete. 

 

So, what did this procedure actually do?

As you can see, the procedure only has 3 steps: The trigger, a Javascript step that sets up values for the final step which is a File Stream step. That last one is where the magic happens. A File Stream step essentially does a download from one platform and an upload to another. In the first step called generateMetadata, we are simply defining values to be used in the Stream File step. Here's a bulleted list of the parameters used in the Stream File step, what they mean, and how they are defined:

  • Download Adapter Instance Variable
    • This is the Adapter Instance that you have authenticated for the source data. In our case, it should be an SFTP Adapter authenticated to your SFTP server where the CSV data file resides
    • Our procedure variable is called sourceInstance so we use the curly bracket notation to specify we are using a config variable called sourceInstance
  • Download Method
    • To grab the file from the SFTP server, we will invoke a GET endpoint
  • Download API
    • This is the path to the desired endpoint. Since we are looking to grab a file from the SFTP server, we are going to use the /files endpoint
  • Upload Adapter Instance Variable
    • Similarly to the Download Adapter Instance Variable, this is the config variable called targetInstance that we assigned when making the Procedure instance
    • We again need to use the curly bracket syntax
  • Upload Method
    • To send the file to the CRM, we will invoke a POST endpoint
  • Upload API
    • Which endpoint are we calling for the POST? In this example, we are going to call the bulk endpoint for the object type we are trying to create. In the Trigger Event JSON, we assigned pathToFile and destinationObject values to be used later. In order to use those values properly, we needed to create properly formatted parameters. In our generateMetadata procedure step, we grabbed the destinationObject and assigned it to a variable called objectName. So, our Upload API value should be /bulk/${steps.generateMetadata.objectName} which says grab the value of the objectName variable from the step called generateMetadata
  • Download Headers
    • This parameter is hidden by default. Click Show Advanced to reveal these additional parameters
    • We defined a downloadHeaders variable in the generateMetadata step that sets the acceptable mime type of files to download to 'text/csv' as a JSON object
  • Download Query
    • In the generateMetadata step, we also defined a variable called path that contains a JSON object with the value from the pathToFile value we defined in the trigger event JSON
    • We again need to use the curly bracket syntax
  • Upload Headers
    • We defined an uploadHeaders variable in the generateMetadata step that sets the acceptable content type to download to 'multipart/form-data' as a JSON object

 

I hope that helps you understand what it takes to grab a csv file from an SFTP server and send it to a CRM as individual records. Play around with it and see what improvements you can make. I'd love to hear about it in the comments!

Outcomes