You’re offline. This is a read only version of the page.
11
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?
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.
The error log is this:
Share
Report
All responses (
Answers (
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.
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 |
Thanks in advance
Also can you post the error log & excel file(change it to dummy values) Just want to see the structure.
Can you try putting values manually in "Get a row" action and see if that works?
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
I recommend selecting field from dynamic content. In your case it might put your compose in Apply to each loop.
Hi @sandracosta ,
Try using this expression
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.
Besides I need the datetime format to remain "MM-dd-yyyy hh:mm".
Hi @sandracosta ,
Try using the expression below to convert excel date:
addDays('1899-12-30',int(item()?['start']),'MM-dd-yyyy')
Thanks
#1
WarrenBelz
146,601
Most Valuable Professional
#2
RandyHayes
76,287
Super User 2024 Season 1
#3
Pstork1
65,946
Most Valuable Professional
