Easy Data Transform v2 improvements

This page summarises some of the improvements we have made to Easy Data Transform in v2.

Easy Data Transform now supports schema versioning for inputs. If the input columns differ from expected, you can automatically:

Insert missing columns (with a default value).
Re-order out of order columns.
Add extra columns to the end or ignore them.

Or you can stop with an error.

Use schema

The new Verify transform allows you to check that a dataset has expected data values.

Verify transform

Currently there are 59 different verification checks you can make:

At least 1 non-empty value
Contains
Don’t allow listed values
Ends with
Integer except listed special value(s)
Is local file
Is local folder
Is lower case
Is sentence case
Is title case
Is upper case
Is valid ASIN
Is valid EAN13
Is valid email
Is valid GTIN
Is valid IBAN
Is valid ISBN
Is valid telephone number
Is valid UPC-A
Match column name
Matches regular expression
Maximum characters
Maximum number of columns
Maximum number of rows
Maximum value
Minimum characters
Minimum number of columns
Minimum number of rows
Minimum value
No blank values
No carriage returns
No currency
No digits
No double spaces
No duplicate column names
No duplicate values
No empty rows
No empty values
No gaps in values
No leading or trailing whitespace
No line feeds
No non-ASCII
No non-printable
No punctuation
No symbols
No Tab characters
No whitespace
Numeric except listed special value(s)
Only allow listed values
Require listed values
Starts with
Valid date in format

You can set a Severity for each failed rule.

Verification fails are shown, color-coded, in the Right pane data table. Hover over a header or value for information on the rule(s) failed.

Vizualize verification fails

A new UUID transform has been added. It allows you to add a column of Universally Unique Identifiers (UUIDs).

Generating UUIDs

A new Reshape transform has been added. It allows you to reshape the values of a dataset into a different number of columns. The new number of rows is calculated automatically.

Reshape example

A new Pseudonym transform has been added. It allows you to pseudonymise a column of data.

pseudonymising the date of birth column

You can now easily compare the column structure in 2 or more datasets, by Column name or Column index.

Compare dataset headers

You can now easily compare data values in 2 datasets, by Row numbers or Key columns.

Compare dataset headers

An overview of the differences are shown on the left. You can click or drag this overview to navigate.

You can now select View>Item Summary (or click the new toolbar button) to display a window summarising all the items in the Center pane.

Summary window

You can now Join 2 datasets by Row number instead of using key columns.

Join by row number

The Matching option in the Lookup transform now has additional options:

Lookup matching

This allows you to do transforms like this:

Lookup by contains key

The No match value option in the Lookup transform can now be a column variable.

Lookup miss column variable

The Sort transform now has additional options:

Check intelligent sort to optionally sort dates as dates, and numbers as numbers.

intelligent sort

Check locale aware to use the Locale set in Preferences to determine the sorting order of text.
Check case sensitive for case sensitive sorting of text.

The Calculate transform can now accept either a column or a single value as the first value for binary operations. Previously the first value had to be a column.

Using a constant for the first value for binary operations

The following operations have been added to the Calculate transform:

DaysInMonth to calculate the number of days in the month for a date value.
DaysInYear to calculate the number of days in the year for a date value.
Occurrences to calculate the number of times 1 piece of text occurs in another piece of text.
MSecsToTime to convert an integer number of milliseconds since midnight to a 24 hour time.
TimeToMSecs to convert a 24 hour time into the number of milliseconds since midnight.

Null characters (ASCII/Unicode value=0) are now displayed when you click show invisible:

null symbol

They now are also shown separately in the Characters tab.

null characters

And you can insert Nulls into text fields, e.g. to replace them using Replace:

insert null

The Find Items window has additional check boxes for:

ids
column names
data values

Find items

Added Record separator (0x1E) as an option for Line endings when outputting text files.

You can now check or uncheck columns that match a range of criteria in any transform that allows the selection of multiple columns.

Select columns window

You can also filter columns by name in various transforms.

filter columns by name

Filtering column names in the 'Rename Cols' transform

Filtering column names in the 'Unique' transform

Filtering column names in the 'Verify' transform

This should make selecting columns a lot faster for datasets with lots of columns.

When you are outputting in Excel format, you can now check use style template to copy the styling from another Excel sheet.

Excel styling

Styling copied from the template sheet includes:

column widths
row heights
merged cells
cell borders
text orientation
fonts
colors

Hovering over a connection now highlights the connection and the items at either end. This makes it easier to select connections and to trace flows.

connection hover highlighting

Hovering over a transform button in the Left pane now highlights all transforms of that type in the Center pane. You can use this as a quick way to find transforms of a particular type.

transform button hover highlighting

View>Zoom Selected has been added. This allows you to zoom in on any selected items in the Center pane.

You can now hold down the Ctrl (Windows)/Cmd (Mac) key when adding a new transform or output item to add it to the bottom left of all the current items, rather than to the right of the selected items.

add below

There is now an additional Remove option in the Subtract transform.

Subtract transform example

There is now an ignore empty values for concat option in the Unique transform.

Unique ignore empty

There is now a case sensitive option in the Unique transform. When this is checked, case is taken account of for Keep unique matching.

The Fill and Unfill transforms now have a new For option to only fill/unfill when the adjacent cells have the same value in a key column. This only works when filling/unfilling up or down (not left/right).

Fill by key value

We have improved how Easy Data Transform handles real (floating point) numbers, to try to avoid numerical artifacts.

Rounding artifacts

You can now preview the data in an item without selecting that item. Hover the mouse over an item in the Center pane and press the # key to display the Data Preview window.

Data preview animation

You can now double click on some cells in the Column Values window to drilldown and see the corresponding rows in the dataset.

Meta info column name

Including duplicate values:

Meta info column name

And the most or least frequent values:

Meta info column name

The following file name variables have been added:

{day}
{month}
{year}
{hour}
{minute}
{second}
{computername}

The transforms drop down menu now allows you to show only the most used or most recently used transforms:

Check move Note items with associated items in the General tab of the Preferences window, if you want Note items to be automatically moved when you drag other items that the notes are adjacent to and pointing at.

Auto move associated notes

Batch processing and the command line now show additional summary information.

Plus lots of minor improvements.

Downloads