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 III
Super User III

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 blogPower Automate Video Tutorials

View solution in original post

abm
Super User III
Super User III

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 blogPower Automate Video Tutorials

View solution in original post

11 REPLIES 11
abm
Super User III
Super User III

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 blogPower Automate Video Tutorials

View solution in original post

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

abm
Super User III
Super User III

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 blogPower Automate Video Tutorials

View solution in original post

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

 

abm
Super User III
Super User III

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

abm
Super User III
Super User III

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 blogPower Automate Video Tutorials
vb2608
Frequent Visitor

Hi @abm 

 

Like this:

vb2608_0-1611693564882.png

Thanks

abm
Super User III
Super User III

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 blogPower 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
MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

Top Solution Authors
Users online (64,642)