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.

[mppowe]

I believe replacing \t with \n will get you a newline.

[dunncrew]

I added "Log message to Job report and Workato Logs" with this syntax:

[Step 3 output].pluck('datetimeoftxn_b5896cc8','listoutput_0ec6e5e5').to_csv

I get this error: Error calculating input for field 'message': no implicit conversion of String into Integer

My data from the SQL query in step 3  is a date field and a string. no integers involved.

[gary1]

That error generally means you're trying to access an index or range of something that isn't an array. 

What happens when you remove .to_csv? The output of the logger/message should be a 2D array.

[gary1]

Also, your example says: [Step 3 output]

It may make a difference if you use the "rows" array data pill instead of the full step 3 output. They're likely structured differently.

Post some screenshots if you can.

[dunncrew]

Screen shots posted below. Thanks for your help.