tray-csv-processor-building-guide blog post cover image

The Tray CSV Editor: A Building Guide

Andrew Wiseman Tray.io author profile image
By Andrew Wiseman

Never worry about downloading, modifying, or uploading CSV files again

Have you ever had to manually manage flat files, such as comma-separated value (CSV) files? Good news - if you’re a Tray Platform user, your days of manually juggling, collating, and tracking down data in multiple CSV files are over. The Tray Platform’s CSV helpers let you automate the management of flat files containing all your important business data. These include the CSV Reader, which imports, exports, and queries large CSV datasets, and the CSV Editor, which lets you manipulate CSV data by either creating new CSVs from an existing file, or creating a new-new CSV file. That’s right - with these helpers, Tray Platform users will never have to worry about manually performing any of these tasks again:

  • Creating CSV data flat files
  • Downloading CSV from FTP
  • Transforming/reformatting data within CSV files (such as cleaning up incorrectly-formatted rows/columns, or hunting for missing data)
  • Merging/collating CSV data files
  • Uploading completed/merged CSV files
  • Moving CSV data into cloud services/hybrid cloud integrations

That’s because the Tray Platform’s new CSV helpers can automate all these operations for you. Here’s a quick example of the kind of questions I get all the time:

“I work in sales operations. My sales team frequently sends me lead lists in multiple CSV files, which they expect me to load into our CRM. However, sometimes, they don’t always completely fill out every field correctly. I find myself having to manually vet their lead lists, and for any missing data, I have to then chase down the person who submitted the list.”

If you work in sales operations, you may already be familiar with this issue. If not, be advised that sales ops professionals often find their entire day screeching to a halt as they have to manually verify CSV files with hundreds or even thousands of rows - a tedious and time-consuming process that just isn’t a good use of anyone’s time.

Thankfully, with the Tray Platform’s CSV helpers, there’s a better way to handle this issue.

So...what can I do with the CSV Reader and CSV Editor?

Data comes in a variety of forms, but for many reasons, we frequently encounter it contained in a flat file, such as a comma-separated value (CSV) file. CSVs are tabular and frequently take the form of a spreadsheet that houses different classes of data within the fields of various rows and columns. They’re useful in many different contexts for both B2B and consumer companies, such as tracking price changes for consumer e-commerce products or marketing lead lists for enterprise Software-as-a-Service (SaaS) companies.

Businesses that need to Extract, Transform, and Load (ETL) can use the many operations of the Tray Platform’s CSV Reader and CSV Editor to do exactly this, while also integrating their CSV data directly into the many, many other applications that our platform supports. The CSV Reader’s operations include:

  • Create CSV
  • Export CSV file
  • Export split CSV file
  • Get query execution
  • Get query results
  • Start query

The CSV Editor's operations include:

  • Create a CSV from an existing file
  • Create a new CSV

Let’s go over an example:

Example Use Case

As above, let’s consider the example of a sales operations professional who must manage multiple CSV files full of lead leads from different sales team members. Our sales ops pro is tasked with loading every lead list into their CRM.

Unfortunately, not every sales team member fills out their lead list correctly, leaving missing fields and incorrect formatting that require our sales ops expert to stop everything and vet every single suspect field manually.

Problem

Verifying data in each individual row of a lead list CSV file is too time-consuming to be feasible, especially for lists that have literally thousands of individual leads. As a result, this time-consuming task disrupts the schedule of sales ops professionals, who then become a bottleneck in the sales process as other sales team members have to wait for updated lead info to be ready to work on in CRM.

Solution

Use the Tray platform to build a workflow that accepts submitted CSV files via email, then uses the CSV helpers to verify the data in each salesperson’s CSV file, row-by-row. In the case of any missing lead information, the workflow can sync up the missing info by pulling it from that contact’s entry in CRM (in this case, Salesforce).

What this looks like on Tray

Step 1: Email Trigger

Begin the workflow with an Email Trigger that kicks off the subsequent steps each time a lead list CSV is submitted by a member of the sales team to that workflow’s designated email.

Step 2: Begin cleansing data

Begin the process of formatting and cleansing the data in four subsequent steps:

  1. Create CSV From File - Use this operation to create and store a temporary CSV file that will contain all the data submitted in the initial email attachment.
  2. Get Column Count - Use this operation to count the total number of columns in the CSV file (by calculating the total number of columns and rows in the file, the workflow can understand how many filled-in fields should be present in the CSV file, and detect any that are empty).
  3. Create CSV - Use this operation to create a “failure CSV” which logs any columns with empty fields.
  4. Get Row Count - This operation then counts the number of rows in the submitted CSV file for reference.

Then, use a List Helper to retrieve a list of any page numbers in the submitted CSV to help you paginate the rows as needed.

Step 3: Begin verifying the CSV data row-by-row

Use the Loop Collection helper to kick off an iterative loop that verifies the CSV data row-by-row:

  • Get Row - Use this operation to select the next row in each subsequent run of the loop.
  • Text Helpers: Is Email? - Use a text helper step to validate whether the row in question contains a valid email with proper email syntax.

Step 4: Locate the appropriate lead in CRM

Use a Boolean Condition to iteratively verify that the lead in each CSV field actually exists in CRM, using the Salesforce: Find Records operation:

Extract, Transform, and Load CSVs

You’ve now built a workflow that seamlessly accepts lead list CSVs from your sales team and auto-generates a CSV of any “failed” leads that don’t have enough information. However, this isn’t the end of the possibilities with the Tray Platform's CSV helpers. It’s just the beginning.

Learn more about the CSV Reader and the CSV Editor by checking their documentation.