Excel to sql server

[vb2608]

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?

[abm · Accepted 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.

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.

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.

Thanks

[vb2608]

Hi @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

[abm · Accepted solution]

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

[vb2608]

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:

[abm]

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? 

[vb2608]

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]

Hi @vb2608 

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

Thanks

[vb2608]

Hi @abm 

Like this:

Thanks

[abm]

Hi @vb2608 

Please see below

[vb2608]

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]

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