question

CharbelDaia-9232 avatar image
0 Votes"
CharbelDaia-9232 asked CharbelDaia-9232 commented

Copy data with fileds enter and tabs

Hi,

I'm reading a table in sql and generating a cvs for the Data Factory copy data activity, but I have text fields with enter and tabs written in the table, when generating the cvs file it gets out of order how can I handle it?

thanks!

azure-data-factory
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @CharbelDaia-9232 and welcome to Microsoft Q&A.

Did @JeffreyWilliams-3310 suggestion help you? If so, please mark as accepted answer. If not, could you please share a little more information?

By "out of order" do you mean visual appearance, or do downstream applications have trouble reading the data?

I assume you mean

 A1,B1,C1,D1
 A2,B2,C2,D2
    
 becomes
    
 A1,B1,C1
 D1,A2,B2,C2
 D2
    
 not
    
 A1,C1,B1,D1
 A2,C2,B2,D2
0 Votes 0 ·

Hi @MartinJaffer-MSFT

Yes, i have problems reading the file by other applications,

I'm temporarily using the solution that @JeffreyWilliams-3310 suggested using replace(char(13) and char(10))

it works but in a way I change the data, is there any other alternative?

Thanks
Charbel

0 Votes 0 ·

Have you tried wrapping the field in double-quotes? If that doesn't work - then your options are limited, either replace the offending characters with a space or replace on output and replace again on input.

0 Votes 0 ·
Show more comments

1 Answer

JeffreyWilliams-3310 avatar image
0 Votes"
JeffreyWilliams-3310 answered CharbelDaia-9232 commented

I am not familiar with Data Factory - but there are a couple of possibilities:

  • Wrap those columns (fields) in double-quotes.

  • Replace the invalid characters with a different character - then have the import process in Data Factory replace the data with the correct character(s)

For example - if the 'enter' character is a carriage return (char(13) then replace(column, char(13), '<13>') and on import: replace(column, '<13>', char(13)).

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.