cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vb2608
Frequent Visitor

Excel to sql server

Hi,

 

I am trying to build a manual flow to transfer data from excel to sql server. I want a flow which looks for the records in sql table for the unique IDs present in excel. If flow finds that ID in sql table it should update that record and if not then insert new record. Also, I have columns FromDate and ToDate which can contain empty values. Can anyone help me with this?

2 ACCEPTED SOLUTIONS

Accepted Solutions
abm
Super User
Super User

Hi @vb2608 

 

First you need to read the Excel sheet list rows. Then filter the id to find the record exists in SQL table or not. Follow the below steps.

 

image.png

 

Above I am using an int() expression to filter the records. Here in my expression the Id is an integer. Below is the expression.

int(items('Apply_to_each_2')?['Id'])

 

Next add a compose to find the length of the results. Then check using an IF condition to determine whether its insert or update operation we need to do.

 

image.png

 

 

To find the length of results from SQL connector - Get Rows, I have used the below expression

 

length(outputs('Get_rows_(V2)')?['body/value'])
 
You can map a blank value from excel against SQL DateTime column if SQL datetime column default value is NULL.
 
I have tested the above flow and worked as expected.
 
image.png
 
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

View solution in original post

Hi @vb2608 

 

Please see my video tutorial how to use Excel Dates in Power Automate.

 

https://www.youtube.com/watch?v=uFZxXMuLj-E&t=106s

 

For blank values it should work as expected. I did tested this and worked for me. 

 

Please watch the above video and convert the numeric value to date.  Then map the converted date into SQL.

 

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

View solution in original post

11 REPLIES 11
abm
Super User
Super User

Hi @vb2608 

 

First you need to read the Excel sheet list rows. Then filter the id to find the record exists in SQL table or not. Follow the below steps.

 

image.png

 

Above I am using an int() expression to filter the records. Here in my expression the Id is an integer. Below is the expression.

int(items('Apply_to_each_2')?['Id'])

 

Next add a compose to find the length of the results. Then check using an IF condition to determine whether its insert or update operation we need to do.

 

image.png

 

 

To find the length of results from SQL connector - Get Rows, I have used the below expression

 

length(outputs('Get_rows_(V2)')?['body/value'])
 
You can map a blank value from excel against SQL DateTime column if SQL datetime column default value is NULL.
 
I have tested the above flow and worked as expected.
 
image.png
 
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
vb2608
Frequent Visitor

Hi @abm 

 

Thanks for the above solution. I have adapted your solution to the following flow.

 

vb2608_0-1611660257815.pngvb2608_1-1611660303567.png

This flow is working without 'FromDate' and 'ToDate' columns. But when I add these columns to excel and also add these columns to sql and set default value to NULL it doesn't work. I am getting the following error.

vb2608_2-1611660452843.png

 

Also, when I insert a value to one of the columns then I am getting the following error.

vb2608_3-1611660763204.png

 

vb2608_4-1611660803209.png

 

Regards

Hi @vb2608 

 

Please see my video tutorial how to use Excel Dates in Power Automate.

 

https://www.youtube.com/watch?v=uFZxXMuLj-E&t=106s

 

For blank values it should work as expected. I did tested this and worked for me. 

 

Please watch the above video and convert the numeric value to date.  Then map the converted date into SQL.

 

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
vb2608
Frequent Visitor

Hi @abm ,

 

Thanks for the informative video. It worked in the case when the column has dates. But for the blank values it's giving me this error:

vb2608_0-1611689592389.png

 

Hi @vb2608 

 

You haven't explained me what that compose is doing? Believe its the Excel date conversion. Then add an IF statement to check the excel value is blank or not? 



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
vb2608
Frequent Visitor

Hi @abm 

 

Yes, that compose is doing the excel date conversion. Can you please elaborate on the IF condition as to where should I exactly place it?#

 

Thanks

Hi @vb2608 

 

Above the compose action add the IF statement to check the Excel date is blank.

 

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
vb2608
Frequent Visitor

Hi @abm 

 

Like this:

vb2608_0-1611693564882.png

Thanks

Hi @vb2608 

 

Please see below

 

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
vb2608
Frequent Visitor

Hi @abm 

 

To explain it further I should check whether the date column is null or not and then if it's null then I should just get rows from sql and then insert / update depending upon the result. And if the condition is false then I should add compose for excel date conversion. But if I follow this approach then I'll be back to square one where flow gives me an error when the field is empty. I am really confused.

 

Thanks

vb2608
Frequent Visitor

Hi @abm 

 

Thanks for helping me out and making me understand the concept behind all the working. I haven't seen someone so active on the community like you. And thanks for the time on call you gave me, it really solved my problem. 

 

Cheers

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

MPA Licensing.jpg

Ask your licensing questions at the Power Automate AMA!

Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.

Users online (1,942)