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.
Most likely you don't have table in your excel sheet.
See this post
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:
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:
Highlight first column
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
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
If you need any further help let me know.
Thanks 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:
Thanks for your reply.
For the date format see this post
Does the expression:
needs to be applied on the DOJ field directly?
In doing so I get this 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.\r\nclientRequestId: 6994ef39-249a-4b57-90a4-448e8b4443cd",
"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."
Or perhaps I need to create a Compose Action before?
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.
I did tried the expression in a compose statement
Here is my test result
The 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 the new Process Advisor community forum board!
Check out new user group experience and if you are a leader please create your group
Participate in the Power Virtual Agents Community Challenge
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.