Removing commas from ADF Sink Text File? Urgent, please help...

Question

Removing commas from ADF Sink Text File? Urgent, please help...

Mike Kiser 1,536

Hello
I have a final 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 correct BUT I need the commas removed. 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, ,, ,******@battelleecology.org,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

MarkKromer-MSFT 5,226 Reputation points Microsoft Employee Moderator

2021-01-21T18:15:17.533+00:00

Change the column delimiter in your sink dataset
Mike Kiser 1,536 Reputation points

2021-01-21T18:32:47.283+00:00

It won't let me use a non delimiter in this...I have tried it
But thanks!
Mike

2 answers

Your answer

MarkKromer-MSFT 5,226 Reputation points Microsoft Employee Moderator

2021-01-21T18:15:17.533+00:00

Change the column delimiter in your sink dataset
Mike Kiser 1,536 Reputation points

2021-01-21T18:32:47.283+00:00

It won't let me use a non delimiter in this...I have tried it
But thanks!
Mike

Answer 1

Nasreen Akter 10,811 Volunteer Moderator

Hi @Mike Kiser ,

According to the specification, I think you are trying to create a fixed length file. In this case, you can do the following (please see the screenshots attached):

add a derived column just before the sink activity
in the derived column, have a column e.g., outputPerLine with concat() function with all the output fields. concat(
toString(col1),
toString(col2),
toString(col3),
toString(col4),
toString(col5),
toString(col6),
toString(col7),
toString(col8),
toString(col9)
)
in the sink Dataset Schema have only one column outputPerLine
and finally in the sink, do the mapping outputPerLine from the derived column to the sink->dataset
also for fixedLength file dont forget to use lpad()/rpad() function.e.g., iif(isNull(col1),
lpad('0',9,'0'),
lpad(col1, 9, '0')
)

----------

If the above response helps, please "accept as answer" and "up-vote" the same! Thanks!

Mike Kiser 1,536 Reputation points

2021-01-21T18:30:50.45+00:00

WOW! Thanks! That is exactly what I am trying to do! I had no idea I could do this in ADF. Let me spend today creating this file. You save me many hours! I was even trying to have to implement SSRS :-)

I'll let you know how it goes....
Thanks a million!
Mike Kiser
Mike Kiser 1,536 Reputation points

2021-01-21T20:48:11.823+00:00

Thanks so much, I am working on it!

Where do I place the following?

iif(isNull(col1),
lpad('0',9,'0'),
lpad(col1, 9, '0')
)

Thanks!
@Nasreen Akter

@MartinJaffer-MSFT
Nasreen Akter 10,811 Reputation points Volunteer Moderator

2021-01-21T21:04:27.297+00:00

Hi @Mike Kiser , create another derivedColumn activity just before the concat-derivedColumn Activity for creating the fixed-length attributes using lpad or rpad
Mike Kiser 1,536 Reputation points

2021-01-21T21:56:26.743+00:00

Hi @Nasreen Akter

I am using a Copy Activity to create my Sink final File; do you have a link on creating a derivedColumn Activity? I'm getting confused creating one in the Copy Activity....thanks so much!!
Mike Kiser
Mike Kiser 1,536 Reputation points

2021-01-21T22:13:00.05+00:00

Hi Again @Nasreen Akter ,
I started thinking; are you talking about Data Flows? We don't have Data Flow in the Government Cloud yet. If you were referring to Data Flows, can you please give me an idea of a solution using the Copy Activity, or any Activity in ADF except Data Flows?
Thanks again!
Mike Kiser
Nasreen Akter 10,811 Reputation points Volunteer Moderator

2021-01-21T22:32:56.607+00:00

Hi @Mike Kiser , in my understanding (I could be wrong), you can't manipulate data in the CopyActivity. You need DataFlow to do that. :(
Mike Kiser 1,536 Reputation points

2021-01-22T00:50:39.177+00:00

Hi @Nasreen Akter ,
The final Text output file where I show one row is perfect. All I need to do is Remove the commas in the ADF Pipeline some way. I don't need to manipulate anything. Is there a Function or something that I can use to remove the commas ? Can the commas be replaced using the REPLACE Expression someway? or in an Azure Function? Its been a while since I have done Azure Functions..

Thanks!!
Mike Kiser

@MartinJaffer-MSFT
Mike Kiser 1,536 Reputation points

2021-01-27T02:24:13.62+00:00

Just to finalize this; I ended up creating a Logic App which I called from my ADF Pipeline. The Logic App stripped the comma's out using the Expression language.

This simple expression performed this:

]1

Answer 2

Mike Kiser 1,536

Just to finalize this; I ended up creating a Logic App which I called from my ADF Pipeline. The Logic App stripped the comma's out using the Expression language.

This simple expression performed this:

![60831-image.png ]1

Share via

Removing commas from ADF Sink Text File? Urgent, please help...

2 answers

Your answer