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.
Solved! Go to Solution.
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.
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
Next we need to iterate the loop to read each row. Below all the steps are going to be inside one loop.
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.
Above format expressions are as follows:
Next we need to find the Minutes. Yes more fun here 🤣
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.
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.
Next check the count of the record and determine there are duplicates or not?
The expression for the IF condition to check the count is as follows:
Finally our flow looks like this.
Here is my run result:
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.
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
So here is what i am thinking...
Trigger when a file is created
Get file metadata
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.
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
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.
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?
will probably do the trick also. Remember to wrap it inside single quotes
Hope it helps
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.
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.
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
I would use
without capital letters. Some examples here
Hope this helps
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?
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
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:
The explanation, in this thread
Hope this helps
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:
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.
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.
Three Super User rank tiers have been launched!
Features releasing from October 2020 through March 2021
We've updated and improved the layout and uploading format of the Power Automate Cookbook!
Fill out a quick form to claim your user group badge now!