With this recipe, you can build a chain to extract data from a Secure File Transfer Protocol (SFTP) server and then upload it to a Wdata table. With this chain, you can select whether to append the data or replace an existing dataset, such as one uploaded during the same time period.
Ingredients
To easily create the commands required for this chain, use these templates:
To use these templates, you'll need:
- A core Workiva connector
- A premium SFTP connector
- A core File Utilities connector
- A core JSON connector
- A core Tabular Transformation connector
Step 1. Create a chain to update table datasets
If you haven't already, create a chain from the Update datasets in a table template. At the end of this recipe, you'll use that chain in a Run chain event to upload the file downloaded from the SFTP server to a Wdata table.
Step 2. Determine the variable values
To enable customization, the templates contain variables. For when you run this chain, determine the values for these variables and runtime inputs:
| Variable | Value |
|---|---|
| CV SFTP file path | The path of the SFTP directory to download the file from, such as /path/to/data/*.csv |
| File prefix | The prefix to append to the file's name when uploaded to the table
Note: The chain uses this prefix to identify datasets to replace, based on the Replace frequency variable value. For example, to identify datasets from the SFTP server, enter a prefix of
|
| Table ID | The ID of the Wdata table to upload the file to
Note: In a table's URL, the ID appears after
|
| Replace frequency | Whether to append the file as a new dataset or replace another dataset that has the same prefix
Note: You can select to replace any dataset with the same prefix, or only one from the same day, month, or year. |
Step 3. Create a chain to download from an SFTP server
- In Chain Builder, create a chain from the Download file from SFTP server template from either Build or Templates.
Note: If this is your first chain in the workspace, create it from Templates.
- From Chains, click the Build icon and then Create Chain from Template. You can then select the Download file from SFTP server template and click Create from template.
- From Templates, open the Download file from SFTP server template, and click New chain.
- From Chains, click the Build icon and then Create Chain from Template. You can then select the Download file from SFTP server template and click Create from template.
- Enter a unique name to help identify the chain.
- Select the workspace and environment to use the chain, and click Next.
- Set up the CSV SFTP file path variable, and click Next:
- In Variable type, select Chain.
- In New variable name, enter
CV SFTP file path.
- Select the connector and runner to use with each connection, and click Submit.
- Click View your new chain.
Step 4. Set up the new chain
- From the new chain, click Chain Settings.
- For the CV SFTP file path variable, enter the path to the SFTP directory, and click Save.
- To test to the connection to the SFTP directory, click Publish, Execute, and Run chain, and verify the List File Contents command creates an output.
Step 5. Finish with a Run Chain event
Remember back in Step 1, when we mentioned you'd need a chain created from the Update datasets in a table template? Here's where it finally comes into play, used in a Run Chain event to upload the file downloaded from the SFTP server to your Wdata table:
- In the chain created from the Download file from SFTP server template, move Run chain to the canvas.
- Drag a link from List File Content to the Run chain event, and click Edit .
- Enter a unique name and description to help identify the event and the chain it runs.
- Under Chain to run, select the chain created from the Update datasets in a table template.
- Set the chain's runtime inputs, and click Save and Publish:
Runtime input Detail File prefix Enter the prefix to append to the datasets' filenames. For example, to identify files the chain downloads from the SFTP directory, enter sftp-.Table ID Enter the ID of the Wdata table to upload the file to. Note: In a table's URL, the ID appears after
/tables/.Dataset file Select the Downloaded file output from the Download file command. Replace frequency Select whether to append or replace new datasets in the table: - To append the file as a new dataset, select Add new dataset.
- To replace any dataset that has the prefix entered for File prefix, select Replace dataset.
- To replace a dataset that has the prefix only when uploaded during the same period, select Replace dataset for current day, ...current month, or ...current year.
- To run the chain and verify it updates the table correctly, click Execute and Run chain.
Note: From Monitor, you can track the progress of both this chain and the chain used in its Run chain event.
- To ensure the file imported successfully, open the table in Wdata, and verify the new dataset appears.