New Line character in source data breaks copy activity from AS400 to ADLS csv

Bhaskar-1984 1 Reputation point
2022-09-13T19:18:58.293+00:00

Trying to load AS400 table data into ADLS Gen2 with CSV file format. But one of column data creating new line in csv file.

select REPLACE(REPLACE(trim(cam), CHAR(10),''), CHAR(13),'') from as400.tablename where trim(csn)='4569' and dse='24';

Please find ref doc for query result in db and result in csv file after copy activity in adf.240751-1-queryresult.png240734-2-csvfile-result.png

In CSV file it is breaking the result and creating in second line.

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
11,237 questions
{count} votes

1 answer

Sort by: Most helpful
  1. AnnuKumari-MSFT 34,011 Reputation points Microsoft Employee
    2022-09-15T11:41:47.8+00:00

    Hi @Bhaskar-1984 ,

    Thankyou for using Microsoft Q&A platform and thanks for posting your question here.

    As I understand your issue, you are trying to copy data from AS400 database to .csv file in ADLS. However, there is an issue with the line breaks

    You can use derived column transformation in mapping dataflow to replace newline with blank by using the expression: replace(colName,'\n','')

    Note: This functionality is not there in Copy activity .

    Kindly check the below image.

    241501-image.png

    For more details, kindly check following resources:
    https://learn.microsoft.com/en-us/answers/questions/91071/how-to-remove-line-breaks-from-excel-file-in-adf.html
    https://www.youtube.com/watch?v=AW0oHU8MAm8

    Hope this will help. Please let us know if any further queries.

    ------------------------------

    • Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you.
      Original posters help the community find answers faster by identifying the correct answer. Here is how
    • Want a reminder to come back and check responses? Here is how to subscribe to a notification
    • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators
    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.