Azure Data Flow : how to eliminate columns with empty names in csv file

Question

Azure Data Flow : how to eliminate columns with empty names in csv file

javier cohenar 211

A supplier sends us CSV files regularly that sometimes include empty column names like this:

columnName1, columnName2,,,,,

Downstream process (Data Flow Activity) requires that the columns have no empty names (otherwise it will fail).

In order to achieve this, I have (unsuccessfully) tried:

in Data Flow Activity: add rule based mapping (Select operation) with name!=""

Please advise on how to best deal with this situation

Nandan Hegde 36,151 Reputation points MVP Volunteer Moderator

2023-07-27T03:25:53.4733333+00:00

Hey,

Is there a specific need that this be done via dataflow only? Based on your input we can help further.

Because you can also use regular activity and pipeline expressions to achieve it.

Alternative:

Use get meta data activity on the file and leverage the structure option to get the column details /header of the file.

Check whether it contains anything like Column, if yes : take necessary next steps
javier cohenar 211 Reputation points

2023-07-27T03:47:30.5+00:00

Thanks @nandan hedge. Could you elaborate more on how to go from the output of the GetMetdata Activity to a file with only the columns needed?
QuantumCache 20,366 Reputation points Moderator

2023-07-27T05:59:54.11+00:00

Hello @javier cohenar,
You may also use Select Transform with Rule Based Mapping as I have showed in the above comment, I hope that helps with initial query.

Accepted answer

1 additional answer

Your answer

Nandan Hegde 36,151 Reputation points MVP Volunteer Moderator

2023-07-27T03:25:53.4733333+00:00

Hey,

Is there a specific need that this be done via dataflow only? Based on your input we can help further.

Because you can also use regular activity and pipeline expressions to achieve it.

Alternative:

Use get meta data activity on the file and leverage the structure option to get the column details /header of the file.

Check whether it contains anything like Column, if yes : take necessary next steps
javier cohenar 211 Reputation points

2023-07-27T03:47:30.5+00:00

Thanks @nandan hedge. Could you elaborate more on how to go from the output of the GetMetdata Activity to a file with only the columns needed?
QuantumCache 20,366 Reputation points Moderator

2023-07-27T05:59:54.11+00:00

Hello @javier cohenar,
You may also use Select Transform with Rule Based Mapping as I have showed in the above comment, I hope that helps with initial query.

Answer 1

QuantumCache 20,366 Moderator

Hello @javier cohenar,

In order to achieve this, I have (unsuccessfully) tried:

in Data Flow Activity: add rule based mapping (Select operation) with name!=""

Could you please add more info on what is the blocker here? any specific scenario failing the Select Transformation?

Hello @javier cohenar,

Below shows the Select Transformation : Rule Match expression.

I have used 2 Rules, please check!

User's image

javier cohenar 211 Reputation points

2023-07-27T14:50:32.36+00:00

hi Satish your solution works perfectly for me. thanks. My error was in the rule based expression in the Select activity. I followed your indications and corrected them.
QuantumCache 20,366 Reputation points Moderator

2023-07-27T15:17:24.0266667+00:00

Hello @javier cohenar Glad to ear the issue is now unblocked. Happy to help you on this forum!

If the response is helpful, please click "Accept Answer" and Click 'Yes'. So that we can close this thread.

Answer 2

Hey,

You can use the Get Meta data activity on the file dataset :

User's image

And select the structure list

Enable the 1st row as header in the dataset :

User's image

and add a character within the column delimiter which you wont expect in file.

Output :

User's image

You can compare this output with the expected column list parameter you have.

If these 2 match, proceed ahead else take necessary actions.

Note:

You can also use Contains function on the output to check whether the list has name name like column

@string(contains(Getmetadatastructure,'column'))

If there are empty columns use a copy activity to copy specifc columns from the source file to another file and this you can use the mapping section of copy activity

w.r.t Dataflow, you can use the below reference :

https://stackoverflow.com/questions/63781415/remove-specific-columns-using-azure-data-factory

Share via

Azure Data Flow : how to eliminate columns with empty names in csv file

1 additional answer

Your answer