Unanswered

How to filter excel file before adding to sql database

(0) Share

Report

Posted on by BPatache

Hi,

Each day I have a email that creates an excel table from an attached file and then I have a flow that is trigger when a new excel file is created in SharePoint

Then the table is filtered by "@" because I need to use only the lines that have values

And then the data is saved into sql database

The problem that I have is that I need to add another filter

, if emploieeid (sql) is found in Emploiee Id (in excel file) than don't add to sql database

At the moment if the excel file is created with same data from a day before, is imported in sql and creates duplicates 😞

This is how my flows looks like

Kind regards

Bogdan

Categories:

Building flows

I have the same question (0)

All responses (4)

Answers (0)

v-yueyun-msft on at

Like (0)

Report
Hi , @BPatache
According to your description, you want to just update the row if the emploieeid is found in your Sql database.
First , you need to get the distinct emploieeid in your sql database. [You can use the Select action to get the id array and use the union() function to get the distinct of the emploieeid in your sql database].
Scond , you can filter your Excel file like this:
@and(contains(item()?['WorkEmail'], '@'),equals(contains(outputs('Compose_2'), item()?['emploieeid']), false))
You need to change the columnName in your side .

Best Regards,
Yueyun Zhang

Was this reply helpful? Yes No
BPatache 8 on at

Like (0)

Report

Hi Yueyun,

Thanks for you replay,

Sorry if I wasn't to precise in my explanation, I need the exact opposite than what you said
Update Sql only if the employeeid is not found

Is on for an appraisal app and I need one record per employee per year.
I will se up an flow to delete the records older than 6 months to make sure the employeeid is not there for the next appraisal.

I will try to replicate your option with "not(contain())"

Thanks
Bogdan

Was this reply helpful? Yes No
v-yueyun-msft on at

Like (0)

Report
Hi , @BPatache
Thanks for your quick response ! Sorry i may make a mistake in my reply.
equals(contains(outputs('Compose_2'), item()?['emploieeid']), false)
The code is used to judge if [emploieeid] in your Excel file is not exist in your sql database.
If yes: emploieeid do not contain in sql database.
If no: emploieeid exist in sql database.

Best Regards,
Yueyun Zhang

Was this reply helpful? Yes No
BPatache 8 on at

Like (0)

Report

Hi Yueyun,,

I have tried your option but I think I am doing something wrong as the filter doesn't work.
I had an excel with 4 lines witch2 of the lines already existed in my sql database.

After I run the flow, all 4 lines were added to my sql database

This is what I did
get rows from sql
created the select function
created the compose action

Created the filter function
and then apply to each /insert row

Regards
Bogdan

Was this reply helpful? Yes No