Trim header and values in a text file stored in azure blob

Question

Trim header and values in a text file stored in azure blob

Kaviprakash Selvaraj 20

Hi,

I have a text file stored in an blob storage that has file name, headers and values. In ADF, we copy the contents from the file into a SQL table using COPY activity. The challenge is as part of the header and values, there is an additional delimiter (PIPE SYMBOL) at last position that needs to be removed before the copy activity.

Here is the sample contents from the file.

FILENAME|EDI|5281|20201226|26**|**

......

.......

What is the best way to remove the pipe symbol at last position ?

Thanks in advance,

Accepted answer

2 additional answers

Your answer

Answer 1

Subashri Vasudevan 11,231

Hi I assume that you just have only pipe symbol at the end of each row. If so, there are a couple of ways to ignore the pipe symbol. 1. Use azure function with python to strip off last Delimiter character. 2. Use data flow to read only required columns. This video explains the way we use data flow to achieve this. Please check and let us know if that helps and let us know if the assumption is wrong.

Answer 2

Kaviprakash Selvaraj 20

@Subashri Vasdudevan - Thanks for your response.

I was able to create a dataflow to fix this issue, however I'm unable to write it to sink. The reason being is we are using SQL server in IaaS model that requires self hosted IR to communicate to the database.

Linked service with Self-hosted Integration runtime is not supported in data flow. Do you know any other alternative workaround ? Using Azure function will resolve this instead ?

Subashri Vasudevan 11,231 Reputation points

2023-08-21T13:29:21.75+00:00

Hi from within data flow we can't use self hosted IR. one work around is to write it to a file in data lake and use a copy activity to read from file and write to SQL on Prem
Kaviprakash Selvaraj 20 Reputation points

2023-08-21T16:11:43.0866667+00:00

Thank you. Let me try this but this is not a good practice I believe. As we have so many files feeding into blob, probably you need to read the data twice from azure storage account for the conversion and copy to SQL table. This would definitively add more costs to the billing If I'm correct.
Subashri Vasudevan 11,231 Reputation points

2023-08-22T01:30:19.69+00:00

Original question just mentioned about trimming last Delimiter. So we recommended the data flow route. If the file size is less, you can consider using az. Function with python to trim Delimiter and then a copy activity to insert. Or use data bricks to do both the tasks. First trim and then load to table.

Answer 3

Kaviprakash Selvaraj 20

Thanks for your answers. Let me explore the options.

Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

Share via

Trim header and values in a text file stored in azure blob

2 additional answers

Your answer