Zapier formatter is a powerful Zapier app. If you’ve ever built anything beyond simple automation, you’ve most likely used it to format text, numbers, or dates. But, contrary to its name, Zapier formatter is not just about formatting the data. With Zapier Formatter Utilities, you can perform different, but just as powerful, operations on your data. Here’s what it’s all about. 

When to Use Zapier Formatter Utilities 

The utilities event gives you access to a set of distinct transformations. Interestingly, not all of them have much in common (although some are very closely related). However, each of them can be used in a variety of situations.

First, you can use Zapier formatter utilities to import a CSV file. This comes in handy when you need to process its data in one of the other steps in your workflow. 

Other options include transformations that let you work with lists and line items.

For example, the pick-from-lists transformation lets pick just the first item from a list of preferences. This comes in handy for example for uploading partial survey data to your CRM or a spreadsheet.

With the Zapier lookup tables (one of my favorite Zapier tools), you can map different values. A common situation when a lookup table proves to be life-saving is a discrepancy between product IDs. A lookup table allows you to map one to the other, and successfully connect the two apps.

Other available Zapier formatter utilities tools all relate to working with lists. They let you turn line items into text (or vice versa) or append/prepend new values to existing lists of line items. They’re useful whenever your work involves lists of incoming or outgoing data.

Where to Find Formatter Utilities

To create a Utilities step, start by selecting Formatter from the list of available apps. As you can see, you don’t even need to perform a search to find it:

You can find Zapier Utilities in Formatter

Then, open the list of available Zapier Formatter events and select Utilities:

A screenshot showing the selection of Utilities

Next, you can browse the list of all available transformations. As you can see, over half of them relate to working with line items or lists:

Utilities offer a wide range of data transformations

Compared to other formatter events, there aren’t many Zapier utilities transformations. The event lets you:

  • Import a CSV file
  • Create, append or prepend items to lists of line items
  • Pick items from a list
  • Turn line items to text
  • Turn text to line items 
  • Create lookup tables and map different sets of values. 

Now, let’s go over each of them to see how they work, and how you can add them to your Zaps. 

How to Import a CSV file with Zapier

Importing a CSV is the first transformation in the Zapier utilities transformations list. To get started, open Zapier Formatter, select the Text event, and pick the transformation from the very top of the list:

Screenshot showing the import CSV file option in Zapier formatter

Next, you can start setting up the “import a CSV file” step. As you can see, there are quite a few things you can set: 

Setting CSV file options in Zapier

First, you want to enter the input file pulled in one of the previous steps. In this example, we’re uploading it from Google drive.

Next, you can choose whether you want to force Zapier to use the first row as a header row. Zapier should detect it automatically, but if it does not, you want to select “yes” in this step.

Then, you can choose the type of CSV file. There are several options to choose from:

  • You can ask Zapier to detect the type automatically (preferred option, especially if you want to create a dynamic workflow).
  • A comma-delimited file
  • A semicolon-delimited file
  • An Excel-comma-delimited file
  • Excel-tab-delimited file
  • A one-column file
  • A text file

In our case, we asked Zapier to detect the file type for us:

A screenshot showing the different commands for CSV file in Zapier

Lastly, you can choose the encoding type. In most cases, you can use the default UTF-8. The two other options, Latin-1 and UTF-16, are much rarer than the other one.

And here’s what the output of a sample list of old cars looks like:

Output provided by Zapier after processing CSV file automatically

One quick note – this step will not be useful for processing large CSV files. Unfortunately, Zapier is unable to import CSV files larger than 150 KB:

Zapier does not allow uploading of large CSV files

In this case, you’ll have to split the file before importing it. 

However, the step is perfect if you plan to regularly import data from your CRM, online store, or other sources. That is before the file gets too big. 

How to Work with Line Itemizer in Zapier?

The next available transformation is the line itemizer. It creates a new line item array out of multiple existing strings of texts or arrays.

To get started, select Line Itemizer from the list of available Zapier transformations:

Line itemizer - one of Zapier Formatter Utilities transformations

Next, look at the available options. First, you’ll be asked to enter the line item group name. In our example, we’ll name the group “Cars for sale”. Then, we add three distinct data sets – Mileage, Price, and Year.

The three data sets are line items that come from an imported CSV. But, we want to add a few more data pieces to each set. Itemizer allows us to merge existing comma-delimited data with the data added manually:

A screenshot showing settings of Zapier itemizer

The two other questions in this Zap relate to the subtotal property. In our case, we don’t need that. But, it’s a very handy feature that we’ll discuss later in this guide to Zapier formatter utilities. 

As you can see in the output, the manually-added data is now part of the original lists of line items:

A screenshot showing the Output provided by Zapier itemizer

Keep in mind that the manually input data is just an example. This step can be used to connect comma-delimited data or line items from different sources. This allows you to create complete data sets dynamically.

For example, you could create a new group of line items by connecting Google sheets with the imported CSV data. As it is in most cases with Zapier, the sky’s the limit!

What Is the Subtotal Property in Zapier Itemizer?

Now, let’s get back to the option that we previously skipped – the Subtotal property. 

