How to handle fields that contain multiple values

Hi

I am trying to take some geographic data and load it into a MS-SQL Server table.

The problem is several fields contain multiple values, and the number of them is variable from 1 to N. They are separated by commas.
I want to convert to normal relational form, so each incoming record must create a variable number of output records.

For example:
Lake A; (Town B, Town C, Town D , Town E);

The lake is shared between 4 towns. It may be divided between 1 up to an unknown maximum number of towns.

I want to end up with the following:
Lake A, Town B
Lake A, Town C
Lake A, Town D
Lake A, Town E
...

I can’t seem to figure out how to do this with Flowheater.

Are there any examples on your website?

Kind regards
Mike
Request reveived via E-Mail
 

Hi Mike,

You can handle this with the help of the GroupOut Heater and the little C# Script (see below) into the .NET Script Heater .

The GroupOut Heater makes just N copies of the incoming row. To figure out how many copies you need you have to count the delimiter sign in the “Towns” column with the Occur Heater .

Now you need the index of the current copied row. For this you need the AutoID Heater with enabled “GroupOut” option. With this the AutoID Heater counts from 1 to the copied row.

The .NET Script takes this row index, splits the “Towns” column by the comma sign and returns the right part.

I´ve made a brief example, see attached ZIP file.

Screenshot description

1. Remove the start and ending brackets with 2 String Replace Heater
2. Count the comma sign with the Occur Heater
3. Copy the input rows
4. Get the current copied row index
5.  Execute the script and retrieve the right Town part
6. Remove white space signs

C# Script
 

 
 

Thanks for the very prompt response.