I am trying to do the following:
1) Receive an email with an attachment (Excel)
2) Save the Excel to One Drive
3) Read the Excel rows and update a SQL Server table (Employees)
(The column names of my spreadsheet match the table column names for simplicity)
My flow runs successfully up to "Get Tables" where the output is [] (null I believe)
and the flow stops.
I am new on this, so kindly let me know:
If my flow is correct.
Any working example I can use?
The documentation I found is not sufficient for my needs.
My spreadsheet
Step 1
Step 2
Step 3
Result
Hi @Anonymous
Most likely you don't have table in your excel sheet.
See this post
Thanks
If you liked my response, please consider giving it a thumbs up
Proud to be a Flownaut!
Learn more from my blogPower Automate Video TutorialsHi @Anonymous,
Unfortunately, the table name couldn't be set dynamically, it could only be set from drop-down manually.
There have some ideas in IDEA forum, you can head to vote it to make it come true in the future:
Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Many thanks abm, with your help I was able to move further.
My problem is now with "Get a row" (images below)
What needs to be entered in key value?
I have tried unsuccessfully:
id
Highlight first column
current item
Bear in mind that, I can't hard-code this value (as the connector seems to imply), because it will vary depending on the contents of the incoming file
Any ideas are welcome
Hi @Anonymous
Thanks for your reply. Get row can only return one record. If you want to read all the content then you need to use List rows action.
Please see my blog post
https://mydevexperience.wordpress.com/2019/09/25/microsoft-flow-excel-list-rows-present-in-a-table/
If you need any further help let me know.
If you liked my response, please consider giving it a thumbs up
Proud to be a Flownaut!
Learn more from my blogPower Automate Video TutorialsThanks again abm.
The "List rows" does the trick.
So far I'm one step away from writing the Excel rows onto my Employees table in SQL.
However I get a failure trying to move a date field in excel to a corresponding date in SQL.
What is the correct format a date field ?
Please check screenshots below:
DOJ in Excel
DOJ in SQL Server
Hi @Anonymous
Thanks for your reply.
For the date format see this post
https://powerusers.microsoft.com/t5/Building-Flows/Date-changes-when-I-create-a-txt-file/m-p/406905
Thanks
If you liked my response, please consider giving it a thumbs up
Proud to be a Flownaut!
Learn more from my blogPower Automate Video TutorialsDoes the expression:
"formatDateTime(addDays('1899-12-30',int(items('Apply_to_each')?['DOJ'])),'dd/MM/yyyy')"
needs to be applied on the DOJ field directly?
In doing so I get this error:
{
"status": 400,
"message": "We cannot convert the value \"\"formatDateTime(addD...\" to type Date.\r\n inner exception: We cannot convert the value \"\"formatDateTime(addD...\" to type Date.\r\nclientRequestId: 6994ef39-249a-4b57-90a4-448e8b4443cd",
"error": {
"message": "We cannot convert the value \"\"formatDateTime(addD...\" to type Date.\r\n inner exception: We cannot convert the value \"\"formatDateTime(addD...\" to type Date."
},
"source": "sql-sea.azconn-sea.p.azurewebsites.net"
}
Or perhaps I need to create a Compose Action before?
Hi @Anonymous
It should work. Try to do via compose. If that doesn't work please post a screen shot.
Thanks
If you liked my response, please consider giving it a thumbs up
Proud to be a Flownaut!
Learn more from my blogPower Automate Video TutorialsMy Compose is failing:
formatDateTime(addDays('1899-12-30',int(items('Apply_to_each')?['DOJ'])),'dd/MM/yyyy')
Please see screenshots below:
Invalid Compose
Hi @Anonymous
Thanks for the screenshot. Could you please send me the List Rows runtime history values. I want to know what value is passing from Excel to convert to Date.
If you liked my response, please consider giving it a thumbs up
Proud to be a Flownaut!
Learn more from my blogPower Automate Video TutorialsPlease find List rows runtime values
Hi @Anonymous
I did tried the expression in a compose statement
formatDateTime(addDays('1899-12-30',int(43768)),'dd/MM/yyyy')
Here is my test result
If you liked my response, please consider giving it a thumbs up
Proud to be a Flownaut!
Learn more from my blogPower Automate Video TutorialsThe formatDateTime doesn't work for my spreadsheet unfortunately, after trying several scenarios I resorted to changing the Date format to string both in the Excel spreadsheet and on my SQL table.
I think it works with your test because you hard-coded the input.
My next problem is unfortunately more difficult to resolve:
I keep on getting the status": 423 "message": "The file is currently checked out or locked for editing by another user."
It happens when I create the file (see image below), and I even tried synchronizing, signing off both in Flow and One Drive to no avail.
I read other posts about this and it seems to be a common problem when dealing with Excel.
Flow locks the file even after the flow is completed.
Excel retry options should work with 'file locked'..
When using any Excel action, it appears to me that having to enter the details on an EXISTING Excel file, limits the scope of my solution as I will need to generate a new one on every run.
As usual, many thanks to all and any input will be most welcome.
Check out new user group experience and if you are a leader please create your group
See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.