topic Re: Format SQL Results into Email in Workato Pros Discussion Board

Format SQL Results into Email

dunncrew — Mon, 09 Oct 2023 18:55:41 GMT

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 ?

Re: Format SQL Results into Email

gary1 — Mon, 09 Oct 2023 19:15:22 GMT

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.

Re: Format SQL Results into Email

dunncrew — Mon, 09 Oct 2023 19:27:11 GMT

Thanks. I have about 1 hour total experience in Workato and HTML, so not familiar with message templates. I will take a look. I hoped I could do something in the Outlook email step to specify just the data.

Re: Format SQL Results into Email

gary1 — Mon, 09 Oct 2023 19:56:16 GMT

Got it. Learning how to use it will have long term benefits, but here's a quick fix for now.

Make a logger with this formula:

[array_output].pluck('datetimeoftxn_b5896cc8','listoutput_0ec6e5e5').to_csv

to_csv isn't the ideal method to use, but it's the simplest way to get close to what you want:

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

If this is close enough, then you're done. If you want to break it up a bit, you can add this to the formula:

.gsub(',','\t')

This will substitute the commas with tabs to give the text a little more breathing room.

You can insert this logger (or the formula) directly into the body of the email.

If you want to get rid of the timestamp in the date, I would suggest reformatting it on the SQL output.

Otherwise, there are longer ways to do this by creating a list variable and reformatting the SQL data as you add it to the list. Then you can convert the list into readable text for your email body.

Re: Format SQL Results into Email

dunncrew — Mon, 09 Oct 2023 20:12:37 GMT

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

Re: Format SQL Results into Email

dunncrew — Tue, 10 Oct 2023 12:36:08 GMT

is .gsub(',','\t') adding a TAB ? Is there a way to add a line break to force each record to a new line ?

Re: Format SQL Results into Email

mppowe — Tue, 10 Oct 2023 13:13:18 GMT

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

Re: Format SQL Results into Email

dunncrew — Tue, 10 Oct 2023 15:42:52 GMT

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.

Re: Format SQL Results into Email

gary1 — Tue, 10 Oct 2023 15:50:55 GMT

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.

Re: Format SQL Results into Email

gary1 — Tue, 10 Oct 2023 15:57:08 GMT

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.

Re: Format SQL Results into Email

dunncrew — Tue, 10 Oct 2023 18:32:48 GMT

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 ! 😀

Re: Format SQL Results into Email

dunncrew — Tue, 10 Oct 2023 18:28:24 GMT

Screen shots posted below. Thanks for your help.

Re: Format SQL Results into Email

gary1 — Tue, 10 Oct 2023 21:08:47 GMT

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.

Re: Format SQL Results into Email

dunncrew — Wed, 11 Oct 2023 13:53:55 GMT

Extremely Helpful. Thanks !

Re: Format SQL Results into Email

dunncrew — Wed, 11 Oct 2023 17:15:45 GMT

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

Re: Format SQL Results into Email

dunncrew — Thu, 12 Oct 2023 13:37:33 GMT

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 ?