Unanswered

Error datetime string in ISO 8601 format

(0) Share

Report

Posted on by sandracosta

Hello,

I am creating a csv file from an excel file and am having trouble keeping the US DateTime format ('start': '11/26/2020 11:13:05 AM') from my original format.

My flow is as following and as from the first "List rows present in a table" this date is transformed into a string

"start":"44161.4674189815"

that I cannot change back to the original format (MM-dd-yyyyTHH:MM).

I've tried to format the date in the Select action using this function

formatDateTime(item()?['start'],'g')

formatDateTime(item()?['start'],'MM-dd-yyyyTHH:MM')

and some other options but I keep getting the error "failed: 'In function 'formatDateTime', the value provided for date time string '44161.4674189815' was not valid. The datetime string must match ISO 8601 format.'.".

Can you please advise?

Categories:

Using flows

I have the same question (0)

All responses (8)

Answers (0)

Community Power Pla... on at

Like (0)

Report

Hi @sandracosta ,

Try using the expression below to convert excel date:

addDays('1899-12-30',int(item()?['start']),'MM-dd-yyyy')

Thanks

Was this reply helpful? Yes No
sandracosta 11 on at

Like (0)

Report

Hello @Anonymous

Thanks for fast response but I've also tried that function (addDays('1899-12-30',int(item()?['start']),'MM-dd-yyyy') but get this error instead.
InvalidTemplate. Unable to process template language expressions in action 'Compose' inputs at line '1' and column '2871': 'The template language function 'int' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'.
Besides I need the datetime format to remain "MM-dd-yyyy hh:mm".

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

Like (0)

Report

Hi @sandracosta ,

Try using this expression
addDays('1899-12-30',int(outputs('Get_a_row')?['body/start']),'MM-dd-yyyyTHH:MM'

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

Like (0)

Report

I recommend selecting field from dynamic content. In your case it might put your compose in Apply to each loop.

addDays('1899-12-30',int(*******),'MM-dd-yyyyTHH:MM'

Was this reply helpful? Yes No
sandracosta 11 on at

Like (0)

Report

Hello @Anonymous,

This seamed like a good solution, but when trying this I can't get any data from Get a Row.
I have setup as this, I get a 400 error message:

Call_SID is a key column in my file.

Can you advise on this? Could this have anything to do with the file itself?

Thanks in advance

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

Like (0)

Report

Can you try putting values manually in "Get a row" action and see if that works?

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

Like (0)

Report

Also can you post the error log & excel file(change it to dummy values) Just want to see the structure.

Was this reply helpful? Yes No

sandracosta

11 on at

Like (0)

Report

Hello @Anonymous

Direct Get a Row with a single value in Call_SID, it fetches the row. Although the 'start' value, straight from the file already comes as a string or a float number (I can't determine), so the 'int' function does not work.

Captura de ecrã 2021-01-08 101732_comErroInt.png

The error log is this:

Action 'DateFormat' failed
Unable to process template language expressions in action 'DateFormat' inputs at line '1' and column '2871': 'The template language function 'int' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'.

But with dynamic content (items / values) the action comes out emply. It's the same error as above.

Captura de ecrã 2021-01-08 101355_comErro.png

The error log is this

{
 "status": 404,
 "message": "No row was found with Id '{\"@odata.id\":\"/drives('b%21-8CU6OTQhkOYvKAjuF_Gp8lZA1_en2FKrlFVv3lHgCFTWkqv8307RpiXRrVJxHYC')/items('01EJH6R5TGAE3BQKFEDJC222VSE76XKVVK')/workbook/tables(%27%7B00000000-000C-0000-FFFF-FFFF00000000%7D%27)\",\"style\":\"TableStyleMedium6\",\"name\":\"Table_ExternalData_2\",\"showFilterButton\":true,\"id\":\"{00000000-000C-0000-FFFF-FFFF00000000}\",\"highlightLastColumn\":false,\"highlightFirstColumn\":false,\"legacyId\":\"2\",\"showBandedColumns\":false,\"showBandedRows\":true,\"showHeaders\":true,\"showTotals\":false}'.\r\nclientRequestId: 86db9511-92f0-4dc6-a568-cb86638e7ba4\r\nserviceRequestId: 5d11c4a4-0dc2-4b3e-a87a-6c1333517654;b079114c-7d78-41e3-a131-da4c4c7f6e54",
 "error": {
 "message": "No row was found with Id '{\"@odata.id\":\"/drives('b%21-8CU6OTQhkOYvKAjuF_Gp8lZA1_en2FKrlFVv3lHgCFTWkqv8307RpiXRrVJxHYC')/items('01EJH6R5TGAE3BQKFEDJC222VSE76XKVVK')/workbook/tables(%27%7B00000000-000C-0000-FFFF-FFFF00000000%7D%27)\",\"style\":\"TableStyleMedium6\",\"name\":\"Table_ExternalData_2\",\"showFilterButton\":true,\"id\":\"{00000000-000C-0000-FFFF-FFFF00000000}\",\"highlightLastColumn\":false,\"highlightFirstColumn\":false,\"legacyId\":\"2\",\"showBandedColumns\":false,\"showBandedRows\":true,\"showHeaders\":true,\"showTotals\":false}'."
 },
 "source": "excelonline-ne.azconn-ne.p.azurewebsites.net"
}

The files structure is this

Call_SID	Interaction_ID	RECORD ID	start	Recommendation_Scale	levelOfSatisfactionOfCall	Resolution_Scale
CAb2b17ed5c0041f6c349968aa3e25c680	01f8e0f817ed448aaeb31e4eee959d1f	01f8e0f817ed448aaeb31e4eee959d1f	1/6/2021 12:53:57 PM	10	4	1
CAcdc73ff39b974960be497d6feea3081e	020ad47cb2234e2ea87938fd41ab47c8	020ad47cb2234e2ea87938fd41ab47c8	1/6/2021 1:50:29 PM	0	1	2
CA10c48146b5a228d9de1cf9d0e6c1abdb	056366ddd7ad4879940686e3b4a9b978	056366ddd7ad4879940686e3b4a9b978	1/6/2021 12:18:13 PM			3
CA03eff9f465d9497e37fda88957b96a5d	08693ea713334499b685e75aae435fc9	08693ea713334499b685e75aae435fc9	1/6/2021 11:44:31 AM	6	3	1