Azure data factory Data Flow: How to delete rows if Specific text in row values

Question

Azure data factory Data Flow: How to delete rows if Specific text in row values

Techie 0

Hi, I have a column in data flow that has Folder Path as Text as below:

FileSystem/Folder1/Folder2/Folder3/test.csv

FileSystem/Folder1/Folder2/Folder3/test01.csv

I want to delete rows that has 01.csv (want to keep only test.csv).

I tried using Alter row action but somehow I am unable to achieve what I want.

Can somebody help me do this? TIA

ShaikMaheer-MSFT 38,556 Reputation points Microsoft Employee Moderator

2023-06-20T08:15:55.6166667+00:00

Hi Techie, Just checking if below answer helps. If yes, please consider hitting Accept Answer button. Accepted answers help community as well. Please let me know if any further queries. Thank you.

5 answers

Your answer

ShaikMaheer-MSFT 38,556 Reputation points Microsoft Employee Moderator

2023-06-20T08:15:55.6166667+00:00

Hi Techie, Just checking if below answer helps. If yes, please consider hitting Accept Answer button. Accepted answers help community as well. Please let me know if any further queries. Thank you.

Answer 1

SSingh-MSFT 16,461 Moderator

Hi
Techie •,

Welcome to Microsoft Q&A forum and thanks for using Azure Services.

As I understand, you want to load specific rows to target using Data Flow and Azure Database for MySQL.

Let us know if the ask is different from above.

Considering the scenario described in the question, let's say we have a table in Azure Database for MySQL as "FilePathCheck" which has below data with columns as ID and Filepath:

User's image

Filepath stores value of Path of the file as mentioned in the question and we need to delete rows that has 01.csv (want to keep only test.csv).

For this purpose, we can connect to Source, then use "Alter Row" transformation in Data Flow Activity as shown below with expression inside "Delete If" as like(Filepath,'%01%') :

User's image

Like function with wildcard would delete rows having '01' and result would look like below in the Target Table 'FileCheckOutput':

User's image

Hope this helps.

If this answers your query, do click Accept Answer and Mark Helpful for the same. And, if you have any further query do let us know.

Thank you.

Techie 0 Reputation points

2023-06-12T08:28:30.2066667+00:00

hey, @SSingh-MSFT , thank you for your reply, looks like your solution is fine. But, I am doing something wrong. Please see the below scenario.

I have files in the folder like:

FileSystem/Folder1/Folder2/Folder3/test202306.csv

FileSystem/Folder1/Folder2/Folder3/test20230601.csv

I am storing the folder path in a column as a string.

here is my Data flow:

The Alter Row1 step is deleting "01.csv" as peer your solution (like(FilePath, '%01.csv%'))

but, unpivot step is not reading output from Alter row1 step. It is displaying all records.

I set up the incoming stream to Alter row 1. But still having the same issue.

The final goal is to store the data in SQL that doesn't have "01.csv" at the end in the FilePath column.
SSingh-MSFT 16,461 Reputation points Moderator

2023-06-12T08:37:11.08+00:00

Hi
Techie •,

Thanks.

Kindly share in details below information:

1). Source Dataset: whether it is DB or ADLS, if ADLS are you connecting to each file inside that Folder oath or the filepath location is stored n a csv as a single column.

2). Why Unpivot is used? what is the purpose if it, please elaborate.

3). How Data should be stored in Target SQL DB, what should be the schema?

4). Share sample Input Dataset.
Techie 0 Reputation points

2023-06-12T13:02:01.62+00:00
Hi, @SSingh-MSFT kindly look at the following info:

Source data is ADLS and connect with Wildcard path (Pointing to all files in all Folders dynamically like SystemFile/container/Folder/**/*.csv)

The data in csv as below:

I am trimming the top 4 rows and unpivoting the data

File will have Day 1 to Day 31 by the end of the Month.

4. Schema as below:

Columns for SQL table will be a. Column 1, b. Column 2, c. Days (Unpivoting ), d. Values (will be the numbers Type(double)), e. FilePath

See the above data table.

and also, the csv file will update every day and add the data (like Day 12, Day 13, and so on). So, adding a column every day. But previous data won't change. At the moment I am using only Insert which is doubling the rows. How can I Update/Upsert the data to the SQL using the same file to avoid the duplication of data?

Change data capture from Source options is not working.

am sorry, I am new to Azure Data Factory.

Thank you so much..! :)

Answer 2

ShaikMaheer-MSFT 38,556 Microsoft Employee Moderator

Hi Techie,

Thank you for posting query in Microsoft Q&A Platform.

After going through all your above comments, basically you don't want to see row which contains path at end as 01.csv in the unpivot transformation. For this you need to use Filter transformation not alter row transformation. In the filter transformation, use condition as like(FilePath, '%01.csv%') inside Filter on field.

That way you will not see that row in Unpivot transformation.

Please consider checking videos to understand more about these transformations.

Filter Transformation in Mapping Data Flow in Azure Data Factory

Alter Row Transformation in Mapping Data Flow in Azure Data Factory

Hope this helps. Please let me know if any further queries.

Please consider hitting Accept Answer button. Accepted answers help community as well.

ShaikMaheer-MSFT 38,556 Reputation points Microsoft Employee Moderator

2023-06-19T05:45:19.9433333+00:00

Hi Techie, Just checking if above answer helps. If yes, please consider hitting Accept Answer button. Accepted answers help community as well. Please let me know if any further queries. Thank you.

Answer 3

Deleted

This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Comments have been turned off. Learn more

Answer 4

Deleted

This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Comments have been turned off. Learn more

Answer 5

Deleted

This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Comments have been turned off. Learn more

Share via

Azure data factory Data Flow: How to delete rows if Specific text in row values

5 answers

Your answer