Format SQL Results into Email

[dunncrew]

Hi, In my recipe, I am selecting 2 columns from a SQL table, and want to put the results in the body of an email.  I get results, but they look like this:

{"rows"=>[{"datetimeoftxn_b5896cc8"=>"2023-10-09 13:59:23.797000000", "listoutput_0ec6e5e5"=>"Test_Last_Name1, Test_First_Name1, Test_Door_Name1                                                  "},

{"datetimeoftxn_b5896cc8"=>"2023-10-09 13:59:23.797000000", "listoutput_0ec6e5e5"=>"Test_Last_Name2, Test_First_Name2, Test_Door_Name2                                                        "}]}                                         

I just want the data in my email like this:

2023-10-09 13:59       Test_Last_Name1, Test_First_Name1, Test_Door_Name1  

2023-10-09 13:59       Test_Last_Name2, Test_First_Name2, Test_Door_Name2

The 2 SQL Columns are datetime and nchar. What's the best way to get just the data in my email ?

[gary1 · Accepted solution]

I'd recommend using a message template to format the data into a nicely formatted HTML table, and then insert the table into the email body.  You'll need to brush on HTML for the formatting and mustache for the parsing in the message template, but it's definitely worth the effort -- especially if you may need to create more emails in the future.

[dunncrew]

I simplified my input and now the SQL field  has concatonated date and names, so I only need to format 1 string field. For some reason it is putting all the records in 1 row. Is there a way to force each record to be on a new line ?

Instead of this

    10/11/2023  10:16     Jones, Mary,   Computer Room    ,    10/11/2023  10:54     Smith, Frank,   Computer Room    ,    10/11/2023  15:56     Amos, Henry,   Computer Room   

I want

10/11/2023  10:16     Jones, Mary,   Computer Room     

10/11/2023  10:54     Smith, Frank,   Computer Room   

10/11/2023  15:56     Amos, Henry,   Computer Room  

I tried GSUB to replace the comma separator with new line, but didn't work. Bad syntax ? Better solution ?