Accelerating data conversion
FTP2SF
Were really proud of FTP2SF at AppGenie, its a really powerful, simple to use and cost-effective integration platform that is designed to be used both at the API level and the UI, think of it like WinSCP or FileZilla, in Salesforce.
You can download FTPSF from the AppExchange here feel free to try it out and let us know what you think!
One of the questions that our users and customers often ask is "how do I process the data returned" and use it in my Apex classes. The traditional way to do this has been to process the Blob by converting to a string and then processing in the following manners:
-
String split:
Using the lowest common denominator string.split based on \r\n and \t or , -
JSON Serialiser:
Assuming your content is Json formatted processing the string using JSON deserialize -
Custom parsing class :
This option probably gives the most flexibility but its more code!
So what is the best way to read or write data using FTP2SF and Salesforce?
Welcome to DataWeave on Salesforce!
DataWeave is a powerful data transformation language used in MuleSoft's Mule runtime engine products. Its been available for many years within Mulesoft and if you are using or have used MuleSoft's integration engine you will already be familiar with DataWeave and its syntax.
DataWeave is a programming "language" technology that allows for seamless integration and data manipulation including the efficient transform data between various formats, such as XML, JSON, and CSV.
DataWeave in Salesforce is now available as an open beta, details are here https://developer.salesforce.com/docs/atlas.en-us.apexcode.meta/apexcode/DataWeaveInApex.htm and its better than the invention of sliced bread and toast. There's a great free resource for learning how DataWeave works here https://dataweave.mulesoft.com/learn/tutorial/1-Introduction if you want to increase your knowledge, but working on the simple process of converting from one format to another is very easy.
DataWeave operates on the concept of input and output payload, where software product developers define transformation logic using declarative syntax and built-in functions.
The language provides a rich set of operations and functions required for data manipulation, filtering, mapping, and aggregation, allowing for support for complex transformations with ease.
If you'd like some examples of how this works go use the DataWeave tutorial. This post is not focussed on the DataWeave conversion process or instruction set - it's more about showing you how to use it!
Im going to be leveraging the examples that Salesforce have published so that its obvious whats happening and you can easily tie this back to the documentation - the end result is going to be the same though, we are going to use DataWeave to transform the blob data we are receiving from FTP2SF from a FTPS server and then converting this from csv to a collection of business contacts.
The data on the server is stored as a csv file and looks like the below, the actual file is available here https://github.com/developerforce/DataWeaveInApex/blob/main/force-app/main/default/staticresources/contacts.csv
FirstName , LastName , Email , etc .. /r/n
We want this converted into a collection of Contact records that we can then upsert into our Contact object.
Converting our data.
Firstly we need to create our DataWeave script - the example is available here https://github.com/developerforce/DataWeaveInApex/blob/main/force-app/main/default/dw/csvToContacts.dwl but repeated below.
%dw 2.0
input records application/csv
output application/apex
---
records map(record) -> {
FirstName: record.first_name,
LastName: record.last_name,
Email: record.email
} as Object {class: "Contact"}
From the above its pretty easy to read but heres what we are doing:
-
input records application/csv
Input is csv and assigned to the records variable -
output application/apex
Output type is an apex sObject -
records map(record) -> {}
We map the records variable in a lambda function, executing the process for each item in the records collection -
FirstName: record.first_name, ....
We assign the record value FirstName to the sObject value first_name -
as Object {class: "Contact"}
The magic happens with the record assigned as a type of Contact
How do we use it?
The DataWeave script needs to be deployed to Salesforce, just like any other metaData object, to do this you will need to include this into a deployment package under the dw directory, including the matching xml file, making sure the minimum version is 56.0
<?xml version="1.0" encoding="UTF-8"?>
<Package xmlns="http://soap.sforce.com/2006/04/metadata">
<types>
<members>csvToContacts</members>
<name>DataWeave</name>
</types>
<version>56.0</version>
</Package>
Once your DataWeave script is on Salesforce, the next step is calling the script. To make sure everything works as expected you can confirm this is working by executing it from the debug console.
string data = 'first_name,last_name,email\r\nBob,Bobbington,bob@bobbington.com\r\n';
DataWeave.Script script = new DataWeaveScriptResource.csvToCustomObject();
DataWeave.Result dwresult = script.execute(new Map{'records' => data});
List results = (List)dwresult.getValue();
system.debug(results);
Okay so from there we need to pull the data from the FTPS server and do the same this is where our AppExchange tool FTP2SF comes into the mix!
Blob content = FTP2SF.FTP.Get('TestServer', contacts.csv);
DataWeave.Script script = new DataWeaveScriptResource.csvToCustomObject();
DataWeave.Result dwresult = script.execute(new Map{'records' => content.toString()});
List results = (List)dwresult.getValue();
database.upsert(results);
Viola! How easy is that! Your new contact import function in 5 lines of code!
Let me at it!
So, how do you go about using DataWeave and FTP2SF, first step download the DataWeave 2.0 VS Extension from here https://marketplace.visualstudio.com/items?itemName=MuleSoftInc.dataweave
Then signup for your FTP2SF account here https://appexchange.salesforce.com/appxListingDetail?listingId=a0N3A00000FnBMFUA3
As always thanks for reading and reach out if you need any help, always happy to hear how people use FTP2SF.