cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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.

Spreadsheet.jpgMy spreadsheetstep 1.jpgStep 1step 2.jpgStep 2step 3.jpgStep 3step 4.jpgResult

13 REPLIES 13
Super User
Super User

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
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.

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

Super User
Super User

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
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 Date in Excel1.jpgDOJ in ExcelDOJ Date in SQL Server.jpgDOJ in SQL Server

Super User
Super User

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

Super User
Super User

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
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 Compose.jpgInvalid Compose

 

Super User
Super User

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
Anonymous
Not applicable

Re: Update a SQL table using Excel problem

Please find List rows runtime values

List of rows runtime.jpg

Super User
Super User

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
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
firstImage

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Automate Community!

firstImage

Now Live: Power Virtual Agents Community!

We are excited to announce the launch of Power Virtual Agents Community. Check it out now!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Community Summit North America

The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Users online (8,805)