cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Update a SQL table using Excel problem

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

13 REPLIES 13
Highlighted
Super User III
Super User III

Re: Update a SQL table using Excel problem

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



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog

Power Automate Video Tutorials
Highlighted
Community Support
Community Support

Re: Update a SQL table using Excel problem

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.

Highlighted
Anonymous
Not applicable

Re: Update a SQL table using Excel problem

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

Get a row new logic.jpgError in GET A ROW.jpg

Highlighted
Super User III
Super User III

Re: Update a SQL table using Excel problem

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.

 



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog

Power Automate Video Tutorials
Highlighted
Anonymous
Not applicable

Re: Update a SQL table using Excel problem

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: 

Insert row error 1.jpgInsert row error 2.jpgDOJ in ExcelDOJ in ExcelDOJ in SQL ServerDOJ in SQL Server

Highlighted
Super User III
Super User III

Re: Update a SQL table using Excel problem

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



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog

Power Automate Video Tutorials
Highlighted
Anonymous
Not applicable

Re: Update a SQL table using Excel problem

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?

Highlighted
Super User III
Super User III

Re: Update a SQL table using Excel problem

Hi @Anonymous 

 

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

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog

Power Automate Video Tutorials
Highlighted
Anonymous
Not applicable

Re: Update a SQL table using Excel problem

My Compose is failing:

 

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

Please see screenshots below:

Compose structure.jpgInvalid ComposeInvalid Compose

 

Highlighted
Super User III
Super User III

Re: Update a SQL table using Excel problem

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.

 



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog

Power Automate Video Tutorials
Highlighted
Anonymous
Not applicable

Re: Update a SQL table using Excel problem

Please find List rows runtime values

List of rows runtime.jpg

Highlighted
Super User III
Super User III

Re: Update a SQL table using Excel problem

Hi  @Anonymous 

 

I did tried the expression in a compose statement

image.png

 

formatDateTime(addDays('1899-12-30',int(43768)),'dd/MM/yyyy')

 

Here is my test result

 

image.png



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog

Power Automate Video Tutorials
Highlighted
Anonymous
Not applicable

Re: Update a SQL table using Excel problem

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.

423 error.jpg

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Upcoming Events

Experience what’s next for Power Automate

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors
Users online (7,473)