cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Microsoft
Microsoft

Import Excel Table to SharePoint List - Check for Duplicates

I have a program that exports a list of closed cases.

This export is in excel with a table already.

I have created a list in SharePoint.

The list columns match the columns in my excel file.

 

There are two things I need to accomplish...

1.  The excel file will have rows that are already in the list.  I need to somehow check for these, do not import these duplicates, and only import new data.

2.  Do not import blank rows, which I actually already have this worked out (just mentioning it).

 

I can import the data into SharePoint, I have a flow already setup.  but I need to modify it so that when we import, we check for the duplicates.

Each column will have the exact same data as the source, so it is just not based on one column.

For example, if the excel file has column A, B, C, D, E, and row 23 has the EXACT same data in each column, as a row in the SharePoint list, we do not import this row.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Import Excel Table to SharePoint List - Check for Duplicates

Hi @lardo5150 

 

Not the straight forward job when excel stored date and time. In your case time is also important. So we have some work to do here. It  took me 4 hours to figure this out 🤣 Here are the details:

 

First I have created a SharePoint list similar like yours.

 

image.png

 

Here I haven't added all the fields but essential fields which you want to filter against. All fields are string except the ClosedDateTime is DateTime format.

 

Next we can start building the flow now.

 

Here I am using the manual trigger then reading the Excel rows

 

image.png

 

Next we need to iterate the loop to read each row. Below all the steps are going to be inside one loop.

 

image.png

 

First we need to find the date. Later we need to find Hours and Minutes. Yes not the straight forward job here.

 

To find the date (dd-mm-yyyy) we need the next two compose statements.

image.png

 

Above format expressions are as follows:

first(split(outputs('Excel_Closed_Date_Time'),'.'))
formatDateTime(addDays('1900-01-01',sub(int(outputs('Excel_Closed_Date_Time_Numeric_Part')),2)),'yyyy-MM-dd')
 
Next we need to find the Hour. Here we have 3 compose and expressions.
 
image.png
 
The first compression I have put a dot(.) infront of the expression last(split(....)). If you look carefully you can see from the image. Here is the expression. 
last(split(outputs('Excel_Closed_Date_Time'),'.')) 
The idea is we want as a decimal.
 
mul(float(outputs('Excel_Closed_Date_Time_Decimal_Part')),24)
first(split(string(outputs('Closed_Date_Time_Hour_Minutes')),'.'))

 

Next we need to find the Minutes. Yes more fun here 🤣

 

image.png

 

Again the first compression I have put a dot(.) in front of the expression last(split(....)). If you look carefully you can see from the image. Here is the expression. 

last(split(string(outputs('Closed_Date_Time_Hour_Minutes')),'.'))
Next expressions are as follows:
mul(float(outputs('Closed_Date_Time_Minutes_Decimal_Part')),60)
first(split(string(outputs('Closed_Date_Time_Minutes')),'.'))
 
So at this stage we have Date, Hour and Minutes. We are nearly there. I promise 😃
Next we have to construct date and time. Here I am using two compose action steps.
 
image.png
 
The expression for the above is as follows:
 
concat(outputs('Closed_Date_yyyy-mm-dd'),' ',outputs('Closed_Date_Time_Minutes_HH'),':',outputs('Closed_Date_Time_Minutes_MM'))
 
formatDateTime(outputs('Closed_Date_Time_-_Construct_datetime'),'yyyy-MM-ddTHH:mmZ')

 

Now we have DateTime no more decimal messy numbers in hand 🤣. Next is the easy part. Filter SharePoint list against date and other two paramters.

 

Here are the steps for that.

 

image.png

 

Next check the count of the record and determine there are duplicates or not?

image.png

 

The expression for the IF condition to check the count is as follows:

length(body('Get_items')['value'])
 

 

Finally our flow looks like this.

 

image.png

image.png

 

Here is my run result:

image.png

 

I ran again and it did worked as expected. All good expect the last record (User2) time is showing as 15:16 but it is stored in the database as 14:16. So the comparison is working perfectly. We did changed the time today (UK Summer time). This could be related to that or UI bug or my Sharepoint might be lacking some service pack updates who knows. I am not bothered about that now. 🤣 No more duplicates that's more important. 

 

Let me know how it goes.

 

This must be one of my longest thread and a challenging one. 😃

 

