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?
Solved! Go to Solution.
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.
Above I am using an int() expression to filter the records. Here in my expression the Id is an integer. Below is the expression.
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.
To find the length of results from SQL connector - Get Rows, I have used the below expression
If you liked my response, please consider giving it a thumbs up
Proud to be a Flownaut!
Learn more from my blogHi @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
If you liked my response, please consider giving it a thumbs up
Proud to be a Flownaut!
Learn more from my blogHi @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.
Above I am using an int() expression to filter the records. Here in my expression the Id is an integer. Below is the expression.
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.
To find the length of results from SQL connector - Get Rows, I have used the below expression
If you liked my response, please consider giving it a thumbs up
Proud to be a Flownaut!
Learn more from my blogHi @abm
Thanks for the above solution. I have adapted your solution to the following flow.
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.
Also, when I insert a value to one of the columns then I am getting the following error.
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
If you liked my response, please consider giving it a thumbs up
Proud to be a Flownaut!
Learn more from my blogHi @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:
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?
If you liked my response, please consider giving it a thumbs up
Proud to be a Flownaut!
Learn more from my blogHi @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
If you liked my response, please consider giving it a thumbs up
Proud to be a Flownaut!
Learn more from my blogHi @vb2608
Please see below
If you liked my response, please consider giving it a thumbs up
Proud to be a Flownaut!
Learn more from my blogHi @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
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