Open Navigation

CSV Editor

A connector for manipulating CSV files (formerly CSV Processor)

Overview

Please note: The CSV Reader and CSV Editor cannot be used interchangeably. A CSV instance created in the CSV Reader is not compatible with an instance created in the CSV Editor and vice versa.

For example, a CSV instance ID created in the Editor is not valid if used in the Export CSV operation in the Reader

The CSV Editor (formerly known as the CSV Processor) connector allows you to create or import CSV files and process them in a number of different ways such as adding, removing, formatting, sorting etc.

When you create a CSV using the CSV Editor it will only be available to modify during the current workflow run. If you want to use it across workflow runs, you need to export it to a persistent file at the end so it can be re-imported on the next run. The reason for this is that it allows for much larger CSV files to be created/processed using the connector than would normally be possible.

Note on major version compatibility

Please note that versions 2.x and 3.x of the CSV Editor are incompatible and cannot be used in the same workflow.

If you want to start using version 3.x in a workflow which has previously used 2.x, all CSV Editor steps will need set to 3.x. This will mean checking and testing the functionality of all steps which were previously set at 2.x.

From this point it should be that every time the workflow is triggered, all CSV Editor steps will be creating only 3.x-compatible output.

Importing CSV Files into a Tray.io workflow

This page will explain how a CSV file is created in the workflow and how a file can be imported from Google Drive. For more use cases on how a CSV file can be imported (via an email or transferred with SFTP) please visit Managing CSV files

Examples

Basic use of the CSV Editor

Step 1 - Create a CSV instance

The first step when using the CSV Editor is to create a new CSV or import an existing CSV for manipulation within the workflow. In this example we will create a CSV instance using the CSV editor. The Create CSV operation allows you to create a CSV with a certain number (and type) of column and some default blank rows.

Here we create a CSV with three columns and five blank rows. We give the columns a Name and Data Type by using the Add Item button.

Create CSV

Create CSV

Step 2 - Manipulate the CSV instance

The Create CSV operation will return a unique ID which needs to be used in all other steps in which we want to manipulate the CSV data. Make sure that each subsequent CSV step you add to your workflow is connected up to the ID of the first Create CSV step.

First we add another CSV Editor step to the workflow and set its operation to Get Row Count (or any other data manipulation operation available to the CSV Editor). We then set its CSV ID parameter to the ID found in the output of the previous step by using the Connector Snake. Finally we can see the JSON path reflected in its CSV ID parameter.

Drag input to connector output

Drag the Connector Snake from the CSV ID to the 'Create CSV' step

Choose output

Select id from the Output Schema

JSON path

The CSV ID field will automatically be set to the correct JSON path

Create a CSV from an existing file

The default operation in the CSV Editor is the Create CSV From File which allows you to create a temporary CSV from an existing CSV File. The CSV Editor will require the URL of the file in order to bring it into the workflow.

In this example we will get an existing CSV file from Google Drive. Before you start this walk-through make sure you have a CSV file in your Google Drive which you can use.

Step 1 - Get the file ID from Google Drive

In order to get the URL of the file we will first need the ID of the file we are trying to bring into the workflow. We get the file ID by using the Google Drive connector.

Add the Google Drive Connector to the workflow and setup your authentication (see the Google Drive docs for details on how to do this). Leave the operation as the default List files operation. Set the Filter by Folder drop down to your target folder and set Filter by Name to your file name.

Get file ID

Use the Google Drive connector to get the file ID

Step 2 - Use the file ID to download the file

Now that we have the file ID we can use it with the Download file operation from the Google Drive connector to download the file.

First, we add another Google Drive connector to our workflow, give it the appropriate authentication and set the operation to Download file. Next we use the connector snake to set the File ID to the ID from the previous step. You should now see the JSON path of your file reflected in the File ID field, you'll see the field in our example set to $.steps.drive-1.files[0].id. If we run the workflow the output from the Download file step will provide a URL to our downloaded file.

Drag to previous step

Use the Connector Snake to get the File ID from the 'Get file ID from Drive' step

Select ID from drop down

Select id from the Output Schema

File ID JSON path set

The File ID field will now reflect the correct JSON path

Step 3 - Create a temporary CSV in your workflow

You can now use the CSV Editor connector to bring our CSV file into the workflow.

Add the CSV Editor step to your workflow and keep it's operation as it's default, Create CSV From File operation. Similar to Step 2 use the connector snake to set the File URL field to the URL output from our Download File step. You should now see the JSON path to the URL reflected in the File URL field. Depending on the formatting of your CSV file you may need to change the other input settings; details of these settings can be found in the Full Operations Reference at the bottom of this page.

