Unanswered

Filter Array and Convert Dates

(0) Share

Report

Posted on by Community Power Pla...

I have a really newbie question for those much more experienced with me. My end goal is to take a CSV I receive via email, clean it up, and then send it out to my team. Here are my steps:

new email arrives
parse CSV from aid email
convert it to an array
filter out all the null values in the "Order End Date" column
filter out any date more than 48 hours from today

However, I keep running into problems in two areas. 1) my filter out of null values is not working and 2) with dates and need some assistance from anyone willing to help. Here is my flow:

Select connector gives error "InvalidTemplate. The execution of template action 'Select' failed: The evaluation of 'query' action 'where' expression '{ "Order End Date": "@formatDateTime('Order End Date', 'dd/MM/yyyy')" }' failed: 'In function 'formatDateTime', the value provided for date time string 'Order End Date' was not valid. The datetime string must match ISO 8601 format.'."

Her is a sample of my JSON data:

{"from":["[{\"Order#\":\"GL Dept:\",\"Bill To Name\":\"in list (9420 Fremont Local Dedicated, 9700 Austin Terminal)\",\"Order Start Date\":null,\"Order End Date\":null,\"Delivery Date and Time\":null,\"Entered By\":null,\"Service Level\":null,\"Gross Order Amount\":null},{\"Order#\":\"\",\"Bill To Name\":\"\",\"Order Start Date\":null,\"Order End Date\":null,\"Delivery Date and Time\":null,\"Entered By\":null,\"Service Level\":null,\"Gross Order Amount\":null},{\"Order#\":\"Order#\",\"Bill To Name\":\"Bill To Name\",\"Order Start Date\":\"Order Start Date\",\"Order End Date\":\"Order End Date\",\"Delivery Date and Time\":\"Delivery Date and Time\",\"Entered By\":\"Entered By\",\"Service Level\":\"Service Level\",\"Gross Order Amount\":\"Gross Order Amount\"},{\"Order#\":\"588281\",\"Bill To Name\":\"Sedak, Inc.\",\"Order Start Date\":\"\",\"Order End Date\":\"\",\"Delivery Date and Time\":\"\",\"Entered By\":\"SAlexander\",\"Service Level\":\"\",\"Gross Order Amount\":\"598.20\"},{\"Order#\":\"629244\",\"Bill To Name\":\"Samsung SDS Global SCL America, Inc.\",\"Order Start Date\":\"2022-01-11\",\"Order End Date\":\"2022-01-20\",\"Delivery Date and Time\":\"\",\"Entered By\":\"MVasquez\",\"Service Level\":\"\",\"Gross Order Amount\":\"11659.64\"}

Categories:

General topics

I have the same question (0)

All responses (3)

Answers (0)

Expiscornovus 33,189 Most Valuable Professional on at

Like (0)

Report
Hi @Anonymous,

I have had a look at the Parse CSV action. It actually outputs a csvData string value which already looks like an array. You can use a json function to convert it to valid json. After that you should be able to directly use it in an filter array.

Below is an example of that approach.

This is the expression I am using in the From of the Filter Array action.

json(outputs('Parse_CSV')?['body/csvData'])

In the where of the Filter Array action I am also using this expression to refer to the Order End Date column:

item()?['Order End Date']

This is the expression used in the value field of the Map section of the Select action:

formatDateTime(item()?['Order End Date'], 'dd/MM/yyyy')

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report
@Expiscornovus

Thanks for helping me out! When I follow your recommendation, it errors our in the "select" data operation with the following error:
InvalidTemplate. The execution of template action 'Select' failed: The evaluation of 'query' action 'where' expression '{ "Order End Date": "@formatDateTime(item()?['Order End Date'], 'dd/MM/yyyy')" }' failed: 'The template language function 'formatDateTime' expects its first parameter to be of type string. The provided value is of type 'Null'.

Any idea why this would happen?

Also in your screenshot above, my filter array looks different than yours:

Was this reply helpful? Yes No
Expiscornovus 33,189 Most Valuable Professional on at

Like (0)

Report

Hi @Anonymous,

My setup is probably slightly different. I am using the contentbytes of the first attachment in the Parse CSV. I am using an expression for that. Are you looping through all attachments?

Was this reply helpful? Yes No