Automated list processing

By Matthew Adams 🕝 Published on January 3, 2024 at 2:30 pm

When automating data streams and files you often encounter variable length data sets with unknown sizes. Automation Workshop is a flexible no-code tool that allows you to process data of any complexity.

It can be simple key/value pairs, CSV tables, or files with unknown properties. Automation Workshop enables you to quickly adjust your workflows in response to data changes caused by outside factors.

Automation Workshop can easily process lists of almost any complexity—be it fixed length, unknown length, and even multi-level lists (tables, lists in lists, and so on). The list separator can be any single character or even a multiple character sequence.

Basic Lists

There are just a few simple concepts of lists in Automation Workshop. All lists are actually treated as a text blob or string. There are simple lists that contain a sequence of items, for example, the Send Email Action provides you with a list of attached files. By default the list items are separated by | vertical bar character.

• Invoice-24-01.pdf | Invoice-24-02.pdf | Invoice-24-03.pdf | …

Double Lists or Tables

There are Actions that provide lists that contain key/value pairs or in other words—a table with two columns. For example, the Copy File Action provides its subsequent Actions with a list of copied files. Each of the list's items contains a source and destination filename.

Double-lists are separated with double || vertical bar characters in contrast to regular lists where the | characters are used. This allows a very flexible way to store tabular data by saving rows (single separator) in a table (double separator).

• item 11 | item 12 || item 21 | item 22 || item 31 | item 32

The same list can be displayed (treated) as a simple two column table, but the idea stays the same—any table can be viewed as a large list with smaller lists inside it.

• item 11 | item 12
• item 21 | item 22
• item 31 | item 32

TIP. Use the Convert List Action or Find & Replace Text Action to change a separator character in a list. For example, to change the default filename separator | to a user-friendly comma or new line character.

Example

Let's say you want to manipulate data in a CSV file/table. First, you need to iterate its rows that are separated by the newline characters. Then split each row with a Tab or Comma character to extract a cell value. The exact configuration depends on input data.

Note that Automation Workshop automatically detects correct newline characters, be it a Windows, Linux, or MacOS style.

Once configured, the automated tasks read newly discovered files fully automatically and without any further instructions. Let's see how easy it is to process, manipulate, and parse lists in Automation Workshop.

1 · Fixed length

If a data structure is well known, it is possible to split the data into tokens. Optionally, for better Task maintenance and readability, you can give each token a custom name. Let's say we are monitoring a folder or FTP server for new files. The files have a fixed naming scheme.

• report-2905-accounting.pdf
• report-210776-sales.pdf

The File & Folder Watcher allows us to access a filename without extension. The filename has 3 parts separated by - dash character. It is basically a list containing 3 items—the name report, ID, and department name.

The Split Text Action allows renaming each of the tokens using a custom naming scheme that aligns to your business processes. To customize this naming even further, a Set Variable Action can be used to create custom variables that are named as the process requires.

Tokenize filenames and give each token a custom name

There are countless file naming schemes where filenames contain client company names, email addresses, invoice numbers, and so on.

Automation Workshop allows anyone to create a custom filename parser and use the extracted information to send emails to each of the customers attaching a corresponding file, or to simply rename files using a different naming convention.

• Video tutorial · Smart rename for generated files
• Tutorial · Automate mail merge emails

2 · Unknown length

The file count may not always be known upfront when Automation Workshop processes multiple files. Or a table can contain a fixed column count followed by some additional optional columns.

• file1.docx | file2.docx | file3.docx | … | file31.docx
• some | list | of an | unknown | length

In the first case it is recommended to use the For Each Action to iterate over the filenames one by one. In the second—use the Split Text Action to access each value of the fixed columns as a variable, and then assign all other remaining values using the Include all remaining tokens feature.

Parse CSV file values, compute a file checksum, and send email if the Product column contains a subscription

Automation Workshop allows parsing and manipulating CSV values with custom rules. More than 100 Actions allows to automate even the most complex business processes, and parse CSV files located on a local server, network share, remote FTP site, or on the cloud.

• Tutorial · Parse CSV files automatically

3 · Multi-level

There are multi-level lists and tables all over the Automation Workshop. The most common built-in lists are the source | destination file lists. Actions like the Copy File or Download File provides its subsequent Actions with a list of files from the source and destination locations.

• file 1 | bckp 1 || file 2 | bckp 2 || … | … || file 9 | bckp 9

If you replace the double || separator with a newline character, you can see a table with two columns—source and destination. In the following example—a file and its backup.

• file 1 | bckp 1
• file 2 | bckp 2
•      … | …
• file 9 | bckp 9

Let's look at a table with 3 columns. Again, it is just a text string that has single | and double || separators.

• file 1 | info 1 | crc 1 || file 2 | info 2 | crc 2 || …

But if you replace the double || separators with newlines, the table's rows are clearly visible.

• file 1 | info 1 | crc 1
• file 2 | info 2 | crc 2
• …

Different techniques can be used to parse and manipulate tables or multi-level lists, however, the basic principles remain the same. Iterate over rows or columns, and then split each row into tokens. Use the following basic steps:

• For Each · iterate over any types of lists;
• Split Text · split a list or row into its values;
• If · to add an if–else logic;
• Set Variable · give friendly names to variables.

Use the source/destination file list to calculate file checksums

Whichever approach you choose to parse lists and tables, Automation Workshop treats them as a CSV or TSV file with an arbitrary separator. The vertical bars | are the best for computer readable files, while newlines and commas—for human readable data.

To work with lists and tables directly, the most obvious choices are the For Each and Split Text Actions. Sometimes when the requirement is to get just one item from a list, the Pick Item comes in handy. Use the Convert List to transfer your table from one format to another.

Getting a particular cell value may not be enough. Often additional processing is required, for example, to remove trailing or leading whitespaces, to remove or extract a part of text, to add leading zeros to align or unify numeric values.

To create a list that is split by some separator, a simple text string concatenation may not be enough. Automation Workshop features a lot of lists, tables, and key/value pairs. See a comprehensive list of Actions and variables that contains such characteristics.

Have a question?

If you have any questions, please do not hesitate to contact our support team.