Azure Data Factory - delete row in a csv

Question

I am having some issues with the ADF/Data Flow/Pipeline to delete one row using alterRow.

I am filtering by ID=1 and deleting ID=1 row. How to include rest of the rows in my final output. I have only 10 rows.

Even if there is a x mark on the row that is getting deleted, the final sink output to the container, is including the row that was showing deleted x mark.

How will I get the final csv without the ID=1 row and include rest of the rows.

Answer 1

Hello @Gunasekar-Desaiyan

To exclude the row with ID=1 from the final output, you can add a Filter transformation after the Alter Row transformation. In the Filter transformation, you can filter out the row with ID=1 using the following expression:

ID != 1

This expression will keep all the rows except the row with ID=1. You can then connect the Filter transformation to the sink to output the final CSV file without the row with ID=1.

Here's an example of how your Mapping Data Flow can be configured:

1. Add a source to read the CSV file.
2. Add an Alter Row transformation to delete the row with ID=1.
3. Add a Filter transformation to exclude the row with ID=1.
4. Connect the Filter transformation to the sink to output the final CSV file. Please note that the Alter Row transformation only marks the row for deletion and does not actually delete the row. The row will be deleted when the data flow is executed in a pipeline.

I hope this helps

Answer 2

Hi @Gunasekar-Desaiyan ,

Thankyou for using Microsoft Q&A platform and thanks for posting your query here.

I understand that you are trying to delete one row out of 10 records in the source data and dump the remaining 9 into the delta sink.

Kindly make sure in sink settings, you have selected 'Allow Insert' and 'Allow delete' options :

Hope it helps. Kindly accept the answer by clicking on Accept answer button. Thankyou