Overview

Many types of imports have an optional step to transform your data. This is useful for things like cleaning your data or for skipping over data you don't want to import. In the example below, we're importing KPI values, but transformations work the same regardless of what kind of data you're importing.

Adding Transformations

The transformation step of all imports is optional, so if you want to import your data as-is, just click next. If you want to change your data before it’s imported, however, click Add Transformation.

This opens the add transformation menu where you can apply any combination of dozens of types of transformations to your data.

For example, you can create a filter to set the KPI value to N/A if the value column is less than 1.

Here we’re adding “My” to the beginning of every KPI name.

You can even use transformations to do data cleanup, like removing all non-numbers from the Value column.

Regular Expression Filter

The "matches regular expression" filter is incredibly powerful, but it's also very technical. Regular expressions are used in software development and some advanced software applications to match text. In this example, the regular expression b[aeiou]bble will match the following words:

babble
bebble
bibble
bobble
bubble

There are resources across the web to help you learn how to make a regular expression to match the text you want. We’ve found that ChatGPT is amazing at building the perfect regular expression. There are also online tools like RegExr that are really handy.

Regular Expression Transformation

Regular expressions are incredibly powerful for text matching, but they can also be used to transform your data. For example, let’s say you have a field that has text values like this:

1440×900 pixels
1600×900 pixels
800×600 inches

And you want to convert it to values like this:

Width: 1440×Height: 900 pixels
Width: 1600×Height: 900 pixels
Width: 800×Height: 600 inches

You can do this by choosing the “matches regular expression” filter type and writing a regular expression with groups. You can then manipulate the groups as separate transformations.

”By converting to date” Transformation

Spider Impact does a great job of turning text into dates on its own, but sometimes you'll need to give it a push in the right direction when your source data isn't in a common format. The “by converting to a date” filter turns text into dates by telling the software where to find the day, month, and year.

Days, months, and years are represented by the following characters:

To separate the days, months, and years, any number of the following characters can be used

space ( )
hyphen (-)
comma (,)
forward slash (/)

For example, if your dates look like 5/15/2020, you’d use m/d/y for the source date format. If your dates look like 3-Mar-19, you’d use d-m-y for the source date format. As long as you tell Spider Impact where to find the data, it’s smart enough to determine that Jan, January, and 1 are the same thing.

There are times, of course, where you’ll come across date formats that Spider Impact can’t parse on its own. For example, February 20th, 2018 could be stored as 022018, but there's no way for the software to automatically determine which numbers match to which parts of the date. In these situations, you can apply regular expression transformations to the data before converting it to a date.

Here we:

Start with text like 022018
Convert it to text like 02-20-18
Parse it into a date with m-d-y