How to Use Zapier Formatter Utilities

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:

Then, open the list of available Zapier Formatter events and select 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:

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:

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: 

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:

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:

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:

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:

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:

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:

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”:

As you can see, Zapier created a new property AND calculated the total 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:

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:

And here’s the output of the 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:

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:

And here’s the output:

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:

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

 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:

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:

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:

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:

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:

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: 

• Introduction to Zapier Formatter
• Guide to manipulating date and time in Zapier
• Guide to manipulating numbers in Zapier
• Guide to manipulating text in Zapier

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.