Remove newlines inside a text field of dynamic csv columns

Question

question

AMJ-2211 asked Apr 28, '22 AMJ-2211 answered May 2, '22

Remove newlines inside a text field of dynamic csv columns

As mentioned in the title there are newlines ({CRLF}) embedded in text fields of csv files such as

,,,,,"this is text field {CRLF} which finishes here",,,,,

Normally you will use a function like: regexReplace(<Column name>, ,"([\r\n|\r|\n]+)",, ' ') https://docs.microsoft.com/en-us/azure/data-factory/data-flow-expression-functions to replace {CRLF} with a space. The issue is the <Column name> is dynamic. Here is the simplified extract of a csv file:

As one can see the text field denoted by a pair of double quotes has span multiple lines as the result of the embedded {CRLF} within the text field. The dynamic <Column name> means you don't know which column in a csv file is a text field. Also there would be multiple csv files through a single pipeline. The schema in each csv file is different. Please chip in your thoughts.

azure-data-factory azure-databricks

image.png (20.4 KiB)

· 2

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

PratikSomaiya · Apr 28 at 04:47 AM

@AMJ-2211 : Can you let me know how the CSV is generated, is the CSV is generated from a database as source via query or any other form?

0 ·

AMJ-2211 PratikSomaiya · Apr 28 at 05:04 AM

@PratikSomaiya The csv was generated via a API call and stored to a data lake as below:

0 ·

image.png (45.4 KiB)

Answer 1 · 2022-04-28T05:27:20.527Z

PratikSomaiya answered Apr 28, '22 AMJ-2211 commented Apr 28, '22

@AMJ-2211

Can you apply the Replace function in your API query body itself? Are you using a Web Activity for API call or any via other way?

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AMJ-2211 · Apr 28 at 05:47 AM

@PratikSomaiya Please see below:

This was a HTTP call with binary format. The request body was empty. Could you supply a sample code regarding to using the function regexReplace() in this body? Bear in mind this would be a binary stream at the point of time before a csv file is formed.

0 ·

image.png (31.1 KiB)

Answer 2 · 2022-05-02T06:38:02.973Z

ShaikMaheer-MSFT answered May 2, '22

Hi @AMJ-2211 ,

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

If possible try to see if you can apply your replace logic on response itself and then storing it as csv file. That way it will reduce lot of efforts.

If you would like to play around the logic on csv file after loading data from API then we should consider using data flows. Firstly, try to read entire file data as single column. For the you should have your dataset settings as below.

Once you get entire file data as single column then apply your replace logic using derived column. and then consider breaking your single column single data as multiple items using split() function on space. But if your any column has space in there data then that will also get splitted. Hence we should consider omit splitting of data which is wrapped insider quotes.

Once we splitted data as array then flatten that data as multiple rows and load as file. and then use this newly created file as source for further processes.

All above mentioned logic handling may be difficult and sometimes dataflows not much flexible to have this logic. So best way is having your custom code written in Azure functions to read your file data and do the replace() accordingly.

Hope this helps. Please let us know how it goes. Thank you.

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

image.png (104.1 KiB)

image.png (230.6 KiB)

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 3 · 2022-05-02T07:52:42.487Z

AMJ-2211 answered May 2, '22

@ShaikMaheer-MSFT Thank you so much for your thoughts. Unfortunately it won't work. I modified your sample data to closely imitate the real situation as below:

As you could see there are 2 issues applying your method to a read data:

There are spaces within a column which is not surrounded by a pair of double quotes. They are legitimate in one column;
There exist double quotes within the double quotes of the text delimiter

image.png (13.5 KiB)

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

question