apply trim before copy using azure data factory

Question

I have a pipeline that copies files from azure datalake to azure SQL

• Gets Meta Data of files in a specific path in Datalake

• Loops through the output and copies the file into a database table.

  • file A.csv is copied to dbo.[A]
  • file B.csv is copied to dbo.[B]

Question

• Is there a generic way to define a trim on the column values being copied ?

Answer 1

To apply a trim rule across ALL string columns in a dataset in ADF, use Mapping Data Flows with a column pattern.

In a Derived Column, set your matching rule to "type == 'string'".

In the Column Name, keep it the same using $$.

In the Value, set trim ($$,'...')

Reference docs:

Matching Patterns: https://learn.microsoft.com/en-us/azure/data-factory/concepts-data-flow-column-pattern

Derived Column: https://learn.microsoft.com/en-us/azure/data-factory/data-flow-derived-column

Answer 2

trim feature is supported by ADF expressions.(link)

e.g.:

trim('!--!wor!ld!', '-!') -> 'wor!ld'

Trims a string of leading and trailing characters. If second parameter is unspecified, it trims whitespace. Else it trims any character specified in the second parameter