How to remove Line Breaks from Excel file in ADF

Priya Jha 866

Hi All,

I have a scenario wherein my Excel file has data with line breaks. If i convert this excel file to CSV, even my CSV file contains line break in data and i am getting error while reading this CSV through PolyBase table in Azure Synapse.

I wanted to know how to remove line breaks from excel file before converting it to CSV using ADF or is there a way to remove line breaks from CSV file.

Row 0 Reputation points

2024-04-12T18:51:17.38+00:00

Here's how I resolved my problem.

I had CRLF for a linefeed (Windows convention) but LFs within the data. Originally, I set the dataset to parse using CRLF. Replacing \n with an empty string wasn't working.

However, I needed to parse the dataset using CR only. Parsing CRLF had the effect of removing all line breaks from being evaluated in the search. With this changed, the Replace function worked.

2 answers

HarithaMaddi-MSFT 10,126 Reputation points

2020-09-10T06:30:32.397+00:00

Hi @Priya Jha ,

Thanks for posting the query.

This can be achieved by using replace function of derived column in dataflow as shown below in GIF and snap.

Hope this helps! Please let us know if it is not aligning with the requirement or for further queries and we will be glad to assist.
Please sign in to rate this answer.
Priya Jha 866 Reputation points

2020-09-11T06:57:27.213+00:00

Hi @HarithaMaddi-MSFT ,

Thanks for your reply.

Is there is a way to achieve this by using copy activity and some other activity instead of using mapping data flow?

HarithaMaddi-MSFT 10,126 Reputation points

2020-09-11T09:37:21.28+00:00

Hi @Priya Jha ,

Thanks for sharing the query. Copy activity does not support this feature. Mapping dataflows are developed to enable huge number of transformations that enable ETL experience in Azure Data Factory. This can be alternatively achieved by using LogicApp or custom code can be written using Azure Functions and please find a reference document for the same. Kindly let us know if there is any specific reason for not using mapping data flow as that will help us in providing appropriate solution. If you are looking for feature in copy activity or in pipeline, I would recommend you to post an idea to enable this feature in feedback forum. All the feedback you share, is closely monitored by the Data Factory Product team and implemented in future releases.

HarithaMaddi-MSFT 10,126 Reputation points

2020-09-14T07:52:52.12+00:00

Hi @Priya Jha ,

We have not received a response from you. Please let us know if above answer is helpful. Otherwise, let us know and we will continue to engage with you on the issue.

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members

HarithaMaddi-MSFT 10,126 Reputation points

2020-09-15T08:21:15.777+00:00

Hi @Priya Jha ,

We still have not heard back from you. Following up to check if above answer is helpful. Otherwise, let us know and we will continue to engage with you on the issue.

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members
Sign in to comment
Dinesh Prajapati 126 Reputation points

2023-01-10T06:07:42.37+00:00

Hi team.. this is not achived for CSV files. It works only for xlsx file.

Can someone tell me how we can do this for csv file ?
Please sign in to rate this answer.

0 comments No comments
Sign in to comment