How to trim data in Azure Data Factory source with csv file format without using Dataflows and Databricks or any other transformation tool

0
votes

In source csv file the data contains white spaces. How to remove those without using any transformation tool and just using Azure Data Factory. I tried "For each" activity on copy activity but the For each @items is of JSON array and string functions doesn't apply on it. Also, Data factory does not support custom functions and expressions. Is there any way to remove the white spaces from the source or during the copy process to the sink? Source and Sink are "Azure Files".

2
azure-data-factory-2
what's the data look like, some thing like 'data data' ? – Leon Yue
If not all the csv data contains white spaces, as I know about DF and per my experience, it's impossible to achieve that! Using data flow or others tools is very easy. – Leon Yue
data looks like "ABCD ", "Software as Service(SaaS) " having white spaces. Due to some limitations I have to only use ADF without Datflows and any other transformation tool in Azure. Is there any way to achieve this using ADF only. – Yogesh Kulkarni
I'm afraid not， there isn't. – Leon Yue
Hi @Yogesh Kulkarni, can I post it as answer? – Leon Yue

2 Answers

0
votes

The most performant way to achieve this would be to temporarily stage the data in Azure SQL or Cosmos DB and then trim each column with an explicit SELECT statement as the source of the subsequent Copy activity moving the data to your sink file.

0
votes

If not all the csv data contains white spaces, as I know about DF and per my experience, it's impossible to achieve that data conversion only with Copy active! Using data flow or others tools is very easy.

There isn't a way to achieve this using ADF only or directly.

HTH.