question

cthivierge avatar image
0 Votes"
cthivierge asked cthivierge answered

Azure Logic Apps - Delete Excel Row

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
    93596-planner-1.png


  2. Loop into excel file to delete all rows
    93634-planner-2.png


azure-logic-apps
planner-1.png (6.7 KiB)
planner-2.png (18.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MayankBargali-MSFT avatar image
0 Votes"
MayankBargali-MSFT answered MayankBargali-MSFT edited

Hi @cthivierge

Updated:

@cthivierge 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.

95516-image.png

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.

93763-image.png

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.


image.png (2.6 KiB)
image.png (24.7 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks for your answer,

Actually, the Excel file has less than 100 rows and around 10 columns.

I will try adding another loop for deleting rows and see if it will work.

As i said, when you export Planner information into an Excel file, you can have the same information on more than one line if for example you have multiple users assigned to a single task. In that case, the same line will appear for each task assigned.

Thanks

0 Votes 0 ·

I tried several combinations of loop and it seems that the issue is related to the degree of parallelism within the "ForEach" loop.

If i let the default value of 20, around 40% or the rows still remains in the file. If i set the degree of parallelism to 1, it seems to work all the time.

But, there should be a way to delete all rows faster than that ? The production environment will have more than 15000 rows... it will take 4-5 hours just to delete rows...

Thanks


94786-planner-1.png


0 Votes 0 ·
planner-1.png (6.8 KiB)

@cthivierge I have updated my answer due to the limitation of 1000 characters in the comment. Please let me know if you have any queries or concerns.

0 Votes 0 ·
cthivierge avatar image
0 Votes"
cthivierge answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LubdhaDolas-7704 avatar image
0 Votes"
LubdhaDolas-7704 answered

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 .

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

cthivierge avatar image
0 Votes"
cthivierge answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.