Concatenate multiple lines of data into a single line

Question

I have a requirement to concatenate multiple lines of data into a single line. The only indexation that multiple lines belong together on a single line is "^" at end of the last line. Please see example below. I have tried solutions in a transformer stage variables with no success. Any help/advise would be greatly appreciated.

**Example**
Input:
name age address 
John|43|1015 main street
    Queens
     New York 10035 ^
Jackie|51|1015 main street Queens New York 10035 ^
James|50|10 Court LN
    Raleigh
     North Carolina 12045 ^

Desired Output:
name age address 
John|43|1015 main street Queens New York 10035 ^
Jackie|51|1015 main street Queens New York 10035 ^
James|50|10 Court LN Raleigh North Carolina 12045 ^

I may have a glimpse what you mean, but I recommend you take a moment to refine your question. Please use formatting tools when editing your question. Beneath the formatting, I am confused by the content, because your Output example seems to be identical to the Input example. — Justus Kenklies, May 30 '22 at 02:32

Justus Kenklies · Answer 1 · 2022-06-04T22:42:54.127

To concatenate multiple lines of data into a single line using a transformer stage:

Process the line as one column Column1 using VarChar (or NVarChar) (with no length given)
Set the Transformer to run sequentially, since you have no key column to partition your lines in the blocks that you want to merge.
In the Transformer, define Stage Variables svLine of Type VarChar with initial value '' and svEOL of type TinyInt(1) with initial value 0
store the line in a Stage Variable svLine using If (svEOL) Then '' Else svLine1 : DSLink2.Column1 to concatenate the lines only if there was no previous EOL maybe implement trimming here, too
To test if you have reached the custom EOL character ^, you can use Right(DSLink2.Column1,1) = '^' to fill svEOL
Add a Constraint to your Output Link containing svEOL

▼ Stage Variables

Derivation	Stage Variable
If (svEOL) Then "" Else svLine: DSLink2.Column1	svLine
Right(DSLink2.Column1,1) = "^"	svEOL

▶ Loop Condition

▼ DSLink4
Constraint: svEOL

Derivation	Column Name
svConcatenateLine	MyOutPutColumn

Wrote this out of my head, should work, but I didn't test it, please give feedback if somthing is wrong. Another approach could be to solve this issue directly when reading e.g. using the Sequential Stage, but since the question does not tell about the complete job, this is it.

Concatenate multiple lines of data into a single line

1 Answers1

Linked