azure - Removing commas from ADF Sink Text File in my Pipeline

Question

I have a sink file (same as a report) as an output from an ADF Copy Activity in a Pipeline where the formatting has to be "perfect" to meet a 3rd Party Vendors requirements. The following shows the first line; it is all perfectly correct, BUT I need ALL the commas removed as the last step in my ADF Pipeline . I could not get ADF to create this without delimiters.

Can anyone please suggest a possible solution? I need this for Production. Thanks! Mike Kiser

05, ,2021-01-21, ,BMECOL, ,,,,,,, ,0000000000,0000000000,0000000000,000000000000000,+,00000000000,+,000000000000000000000,10,E,2007-10-09 00:00:00.0000000,XXXXXXXXX06,BMECOL, , , , ,00,,Henry,W, ,Loescherkisertest3,,M,1960-01-01 00:00:00.0000000, ,USA,,,,XXXXXXXXX06,,,,,,,S,Single,,010004,,,,,,,,,,15,1,,XXXXXXXXX06,BMECOL , , , , ,0 ,Address 1,Address 2,Address 3, ,City,OH, ,United States,12345,Home, ,541/981-1818, ,, ,[email protected],20, ,,XXXXXXXXX06,BMECOL , , , , ,00,,5,HIS, , , ,25, ,,XXXXXXXXX06,BMECOL , , , , ,00,0000,C4029O,Professional,000000,000000 ,S,5200,M ,CO0001,BE CS Boulder HQ Ops,D22,NO ,REGULAR , ,,BCO , , ,40,0000000000 ,0000000000 ,1900-01-01 00:00:00.0000000,1900-01-01 00:00:00.0000000

question from:https://stackoverflow.com/questions/65837927/removing-commas-from-adf-sink-text-file-in-my-pipeline

Answer 1

I think you can use column-patterns in dataflow to replace the commas.
I've created a simple test.

1. I saved the sample data as a row in txt file. And select No delimiter at Column delimiter. The data preview like this:
2. In DerivedColumn1 activity, we can use replace($$,',','') to remove the commas.
3. Sink preview like this: