How to copy data from CSV file that has one column with line breaks

Mohsen Alam 11 Reputation points
2022-02-22T15:10:41.64+00:00

My source CSV file has two columns that have huge chunks of texts with lots of line breaks.

176856-image.png

When I use Copy activity to read this file in ADF, it breaks the rows whenever it sees an line break instead of actually parsing the complete row.

176873-image.png

How can I parse this type of file in ADF?

Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,600 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. HimanshuSinha-msft 19,381 Reputation points Microsoft Employee
    2022-02-24T01:47:57.603+00:00

    Hello @Mohsen Alam ,
    Thanks for the question and using MS Q&A platform.
    As we understand the ask here is copy all the rows with the inline character . Please do let me know if thats accurate .
    The reason we are seeing the output which you have shared is because the row delimiter is ( \n) and also the we have the same charanter in some columns .

    One way whcih i can think as a work around is to add a custom delimiter ( if you can ) , in the below eg I will use $

    This is the my intial csv
    col1,col2,col3,col4
    aaa,bbbbbb,cccc
    c1
    c2
    ,d1
    d2
    d3

    And the output looks below in the preview

    177382-image.png

    I suggest if you can add $ in the end of the row and use that as the row dimiter in the ADF setting , it will work .

    The input as

    col1,col2,col3,col4$
    aaa,bbbbbb,cccc
    c1
    c2
    ,d1
    d2
    d3$

    Set the delimiter as shown below

    177391-image.png
    The output will lok like below
    177343-image.png

    Please do let me if you have any queries.

    Thanks
    Himanshu

    • 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
    1 person found this answer helpful.
  2. MADHUSUDAN PANWAR 86 Reputation points
    2023-06-28T13:05:41.6966667+00:00

    Hi @Mohsen Alam

    I got into the same problem today and tried to get various workarounds. (I know my solution is 1 year late but can you try the solution)

    I had a CSV file comma delimited and wherever there was comma in any of the single column; column was quoted in double quotes. The same column was having line breaks as well. And as soon as line break was encountered, the data in the table was moving to the next line.

    The solution which I found myself and worked was mentioning Quote Character and Escape Character as double quotes.

    Please upvote this if it works for you.

    Thanks,

    Madhusudan

    0 comments