Any questions please let me know.

 

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

View solution in original post

44 REPLIES 44
Highlighted
Dual Super User III
Dual Super User III

Re: Import Excel Table to SharePoint List - Check for Duplicates

Hi!

Can you share a screenshot from your current Flow design?

Anyway, this thread provide an example I guess very similar to what you're looking for

https://powerusers.microsoft.com/t5/Building-Flows/Duplicates-created-when-creating-new-items-in-Sha...

Thanx!



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted
Microsoft
Microsoft

Re: Import Excel Table to SharePoint List - Check for Duplicates

So here is what i am thinking...

Trigger when a file is created

Get file metadata

Rename file

List rows

Apply to each with a condition, if the rows are blank, do not import them into the SharePoint List.

I need to add a check for duplicates somewhere.

 

Flow 1.JPGFlow2.JPGFlow3.JPG

Highlighted
Dual Super User III
Dual Super User III

Re: Import Excel Table to SharePoint List - Check for Duplicates

@lardo5150 

You almost got it! The method to check duplicates is detailed on the thread i shared before (explained step by step with screenshots). Did you have a look to it?

 

Assigning the appropriate Odata  expression as 'Filter Query' input onyour 'List rows present in a table' should help you identify whatever matching row with your current event. Now, the condition action block evaluates if any match found, if no... create item

Thanx!



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted
Microsoft
Microsoft

Re: Import Excel Table to SharePoint List - Check for Duplicates

so that is the problem, I am never able to get the Excel Dynamic Content to compare to the SharePoint dynamic.

In that thread and screenshot, he was able to add the Excel Dynamic content of Customer name to the filter, I am not able too.

 

I even tried create two AND groups, but I can never get the excel dynamic content to populate, it only shows me SharePonit.

 

compare2.JPG

Highlighted
Dual Super User III
Dual Super User III

Re: Import Excel Table to SharePoint List - Check for Duplicates

Hi!

Aarragh! Sometimes Flow editor is a nightmare.

You already manage to add the target 'Dynamic content' in a late step, right? 'Create item'... If so, copy paste it. Some browsers allow to do this

If it does not work... what's the name of your Excel column?

@{item()?['NameOfYourExcelColumn']}

will probably do the trick also. Remember to wrap it inside single quotes

Hope it helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted
Microsoft
Microsoft

Re: Import Excel Table to SharePoint List - Check for Duplicates

Ok, I got the excel dynamics to populate.

Weird thing, I had to point it to the actual file, set it up, and then i switched the file name back to dynamic.

Weird, but it worked.

 

From there, I did apply all to the excel table, pulled ALL items from the Sharepoint list, cause I need to compare each column with the corresponding column in Excel.

I then do a condition where I do two AND groups

first one is where all the columns are not blank

second one is supposed to be where the excel column matches the Sharepoint column.

 

It keeps autopopulating it with onedrive.  When I change it from that to the Sharepoint column, Flow creates a SECOND Apply all.

That picture is below last.

Now that is all fine, as it all looked ok.  I ran a test and ran a normal flow, it succeeded, but nothing was added to my SharePoint list.

 

compare3.JPGFlow2.JPGFlow3.JPG

Highlighted
Microsoft
Microsoft

Re: Import Excel Table to SharePoint List - Check for Duplicates

With it populating with onedrive, I can't save it, it gives me an error.

So I will populate it with the sharepoint, let it create the second apply all and show you.

 

error.JPG

Highlighted
Microsoft
Microsoft

Re: Import Excel Table to SharePoint List - Check for Duplicates

Here it is when Flow creates the apply all 2 by itself.

 

I ran it like this but nothing was created in the List.

 

apply1.JPGapply2.JPGapply3.JPG

Highlighted
Dual Super User III
Dual Super User III

Re: Import Excel Table to SharePoint List - Check for Duplicates

@lardo5150 

I think the problem is... you want to design the complex scenario since the beginning, and I do not recommend such approach

Try to design a simple scenario, once it works add complexity

 

So if you agree, remove this weird condition with tons of rules, and add instead the simple condition recommended in the post I shared with you. Verify length of filter array, if 0 create item. That's all for the moment.

 

Use excel rows with no empty fields, once you are sure it works, we will try to analyze what to do if the excel row has an empty field

 

Hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted
Microsoft
Microsoft

Re: Import Excel Table to SharePoint List - Check for Duplicates

For that query, how do I add multiple, do I just use AND?

 

For example:

 

query.JPG

Highlighted
Dual Super User III
Dual Super User III

Re: Import Excel Table to SharePoint List - Check for Duplicates

Hi!

I would use

and

without capital letters. Some examples here

https://flow.microsoft.com/fr-fr/blog/advanced-flow-of-the-week-filtering-with-odata/

Hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted
Microsoft
Microsoft

Re: Import Excel Table to SharePoint List - Check for Duplicates

I am running into an error where it says it can't find the column, but the column is clearly there in the spreadsheet and in SharePoint.

 

Any idea what i am doing wrong here?

 

columns.JPGget items.JPGSharepointListColumns.JPG

Highlighted
Dual Super User III
Dual Super User III

Re: Import Excel Table to SharePoint List - Check for Duplicates

Hi!

My suggestion is to inspect your Column internal name in 'List rows from an excel table' output

 

BTW it 's the first time I see 8? 9? and operators in the same Filter Query expression. I heard no more than 2 or 3 where supported, so it's good to know the Earth did not explode yet hahaha

 

Remember my motto: try the simple, once running add complexity

 



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted
Microsoft
Microsoft

Re: Import Excel Table to SharePoint List - Check for Duplicates

I am getting a date and time error.  Ok, at this point I have no idea what is happening here.

Here is what the date looks like in the excel file.

closed date.JPGstring error.JPG3query.JPG

Highlighted
Microsoft
Microsoft

Re: Import Excel Table to SharePoint List - Check for Duplicates

So i may need to change the date an time in Excel?

Looking at the output from the failed run for the List Rows, i see this:

 

  "CreateDateTime""43717.1725925926",
    "ClosedDateTime""43766.2760532407",
 
What is that?
Highlighted
Dual Super User III
Dual Super User III

Re: Import Excel Table to SharePoint List - Check for Duplicates

@lardo5150 

The explanation, in this thread

https://powerusers.microsoft.com/t5/General-Power-Automate/Flow-not-triggering-when-scheduled/td-p/5...

 

Hope this helps



Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Highlighted
Microsoft
Microsoft

Re: Import Excel Table to SharePoint List - Check for Duplicates

ok, at this point I dont know what I am doing.

Could use a little hand holding.

I was trying to add the initialize variable, but it would only add it as a top level.

Ok, so I tried that and Flow automatically created an apply to each.

My thinking was, I needed to do this for the CreateDateTime column and more importantly the ClosedDateTime.

So I created two.

I then used that code you mentioned for the expression, and you can see where I used it below along with the error:

Here is what I was adding into the expression:

addDays('1900–01–01T00:00:00Z',sub(int(variables('ConvertDateTime')),2))
 
variable3.JPGVariable4.JPGvariable1.JPG

 

Highlighted
Microsoft
Microsoft

Re: Import Excel Table to SharePoint List - Check for Duplicates

ya man sorry, I need some hand holding as I am past my knowledge level.

Reading some other articles plus what you sent me.

Correct me if I am wrong, but it looks like I need to convert the values in the two Excel columns (createdatetime and closeddatetime).

 

But i am unsure how to correctly convert these, for each line, for the query (ClosedDateTime in the query is super important) and then importing them into sharepoint.

 

I tried to create a variable, but it keeps creating an apply to each for it.  I do not understand that either.

Highlighted
Microsoft
Microsoft

Re: Import Excel Table to SharePoint List - Check for Duplicates

Ok, throwing in the towel, I need your help on this one.

 

I tried it a bunch of different ways.  Below is the latest and last.

I tried to initialize a variable at the beginning, then set it once I got the excel data.

The problem I ran into was I was using your expression, but it would not let me put in the excel dynamic column name of CreateDateTime or ClosedDateTime.  They were never available if I used expression.

So I figured ok, let me put them in there first, then paste your expression around it.

i am sure this is wrong, obviously, but I was getting desperate and trying anything.

variable7.JPGvariable6.JPGvariable5.JPG

Helpful resources

Announcements
firstImage

Super User Program Update

Three Super User rank tiers have been launched!

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

Top Kudoed Authors
Users online (7,244)