If you now run the workflow your CSV file will be available to be manipulated in the output of the CSV Editor step.

Create temporary CSV

Use the CSV Editor to create a temporary CSV

Persist a CSV across workflows

In the previous examples have covered how to create a CSV from scratch and how to import a pre-existing CSV to manipulate within the workflow, however, the CSV that you manipulate will only be available to that run of the workflow. In order to make the file persistent we will need to export it so it can be re-imported for future use.

In this walk-through we will use our Basic Use of the CSV Editor example in which we create a new CSV.

Step 1 - Export the CSV

Add another CSV Editor step to the Basic Use of the CSV Editor example created previously and set it's operation to Export CSV. Using the connector snake from the CSV ID input parameter select the Create CSV Step and then select the ID parameter from the dropdown. You will then see the JSON path of the file ID reflected in the CSV ID input parameter $.steps.csv-1.id. The CSV in the example includes a header to define the column names so we keep the Include Header boolean checked. Finally we give the file a name, in this case we call it TEST_DATA.

If you run the workflow and check the output of the Export CSV step you will see the persisted CSV file object.

User the connector snake to get the CSV ID

Use the Connector Snake to get the CSV ID from the 'Create CSV' step

Set the required fields of the Export CSV step

Complete the step by giving your file a name

Step 2 - Import the CSV into a new workflow

First create a new workflow with a Callable Trigger. Now return to the workflow in which we're exporting the CSV and add a Call Workflow step. Keep the default operation as Fire and forget, choose our target workflow from the Workflow dropdown, and set the Data field to the output of our Export CSV step $.steps.csv-3.file.

Create new workflow with Callable Trigger

Create a new workflow with a Callable Trigger

Add a call workflow step

Return to the 'Export CSV' workflow and add a Call Workflow step with these parameters

Step 3 - Does the output match the file we exported?

Run the workflow in which you're exporting the CSV and switch to the import CSV workflow. If you click on the Debug tab you will see a successful run with the output of the Callable Trigger step equal to the CSV file object we created in the previous workflow.

{
"name": "TEST_DATA.csv",
"url": "https://tray-csv-file-processing.s3.xxxxxxxx",
"mime_type": "text/csv",
"expires": 1572956515
}

Parse from raw CSV text

In order to handle raw CSV text the CSV Editor has a Parse Text operation which will convert raw text into a list of rows.

Step 1 - Simulate the use of raw CSV text in our workflow

To simulate the use of raw CSV text in a workflow this example will use the Script connector in conjunction with the following mock data:

1,Malinde,Monier,mmonier0@netvibes.com,Female
2,Fiann,Ayling,fayling1@i2i.jp,Female
3,Jasmine,Schulze,jschulze2@sourceforge.net,Female
4,Clerkclaude,Tedman,ctedman3@ox.ac.uk,Male
5,Tina,Asey,tasey4@thetimes.co.uk,Female

Drop the Script connector into the workflow and set the variable to the mock data. Running the workflow now will return the mock data.

Set our mock data to the value of the script connector's value

Copy and paste the mock data into the Value field of the 'Raw CSV text' step

Step 2 - Parse the data into a list of rows

Drag a CSV Editor step into the workflow and set the operation to Parse Text. Set the Delimiter attribute to reflect the data you're using, in this instance our text is delimited by a comma. Running the workflow will parse the data and return a list of rows. (For a guide on how to grab output from previous steps see the Basic use of the CSV Editor walk-through)

Parse the raw data with the CSV Editor

Use the CSV Editor to parse the output of the 'Raw CSV text' step

Update rows by filter

The CSV Editor has operations which will allow you to apply a filter to find specific rows and to then modify the rows that are returned. In this example we will search for all rows where first_name is equal to tina and then change the email address tina@test.com where this returns true.

This example will use the workflow from the 'Create a CSV from an existing file' walk-through.

Step 1 - Update the email parameter

Add another CSV Editor connector into the workflow. Set the operation to Update Rows by Filter and use the connector snake to link the CSV ID input to the ID output from the Create temporary CSV step. Now add a property to the Cell Values attribute, call it email and set the value to tina@test.com.

Set the email cell value to tina@test.com

Add a new CSV Editor step and set the CSV ID and email fields

Step 2 - Filter for rows where first_name is equal to Tina

Add a Filter with Column Name set to first_name and the value to tina, the Operator can be left as its default =. Running the workflow now will update the target cells within the filtered rows. If the run is successful an object will be given in the output providing details of whether or not the run was a success and how many rows were updated.

Set the filter

In the 'Filter & update' step set the 'Filters' parameters

Was this article helpful?
Yes
No