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

Question

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

Answer 1

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

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

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

8. Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
9. Want a reminder to come back and check responses? Here is how to subscribe to a notification
10. 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