Reading fixed-length text files inside Data Factory or Synapse Analytics

Question

Hi,

I need to implement a ADF of Synapse Analytics solution in order to read some fixed-length text files, to control and process them and finally to save the processed data in a SQL table.

I've seen this article https://learn.microsoft.com/en-us/azure/data-factory/how-to-fixed-width that talks about how handling this case with a data flow.

I'd like to know if it exists a different and better solution respect to use a data flow.

Any suggests to me, please? Thanks

Answer 1

@pmscorca Welcome to Microsoft Q&A forum and thanks for reaching out here.

As you rightly pointed out, using mapping data flow (as explained in this document) is the ideal way if you want to implement it through ADF/synapse pipelines.

If you want to explore other options:

1. You may write your own code logic and execute it in either custom activity or Azure Function activity in ADF/Synapse pipelines.
2. Another apporach is using the Apache spark in Synapse or Databricks notebooks as explained in below articles and execute those notebooks from Synapse pipelines:

• pyspark parse fixed width text file
• how to read a fixed character length format file in spark

Hope this info helps.

---

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.

Answer 2

@pmscorca - Thanks for the additional context and clarification of the requirement. Sure, Same thing you can do in derived column with substring with hardcoded start and end.

Read each line as single column and then as mentioned above you can implement defining the schema using derived column.

Hope this info helps.

---

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.