Operations in column matching lets you go beyond simple 1:1 column matching. You can use operations to split, join, and much more with custom expressions.

Column

The COL operation is the default operation. It simply matches the column from the source file to the column in the table schema.

Splitting

Splitting is useful when you have a column with multiple values in it. For example, if you have a column with a list of tags, you can split them into multiple columns. When you select SPLIT for a column you will be prompted to enter a delimiter. This is the character(s) that separate the values in the column. For example, if you have a column with the values tag1,tag2,tag3 and you enter , as the delimiter, the column will be split into three columns with the values tag1, tag2, and tag3 respectively.

You then need to pick the index of the split value you want for that target column. For example, if you have a column with the values tag1,tag2,tag3 and you enter , as the delimiter, the column will be split into three values with the values tag1, tag2, and tag3 respectively. If you want the first value, you would enter 1 as the index. If you want the second value, you would enter 2 as the index, and so on.

You can reuse the SPLIT operation on the same source column multiple times if you want to match the split values to multiple target columns.

Joining

Sometimes you need to join values from multiple source columns to a single value. For example, if you have First Name and Last Name as separate columns in your source file, and has 1 Full Name column in the table schema you can use the JOIN operation to concat those values together. When you select JOIN for a column you will be prompted to enter a delimiter. This is the character(s) that used to separate the values that are combined. So for example, in the case of First Name and Last Name, you would enter a space as the delimiter. This would result in a Full Name column with values like John Smith.

NULL

Sometimes the source file doesnt have all the columns of your target schema. In those cases you can use the NULL operation to set the column to a NULL value. This will result in the column being set to NULL for all rows.