Check if item already exists Sharepoint List is failing with JSON Parse output comparison. CSV to JSON to Sharepoint List

[debarbanson]

Hi all, been cracking my head for hours and hours and would appreciate any help!

I have a flow where I take the input from a CSV file from Sharepoint, convert it into JSON format and then push that output towards a Sharepoint List. This works all fine the first time I run the flow and 50 items are created in my Sharepoint list.

When I run the flow again (since the CSV will get updated every hour) I only want to update existing items already in Sharepoint and create a new item if that item is not already in Sharepoint.

I'll try to be as detailed as possible including pictures from the steps/outputs and I will start with the flow which just creates the Item in Sharepoint, so without checks.

so far so good. To give you an indication of the output of Parse JSON see below. I've hidden some confidential information and the field Ordernummer (Ordernumber in English) is a unique number.

Do notice the Ordernumber shows as "\n2.200.501" where the number is 2.200.501. Not sure where the \n comes from.

If I then look into the Create Items output, it shows the following. Do notice here the Ordernumber comes in as expected, so without the \n in front.

Sharepoint List then shows the following and also there the Ordernumber comes in without \n in front, as expected.

So far so good.

Given the flow is run multiple times a day, I want to achieve that existing Ordernumbers in Sharepoint and the JSON body get updated in Sharepoint and that new Ordernumbers in the JSON body which do not exist yet in Sharepoint get created.

To do so I have adjusted the flow as follows:
First after the step Parse Json I include a new Step Get Items from Sharepoint and provide a Filter query to check if the Ordernumber in the Sharepoint list matches with Ordernumbers in the Parse JSON output.

Then within this Apply to Each 2 step, I also add a condition to check the length of the output of the Filter query as follows:
length(outputs('Get_items')?['body/value']) equals to 0

If the result would be If yes, then I want to create an item, assuming this then indicates no matches were found.
If the result would be If no, then I want to update that specific item, assuming this then indicates there is a match found and as such length is > 0.
I have the following in if Yes/No:

and the statement I am using in Update item to determine the ID is as follows: outputs('Get_items')?['body/value'][0]?['ID']

Unfortunately the result once I run the flow again is that I get duplications in Sharepoint, so somehow the query filter I applied to check if the Ordernumber from the Sharepoint List is equal to the Ordernumber in the JSON Parse output is not working. I am clueless. 

I feel it might have to do with the \nOrdernumber in JSON Parse compared to just Ordernumber in Sharepoint but I do not know how to tackle that issue. Is there anyone who can help me fix this? Your help is much appreciated!!!

See Sharepoint result below after running the flow 3 times in total:

[v-dezhili-msft · Accepted solution]

Hi @debarbanson ,

Do you want to use the Get items action to get all the data that exists in your json array?
Actually CSV files are essentially a string, each row of data will have "\r\n" after it.When you operate on strings, you should remove the "\r\n" using the decodeUriComponent().
Please note that

decodeUriComponent ('% 0D')='\r'
decodeUriComponent ('% 0A')='\n'

split(base64ToString(outputs('Get_file_content_using_path')?['body'].$content),decodeUriComponent('%0D%0A'))

Best Regards,

Dezhi

 

[debarbanson]

Hi @v-dezhili-msft ,

Thanks, this was indeed what was causing the issue. Turned out in the end I created the issue myself...
The original file I loaded had a Macintosh decoderURIComponent, according to Notepad.
I altered the file in Excel, to add a record, and then it switched to Windows decoderURIcomponent and then all of the sudden it didn't work anymore. Took a long time to figure out the change 🙂