0

I'm wondering if it's possible to use an AMPscript loop to build a concat function from Data Extension rows.

Let's say I want to create a Base64 encoded string of pipe delimited data from rows in a Data Extension. Pretend for now that there are just 3 rows in that DE, then I could use the following code:

%%[
var @rows, @row, @binaryStream

set @rows = LookupRows("My Data Extension","Campaign", "Welcome")

set @row1 = row(@rows,1)
set @row2 = row(@rows,2)
set @row3 = row(@rows,3)

set @binaryStream = Base64Encode(Concat(
  field(@row1,"id"),'|',field(@row1,"First Name"),'|',field(@row1,"Last Name"),Char(13),
  field(@row2,"id"),'|',field(@row2,"First Name"),'|',field(@row2,"Last Name"),Char(13),
  field(@row3,"id"),'|',field(@row3,"First Name"),'|',field(@row3,"Last Name")))
]%%

This would produce output, which when Base64 decoded would look like this:

1|Jack|Johns
2|Jane|Jones
3|Luke|Smith

That's fine. But what if I wanted to build the @binaryStream based on the number of rows in the DE using a FOR loop? For example, for @i = 1 to RowCount(@rows) do... Can this be done in such a context?

1 Answer 1

4

Are you looking for something like this...

%%[
var @rows, @row, @binaryStream
set @rows = LookupRows("My Data Extension","Campaign", "Welcome")
SET @binaryStream = ""

FOR @i = 1 TO ROWCOUNT(@rows) DO
    SET @row = ROW(@rows,@i)    
    SET @binaryStream = CONCAT(@binaryStream,field(@row,"id"),'|',field(@row,"First Name"),'|',field(@row,"Last Name"))

    /* Add linefeed if this is not the last row */
    IF @i != ROWCOUNT(@rows) THEN
        SET @binaryStream = CONCAT(@binaryStream,Char(13))
    ENDIF
NEXT @i
SET @binaryStream = Base64Encode(@binaryStream)
]%%
4
  • Works like a charm, thanks! I tried something similar but was approaching it wrong. Interesting to see that you can set a variable on itself SET @binaryStream = Base64Encode(@binaryStream), I've never tried that before! Commented May 19, 2017 at 6:16
  • Great answer @AndrewMcLeod! I have been looking for a way to add linefeeds, never thought to add via Char()! (boy is my face red) Commented May 19, 2017 at 12:32
  • @Gortonington Thanks, but I can't take any credit for putting unprintables into the output. The code was lifted from Eliot's original question.
    – Macca
    Commented May 20, 2017 at 1:22
  • @Gortonington, would you be able to join the marketing-cloud chat room and participate in a related discussion of interest to you? Commented May 20, 2017 at 15:39

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .