Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
Escape backslash while loading to Snowflake using ADF pipeline
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(38.4, 81%, 13%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EMP%3C/text%3E%3C/svg%3E)
Mahesh P R
0
Reputation points
Data has got backslashes. We are loading CSV, TSV files dynamically based on delimiter to Snowflake. ADF pipeline creates stage to Azure blob before copying into Snowflake. While creating the stage it uses backslash has escape character and hence the issues. Can we override the creation of stage manually or using some activity in ADF pipeline.
Have tried the options mentioned in this forum by changing the quote character and escape characters but unable to solve the problem.
Finding and replacing the backslash with a blank in a separate copy activity will also work but not sure how to do this?
Azure Data Factory
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(54.400000000000006, 38%, 15%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EQ%3C/text%3E%3C/svg%3E)
QuantumCache 20,681 Reputation points Moderator2023-08-31T17:18:22.49+00:00 Hello @Mahesh P R,
Did you try aData Flowactivity in Azure Data Factory to find and replace the backslashes with a different character, such as a blank space?Add a
Derived Columntransformation to your data flow and connect it to the source. In theDerived Columntransformation, add a new column with the following expression:-
replace(YourColumn, '\\', '')
-
-
Mahesh P R 0 Reputation points
2023-09-01T09:44:34.8866667+00:00 Thanks for the response Satish.
Yes, tried searching for the entire file and replaced it with a blank using data flow but for a test file of around 50 MB data flow created chunks of files instead of replacing the existing file. Also, using data flow performance of copy activities is taking hit.
We are feeding pipelines dynamically through a control table for the source filenames, target objects, mappings etc. There are several hundreds of files with many columns with this kind of data. Handling them will be challenge.
Looking for any workaround, please.
-
QuantumCache 20,681 Reputation points Moderator
2023-09-06T00:06:31.7333333+00:00 Hello @Mahesh P R, This needs further deep investigation on your scenario.
Can you please attach the sample pipeline activities image with various stages?
Example: Are you using Dataflow as below? or any Pipeline activities?
Sign in to comment
Sign in to answer