The Tray CSV Processor: A Building Guide

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 Processor lets you automate the management of flat files containing all your important business data. That’s right - from now on, 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 Processor helper 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 Processor, there’s a better way to handle this issue.

So...what can I do with the CSV Processor?

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 Processor to do exactly this, while also integrating their CSV data directly into the many, many other applications that our platform supports. The CSV Processor’s operations include:

  • Add column
  • Add row
  • Get column count
  • Get row count
  • Create CSV from file
  • Create CSV
  • Delete column
  • Delete columns
  • Delete row
  • Delete rows
  • Duplicate
  • Duplicate column
  • Export CSV
  • Find row
  • Generate columns from object
  • Get cell
  • Get row
  • Get rows
  • Import data
  • Join CSVs
  • Rename column
  • Rename columns
  • Update cell

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 Processor 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: Use CSV Processor to begin cleansing data

Use the CSV Processor in multiple steps to 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 CSV Processor 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:

  • CSV Processor: 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 CSV Processor. It’s just the beginning.


Learn more about the CSV Processor in our docs section.

Get updates from the Tray.io blog directly to your inbox

All the latest product news, tips and tricks direct to your inbox weekly. You can unsubscribe when ever you want!