replace the column delimiter ¶ with another delimiter supported by synapse

Question

Hello everybody,

I'm extracting data in .csv format, which has a paragraph mark (¶) as the field delimiter. As I see this format is not defined in the list of column separators in the synapse. I've tried different ways to replace the delimiter, for example, by creating an external table but was not successful.  Any Idea?

Answer 1

@Sara Fadaei | b.telligent ,

Thankyou for using Microsoft Q&A platform and thanks for posting your query here.

As I understand your question, you want to replace the paragraph mark to some character which is supported as column delimiter by synapse. Please let me know if that is not the ask here.

• You can use the replace function in derived column transformation of mapping data flow to replace the paragraph mark (¶) with a different character that is supported as a column separator Check here for more details: replace function , regex replace function in mapping dataflow.
• You can also leverage replace()/regexp_replace() function in PySpark to write a notebook in synapse to achieve this requirement Reference: Replace , Regexp_replace in spark.

---

Hope it helps. Kindly accept the answer and mark it as helpful. Thankyou