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

Svetla 1 Reputation point
2022-02-03T16:57:35.58+00:00

I'm copying data from Oracle DB to csv files in ADLS Gen 2 using copy activity in ADF. In my Oracle table there are columns which contain new line character (CHR(10), CHR(13)). In the csv I get output as:
ID,NAME,GENDER
1,"Tom Alex","M"
2,"John
Hopkins","M"
3,"Emily Burns","F"
When I try to load this file to SQL or parse, it fails

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,531 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. HimanshuSinha-msft 19,376 Reputation points Microsoft Employee
    2022-02-04T21:20:31.61+00:00

    Hello @Svetla ,
    Thanks for the ask and using Microsoft Q&A platform .
    As we understand the ask here is how to copy the records with new line & that here the source is Oracle and sink is ADLS gen2 . Please do let me know if that not accurate.

    I was able to repro the issue using SQL as I do not have Oracle .

    DECLARE @STRINPUT VARCHAR(100), @STRRESULT VARCHAR(100);
    SET @STRINPUT = 'JOHN , HOPKINS ';
    SET @STRRESULT = REPLACE(@STRINPUT, ',', CHAR(10));
    --SELECT @STRRESULT AS 'STRING WITH LINE FEED';
    INSERT INTO DBO.SVETLA( ID , NAME) VALUES ( 4,@STRRESULT)

    Since the copy activity does not allow any tranformation , you have two options

    1. Use dataflow
    2. Query the data from Oracle so that we remove the char(10) using replace or simialr function.

    I did tried the second option in SQL and I was able to do that using replace function , I am confident that Orable will also have some function like that .

    SELECT id ,replace(name,CHAR(10),'') as Name from dbo.Svetla

    The output in csv is

    171534-image.png

    Please do let me if you have any queries .
    Thanks
    Himanshu

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

    1. 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
    2. Want a reminder to come back and check responses? Here is how to subscribe to a notification
    3. 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