Sure, you’d think this would be simple – hey, it’s easy to do in a text editor, so why not TOS? Well, it is easy in TOS, but you have to get your head around that fact that TOS is a transformation tool. Virtually anything can be done in TOS when you put your mind to it and are familiar with the way that transformations work.

When you’re using a text editor, how does your brain know that you want line 1 of file 2 concatenated with line 1 of file 1? Your brain is actually creating a “primary key” (the line number) automatically and then joining the two lines together based upon their common line number.

Using my favourite editor PSPad (www.pspad.com) here are a couple of files I want to join. Note that I’ve turned the “Line Numbers” option on in PSPad to demonstrate this point:

Ultimately, I want to create a file like:

The line number is the common link between the two files, so the secret is to generate line numbers for each file as they are loaded into the TOS Job. This is done using the sequence function within TOS to assign the “line number” as the lines are loaded.

Think “identity” in SQL Server or Sequences in Oracle.

The sequence function in TOS allows different sequences of numbers to be generated by using a unique name for each sequence required. A sequence function call looks like:

sequence(name, start, step)

Where:

• name is the unique name of the sequence
• start is the number to start from
• step is the incremental value

In this example, we would need a sequence for the alpha.txt file and one for the num.txt file:

• sequence(“alpha”, 1, 1)
• sequence(“num”, 1, 1)

This will match the line numbers as they are loaded.

Now that we’ve finished the concepts, let’s load up TOS and create the job.

It’s up to you which type of component you use, but I have used tFileInputPositional components in this example to represent the two input files alpha.txt and num.txt.

The next step is to generate the line numbers (or sequence) for each file.

Add a tMap to each of the tFileInput components in the job:

Within each tMap:

• create an output flow for the “line number” and the text
• add the “line number” and text to the schema for the flow (set the “line number” to an integer and make it a Primary Key)
• modify the expression for the “line number” to the sequence expression - sequence(“alpha”, 1, 1) or sequence(“num”, 1, 1)
• drag the text from the input flow to the text in the output flow

Now that the flows have had line numbers (primary keys) added, they can be joined together using those line numbers.

Add a tMap to the Job and join the flows from the other tMap components as inputs:

Within the tMap, join the input flows together based upon the “line numbers”:

Add a new output table and add a column for the merged text. For the merged text enter the expression to merge the two lines together in your relevant language.

Using Perl, this expression would be:

• $alpha[alphaText] . $num[numText]

You now have a flow that has concatenated the text from two files together based upon their line numbers – the desired result!

Now, add your final output to the Job – in this example I’ll add a tLogRow but you could add any type of output:

Then, run the job:

Voila!

TOS has very powerful tools that can be used to achieve nearly any result, as can be seen in this example. The use of the sequence function is normally used in tRowGenerator, but can be used in many expressions throughout TOS.