By using this option, you can automatically calculate the subtotal value. This is useful when the destination app requires a total amount (and when the source app doesn’t provide it). 

To force Zapier to automatically calculate the value, you need to map your data sets to properties called price and quantity (or Qnt). Of course, don’t forget to set “Create Subtotal Property” to a “Yes”:

Zapier itemizer can automatically calculate subtotal value

As you can see, Zapier created a new property AND calculated the total value:

Screenshot showing the output provided by Zapier Itemizer after calculating the subtotal value

Of course, this substep can also be used to merge data from different sources and calculate the subtotal value automatically. Just be aware that the quantities have to be higher than 1. 

How to Turn Line Items to Text

The next step allows you to turn line items into text separated with a separator of your choice. It also returns each item individually. This allows you to use individual items in future steps. 

Just keep in mind that if you pick the first item from the list, all future zap runs will be using the first item from this step’s output. So, how do you get started? 

As usual, start by selecting the right transformation from the dropdown list. In our case, we want the Line item to text one:

Zapier allows you to turn line items into text separated with a separator of your choice

Next, you will be asked to provide the input data source and a separator. In our case, we want to split the data with a new line. The [:newline:] is one of Zapier’s special characters:

Setting of the Line-item to text transformation - a data set and a separator

And here’s the output of the transformation:

Output provided by Zapier after line item to text transformation

Of course, you are free to choose a different separator, such as a comma or semicolon. The main advantage of this transformation is that it makes data easier to use and manipulate. 

How to Turn Text to Line Items

Now, let’s look at the opposite of the previous step. This time, we want to turn text into line items. To get started, first pick the Text to Line item from the Zapier formatter transformation list. Note that it doesn’t appear immediately next to its opposite transformation:

Zapier formatter allows you to transform text to line item

For the Zap to work, the text has to be comma delimited. In our example, we’re reusing the data that we generated in the previous step. We just changed the separator to a comma compared to the example:

A screenshot showing the input for Zapier text to line transformation in Formatter Utilities

And here’s the output:

The output of the text to line item transformation in Zapier Formatter Utilities

Keep in mind that not all apps will accept text, just like not all of them will accept line items. That’s why it’s important to understand the input data that your app accepts before you get to building a Zap.

How to Pick Items from a List in Formatter

Now, let’s look at how you can use Zapier Formatter utilities to pick individual items from a list. Of course, you could always split that list into separate items. But unless you need to be very specific, there’s no need to do that.

First, select the “Pick from list” transformation:

Zapier Formatter utilities can be used to pick individual items from a list

Next, you’ll be asked to fill in the details. As you can see, there’s not much to fill:

A screenshot showing the input details to pick individual items from a list

 The transformation allows you to select one of three different operations. Zapier can choose the first item, the last one, or a random one. In our case, we asked it to pick the first one:

The three different options to choose for pick from the list Zapier transformation

And here’s the output – as you can see, it’s equal to the first value in the list that we provided in this step:

The output of the pick from list transformation

How to Use Zapier Lookup Tables

Now, let’s look at one of the most powerful and useful Zapier formatter steps – the lookup tables. The tables are pretty powerful and allow you to map different types of data. This is critical in ensuring that you connect the right data sets.

For example, you can map the different IDs and SKUs of products or services in different apps. Similarly, you can map users between two different apps, making it easier to update them.

In fact, the number of use cases for the lookup tables is endless – and it all depends on your individual needs. So, how do you get started with the lookup tables?

First, select the Lookup Table from the transformations list:

Zapier allows you to map data using Lookup tables

Next, you’re ready to start building the table. First, you want to set the lookup key. It’s a dynamic value that you receive in one of the previous steps. 

In our case, the lookup key is a service ID inside SPP. We then map different service IDs to their respective ClickUp task templates IDs:

A screenshot showing a sample configuration of lookup tables

We also set a Fallback value. It’s used if Zapier is unable to locate the value for the lookup key in the table.

And here’s the output:

Output of the lookup table transformation in Zapier formatter Utilities

As you can see, it’s the same value that got mapped to service ID 13.

Get More Out of Your Zaps with Zapier Formatter Utilities

And that’s it! You now know all the transformations available inside Zapier formatter utilities. Now you are ready to manipulate lists of items, create lookup tables, and import CSV files. 

Still, let’s not forget that Zapier formatter is more than just the utilities features. To learn all the ins and outs of it, don’t miss the other guides in the Zapier Formatter series: 

Naturally, learning all of that will take time – and you’ve got a business to run. Thankfully, you don’t have to do all the Zapier work on your own. 

Hire a professional Zapier consultant and let them do the work for you. All that while you focus on the things that you’re the best at.

To get started, head over to this page and get in touch. Let’s talk about your business and automation projects.

Jacek Piotrowski
Jacek Piotrowski

Hey, I’m Jacek. I’m the founder and Chief Automator at Clickleo.com

I’m on a mission to help you use automation to reclaim your time and achieve more in your business.

You can find out more about me – and why I started Clickleo – over on this page

Comments

Share on activity feed

Powered by WP LinkPress

Pin It on Pinterest

Share This