Update a SQL table using Excel problem

[Anonymous]

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 spreadsheetStep 1Step 2Step 3Result

[abm]

Hi @Anonymous 

Most likely you don't have table in your excel sheet.

See this post

https://powerusers.microsoft.com/t5/General-Power-Automate/Flow-to-create-table-in-Excel-file-that-I-save-to-SharePoint/td-p/98467

Thanks

[v-litu-msft]

Hi @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:

https://powerusers.microsoft.com/t5/Power-Automate-Ideas/Dynamic-content-in-File-amp-Table-inputs-Ex...

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.

[Anonymous]

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

[abm]

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.

[Anonymous]

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: 

DOJ in ExcelDOJ in SQL Server

[abm]

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

[Anonymous]

Does 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?

[abm]

Hi @Anonymous 

It should work. Try to do via compose. If that doesn't work please post a screen shot.

Thanks

[Anonymous]

My Compose is failing:

formatDateTime(addDays('1899-12-30',int(items('Apply_to_each')?['DOJ'])),'dd/MM/yyyy')

Please see screenshots below:

Invalid Compose

[abm]

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.

[Anonymous]

Please find List rows runtime values

[abm]

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

[Anonymous]

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.