ADF-How to suppress line feed (\n\r) from output file while using DelimitedText dataset

Question

ADF-How to suppress line feed (\n\r) from output file while using DelimitedText dataset

Mannur, Deepak 20

I am extracting blob data from Salesforce and storing it in ADLS as DelimitedText format. However, I've noticed that a new line is consistently added at the end of each file, and I haven't found an option to suppress the next line feed (\n\r) in the output file. Can anyone offer a solution for this issue?

User's image

Mannur, Deepak 20 Reputation points

2023-05-11T09:51:08.2533333+00:00

I am extracting blob data from Salesforce and storing it in ADLS as DelimitedText format. However, I've noticed that a new line is consistently added at the end of each file, and I haven't found an option to suppress the next line feed (\n\r) in the output file. Can anyone offer a solution for this issue?
David S 0 Reputation points

2023-05-25T19:34:19.54+00:00
To address the issue of a new line being consistently added at the end of each file when extracting blob data from Salesforce and storing it in ADLS as DelimitedText format, there are a few potential solutions you can consider:

Data Transformation: After extracting the blob data from Salesforce and before storing it in ADLS, you can perform a data transformation step to remove the trailing new line characters. This can be done using various methods or tools depending on your specific setup, such as scripting languages like Python or using data transformation tools like Apache Spark or Apache NiFi.

Post-processing: After the data has been stored in ADLS as DelimitedText files, you can run a post-processing script or job that goes through the files and removes the extra new line character at the end of each file. This can be achieved through scripting languages or text processing tools. For example, you can use Python's file handling capabilities to open each file, remove the trailing new line, and save the file again.

Output File Options: Check if the tool or method you are using to extract blob data and store it as DelimitedText format in ADLS provides any options or settings to control the output file format. Look for options related to line termination or newline characters. It's possible that there may be a configuration setting to suppress the trailing new line.

ADLS Settings: Verify the settings or configurations of your ADLS system. It's possible that there are options or configurations within ADLS that affect how files are written and whether or not a new line character is added at the end. Check the documentation or consult with your ADLS administrator to see if there are any settings that can be adjusted.

Remember to thoroughly test any changes or solutions before implementing them in a production environment to ensure they do not have unintended consequences on your data or processes.

Accepted answer

1 additional answer

Your answer

Mannur, Deepak 20 Reputation points

2023-05-11T09:51:08.2533333+00:00

I am extracting blob data from Salesforce and storing it in ADLS as DelimitedText format. However, I've noticed that a new line is consistently added at the end of each file, and I haven't found an option to suppress the next line feed (\n\r) in the output file. Can anyone offer a solution for this issue?
David S 0 Reputation points

2023-05-25T19:34:19.54+00:00

To address the issue of a new line being consistently added at the end of each file when extracting blob data from Salesforce and storing it in ADLS as DelimitedText format, there are a few potential solutions you can consider:

Data Transformation: After extracting the blob data from Salesforce and before storing it in ADLS, you can perform a data transformation step to remove the trailing new line characters. This can be done using various methods or tools depending on your specific setup, such as scripting languages like Python or using data transformation tools like Apache Spark or Apache NiFi.

Post-processing: After the data has been stored in ADLS as DelimitedText files, you can run a post-processing script or job that goes through the files and removes the extra new line character at the end of each file. This can be achieved through scripting languages or text processing tools. For example, you can use Python's file handling capabilities to open each file, remove the trailing new line, and save the file again.

Output File Options: Check if the tool or method you are using to extract blob data and store it as DelimitedText format in ADLS provides any options or settings to control the output file format. Look for options related to line termination or newline characters. It's possible that there may be a configuration setting to suppress the trailing new line.

ADLS Settings: Verify the settings or configurations of your ADLS system. It's possible that there are options or configurations within ADLS that affect how files are written and whether or not a new line character is added at the end. Check the documentation or consult with your ADLS administrator to see if there are any settings that can be adjusted.

Remember to thoroughly test any changes or solutions before implementing them in a production environment to ensure they do not have unintended consequences on your data or processes.

Answer 1

ShaikMaheer-MSFT 38,551 Microsoft Employee Moderator

Hi Mannur, Deepak,

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

Its expected behavior. Once data gets loaded in to delimited file. You can try creating a dataset on that file. You will observe that your data comes correctly as last row is empty. It's actually not a row. Its end of content.

Are you seeing any issues because of it to read that delimited file data?

Mannur, Deepak 20 Reputation points

2023-05-16T09:39:12.7266667+00:00

Thanks Maheer! yes the API call fails to access the data due to next line feed. I used a Web activity to suppress the next line.
ShaikMaheer-MSFT 38,551 Reputation points Microsoft Employee Moderator

2023-05-17T16:34:18.18+00:00

Hi Mannur, Deepak, Are we making API call to ADLS gen2 here to read content of file using web activity? is that failing that way? Any specific reason for it. Actually you can create a dataset in ADF which points to file and that should help to copy data where ever we want.
ShaikMaheer-MSFT 38,551 Reputation points Microsoft Employee Moderator

2023-05-23T10:32:25.2166667+00:00

Hi Mannur, Deepak, Just checking if any updates.

Please consider hitting Accept Answer button. Accepted answers help community as well.
ShaikMaheer-MSFT 38,551 Reputation points Microsoft Employee Moderator

2023-05-29T13:32:41.5266667+00:00

Hi Mannur, Deepak, Thank you for share your work around. Kindly consider hitting Accept Answer button. Accepted answers help community as well. Thank you.

Answer 2

Mannur, Deepak 20

Hi Maheer instead of new dataset I opted a ADF Web activity. It worked fine. API call is to fetch data from ADLS file and even that worked fine. Thanks for checking back on this. We can close this thread now.

Share via

ADF-How to suppress line feed (\n\r) from output file while using DelimitedText dataset

1 additional answer

Your answer