Azure Logic Apps - Delete Excel Row

Question

Hi,

We do have a Logic App to export Planner tasks into an Excel file (into sharepoint). This works well.
But before running the next export, we must delete all rows in the current Excel file or we will have duplicates entry.
I don't want to delete and create another Excel file because Logic App will fail even if the file has the same name...

So i added few steps in the Logic App to list Excel Rows and delete all of them if the Title column is Not Equal To blank.
This works well for all rows except for duplicates rows.

Yes... there is duplicates rows because in Planner, you could have more than 1 user assigned to a task so it create 1 row per user and all the other columns are identical...

So the question is how to delete all rows in the Excel file without replacing the Excel file ?

Here is my logic app steps

1. List Excel rows
   
2. Loop into excel file to delete all rows
   

Answer 1

Hi @Anonymous

Updated:

@Anonymous The delete a row action will try to delete the single row that matches in your excel. In case you are running the "for loop" in parallelism then there could be a match that the same row will try to be deleted from excel therefore the behavior that you have observed is expected as there is no uniqueness in any of the excel records that can be used for the deletion.

The alternative option would be looping it until all the rows are deleted and run it in the max degree of parallelism. In the worst condition where all rows are duplicated of same record it will be not efficient.

Another workaround would be using the "run script" action and write your custom script that will have the logic to delete all the rows from your excel table. You can refer to this document to know more about scripting fundamentals.

----------------------

I have tested the scenario and I can see that the duplicate rows are also deleted from the excel sheet.

If some of your rows are not deleted then I will suggest you to first verify whether your excel sheet has more than 500 rows as per the excel connector limitation.

The connector retrieves rows for 500 columns maximum in the List rows present in a table action. Rows for first 500 columns are returned by default. You can provide comma-separated list of specific columns to retrieve in Select Query parameter.

If this is the case then you need to use List rows present in a table action specifying the Select query parameter.

If your excel has less than 500 rows then the condition that your specified condition might be false for some of the rows. You can navigate to run history of the logic app and compare the number of item return by your "List rows present in a table" action with the count of True condition executed in your logic app. For testing purposes, you can create variable inside your condition and increase that by one rather than manually counting it. If this is the case then you need to use some other column in your condition and delete it using that value whose value is never empty.

Hope the above helps you to resolve your issue. Feel free to get back to me if you have any queries or concerns.

Please 'Accept as answer' and ‘Upvote’ if it helped so that it can help others in the community looking for help on similar topics.

Answer 2

Thanks for the update.

Actually, we decided to export Planner data into a csv file instead. It's a lot easier to manage and you can easily replace the file compare to the excel file which is a lot harder to do.

Thanks

Answer 3

How are you doing this? deleting row from csv files?

I have a condition where I have to delete the ignore the row where there is a certain value .

Answer 4

Hi,

As i mentioned, we decided to export Planner into a CSV file and replace the file each time. It's a lot easier to do.

At first, our goal was to export Data into an Excel file. But we had a lot of issues to deleting Excel rows so we decided to create an CSV file instead.

CSV file doesn't need to be created before compare to an Excel file so that's why it's a lot easier to do.

hth