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

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

PA Survey Carousel Image.png

We want to hear from you!

If you are a small business ISV/Reseller, share your thoughts with our research team.

Users online (1,033)