Copy data activity import CSV issue when Line Feeds are mixed in column data and conflict with end of file delimiter CR LF

Question

You cannot set the value to \r\n as a distinct option which doesn't make sense considering it is a standard row delimiter on most text extracts, forcing it is not an option nor can you create a parameter to set it manually. The error generated when you try to set it is "Row delimiter cannot be empty string or multi-character string when dataset is referenced ...."

I originally expected the process to be able handle setting the Row Delimiter to \r and that it would handle the situation where there is line feeds in the columns but it would key off the CF only

this answer https://learn.microsoft.com/en-us/answers/questions/746097/how-to-copy-data-from-csv-file-that-has-one-column doesn't seem like the logical answer as I cannot account for the possible data combinations coming from the 3rd party CRM we are exporting the data out of

Is there any way around this limitation where I can keep the Copy Data activity and the pipeline simple ?

Answer 1

Sam Miller ,

Thankyou for the follow up query. Unfortunately, multi character delimiter is not allowed in copy activity by design and '\r\n' is a multi-char delimiter. Only the default value on write is : "\r\n" when ["\r\n", "\r", "\n"] is selected. The option '\r\n' would probably be removed from the row delimiter dropdown as planned. I will keep you posted on the updates of the same.

Hope it helps. Kindly accept the answer if it's helpful. Thanks

Answer 2

@Sam Miller ,

Welcome to Microsoft Q&A platform and thanks for posting your question here.

As i understand your query, you want to know if there is any way to dynamically pass the row delimiter value in the delimited text dataset. In your case, say, you want to pass \r\n as the row delimiter value. Please let me know if that is not the ask.

For Copy activity, the single character or "\r\n" used to separate rows in a file. The default value is any of the following values on read: ["\r\n", "\r", "\n"]; on write: "\r\n". "\r\n" is only supported in copy command.

You can pass the value \r\n via pipeline parameter . Once you do that in the pipeline json it automatically appears like \r\n , try to remove the extra \ and run the pipeline.

Similar scenario handled in this thread: Parameterization of Row Delimiter property of ADF dataset

---

Hope it helps. Kindly accept the answer by clicking on Accept answer button as accepted answer helps community as well. Thanks