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

Question

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

Svetla 1

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

HimanshuSinha-msft 18,366 Microsoft Employee

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

Use dataflow
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

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

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
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

Svetla 1 Reputation point

2022-02-06T09:10:59.087+00:00
Hello @HimanshuSinha-msft
Thank you for your reply and investigation on the issue.
Yes you are right about option 1 & 2 that you suggested and in fact I have implemented option 2) proposed. I see 2 problems here:

Semantics of the data will be changed, as example I have a column address where new line is desired to be preserved while moving data from source to target .

In my scenario I have 2-3 thousand of tables and I'm not aware at which columns such data (with a new line) will be presented so that I can apply replace logic.

Can you suggest a generic approach where I don't need to specify for every column and table whether I expect char(10) to be replaced, and which will preserve the semantic and new line will be available as such at the target?
HimanshuSinha-msft 18,366 Reputation points Microsoft Employee

2022-02-17T00:39:38.78+00:00
Hello @Svetla ,
Thanks for the reply and i think I understand the limitations which you are refering too .
I have a thought ,which may need some thinking from your side as you have access to the data .
The above issues comes because in the copy activty we have the newline is \n( default) and in the actual data also we have (CHR(10), CHR(13)) . This is what I suggest ,

When coping the data from table , use a query which calls out all the column .

At the end of the SELECT statement add a character whcih is unique , lets say "$" . In SQL it looks like this .

SELECT *,'$' from SomeTableName

In the copy activity now add the newline character as "$".

Please so share know if you find any better solution .

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

Please don't forget to click on or upvote button whenever the information provided helps you
HimanshuSinha-msft 18,366 Reputation points Microsoft Employee

2022-02-23T00:53:27.657+00:00

Hello @Svetla ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet .In case if you have any resolution please do share that same with the community as it can be helpful to others . Otherwise, will respond back with the more details and we will try to help .
Thanks
Himanshu
HimanshuSinha-msft 18,366 Reputation points Microsoft Employee

2022-02-23T21:54:47.113+00:00

Hello @Svetla ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet .In case if you have any resolution please do share that same with the community as it can be helpful to others .
If you have any question relating to the current thread, please do let us know and we will try out best to help you.
Thanks
Himanshu
Svetla 1 Reputation point

2022-02-24T05:51:43.88+00:00

Hi @HimanshuSinha-msft
The proposed option of replacing the new line character in the Copy activity works, but it makes the output file a 1 line output, thus it's no longer human readable. Unfortunately so far I'm not able to achieve the desired solution, where chr(10) in the source can transfer as a line break in the target without breaking the scv schema.

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

1 answer

Activity