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]

Your other suggestions got me a usable result. I will now start looking into Message Templates.

[dunncrew]

Thanks. I will give those ideas a try. 

I don't know what is appending these characters _b5896cc8 , _0ec6e5e5 to the end of the column names.

datetimeoftxn_b5896cc8

Listoutput_0ec6e5e5

[dunncrew]

Input data from MS-SQL Server

DateTimeOfTxn                                 ListOutput

2023-10-09 17:38:36.243             Smith, Joe, Front Door                                                                             

2023-10-09 17:38:36.243             Jones, Mary, Back Door     

2023-10-09 17:42:37.770             Bishop, Dennis, Side Door                                                                                                                                                   

Desired result is an email with date-Time formatted like this:

10/09/2023 17:38           Smith, Joe, Front Door                                                                             

10/09/2023 17:38           Jones, Mary, Back Door      

10/09/2023 17:42           Bishop, Dennis, Side Door        

SQL “Select” to get the records                                                                 

Log records to CSV

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Email from Step 3 with non-data characters removed with gsub (very messy, data wraps, wrong date format)

    2023-10-09 17:38:36.243000000 ,    Smith, Joe, Front Door                                                                               ,    2023-10-09 17:38:36.243000000 ,    Jones, Mary, Back Door                                                                               ,    2023-10-09 17:42:37.770000000 ,    Bishop, Dennis, Side Door                                                                            ,    2023-10-09 17:42:37.770000000 ,    Fisher, Emily, Emergency Door                                                                        

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Email from Step 3 “Rows”. Contains all the “junk” characters.

[{"datetimeoftxn_b5896cc8"=>"2023-10-09 17:38:36.243000000", "listoutput_0ec6e5e5"=>"Smith, Joe, Front Door                                                                              "}, {"datetimeoftxn_b5896cc8"=>"2023-10-09 17:38:36.243000000", "listoutput_0ec6e5e5"=>"Jones, Mary, Back Door                                                                              "}, {"datetimeoftxn_b5896cc8"=>"2023-10-09 17:42:37.770000000", "listoutput_0ec6e5e5"=>"Bishop, Dennis, Side Door                                                                           "}, {"datetimeoftxn_b5896cc8"=>"2023-10-09 17:42:37.770000000", "listoutput_0ec6e5e5"=>"Fisher, Emily, Emergency Door                                                                       "}]

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Email from CSV. Data wraps and needs formatting

{"message"=>"2023-10-09 17:38:36.243000000,\"Smith, Joe, Front Door                                                                              \"\n2023-10-09 17:38:36.243000000,\"Jones, Mary, Back Door                                                                              \"\n2023-10-09 17:42:37.770000000,\"Bishop, Dennis, Side Door                                                                           \"\n2023-10-09 17:42:37.770000000,\"Fisher, Emily, Emergency Door                                                                       \"\n"}

++++++++++++++++++++++++++++

FYI,  If you ask my “why” am doing something, it’s probably because it at least didn’t cause an error ! 😀

[gary1 · Accepted solution]

For step 8, replace "Step 5 Output" with Step 5 Message. IMO it's very rare that you will use the "Step # Output" directly. You generally want to use the data pills within the step's output.

Anyway, switching gears: let's try something different.

First, make a variable list:

By doing this, you now have the ability to parse and transform (clean up) the values as they go into the list:

Using to_time makes the date string a date object and then using strftime converts it back to a string using the specified formatting. Using strip on the output removes leading/trailing whitespace characters.

Now that it's cleaned up, you can pluck/to_csv and it'll be a lot closer to what you want in your email, but I still recommend using a message template! It's only a few extra steps from here.

[dunncrew]

Extremely Helpful. Thanks !