Data factory read file by position

Question

Data factory read file by position

Emilio Salas Roura 65

Hello, I have a txt file with several columns, and there is no delimiter. We find the columns by index (position). How can I read this file from a data factory and copy the contents into a table with several columns Thanks,

Nandan Hegde 36,796 Reputation points MVP Volunteer Moderator

2024-02-18T11:21:42.89+00:00

Can you please provide a screenshot of your file? Or upload the sample file
Smaran Thoomu 34,960 Reputation points Microsoft External Staff Moderator

2024-02-23T07:33:40.04+00:00

@Emilio Salas Roura Following up to see if the below suggestion was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

3 answers

Your answer

Nandan Hegde 36,796 Reputation points MVP Volunteer Moderator

2024-02-18T11:21:42.89+00:00

Can you please provide a screenshot of your file? Or upload the sample file
Smaran Thoomu 34,960 Reputation points Microsoft External Staff Moderator

2024-02-23T07:33:40.04+00:00

@Emilio Salas Roura Following up to see if the below suggestion was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 1

Hello @Emilio Salas Roura

To read a text file with several columns and no delimiter, you can use the Custom delimited format in the Azure Data Factory. In this case, you can specify the column widths as the delimiter.

Here are the steps to do this:

Create a new dataset in your Azure Data Factory and select the Azure Blob Storage connector as the source.
In the dataset settings, specify the path to your text file in the File path field.
In the Format section, select Custom delimited as the format.
In the Column delimiter field, specify the column widths as the delimiter. For example, if your first column is 10 characters wide and your second column is 5 characters wide, you would specify "10,5" as the delimiter.
In the Schema section, specify the column names and data types for each column in your text file.
Create a new dataset for your destination table in Azure SQL Database and select the Azure SQL Database connector as the sink.
In the dataset settings, specify the table name and the Azure SQL Database linked service.
In the Mapping section, map the columns from your source dataset to the columns in your destination table. Once you have created both datasets, you can create a pipeline to copy the data from your source dataset to your destination dataset using the Copy Data activity. In the Copy Data activity, select your source and destination datasets and specify any additional settings, such as the frequency of the copy operation.

I hope this helps! Let me know if you have any further questions.

Answer 2

Emilio Salas Roura 65

Yes, that's exactly what I'm trying to do. But when I want to test this example I had this error. User's image

Importing the projection. Where can I see the error details (view notification)

Subashri Vasudevan 11,306 Reputation points Volunteer Moderator

2024-02-19T11:05:35.8333333+00:00

You can go to monitor tab and see the details of the execution. Please see if you are able to import schema in the dataset. And then you can come to data flow and import projection. Please try and let us know.
Emilio Salas Roura 65 Reputation points

2024-02-19T14:34:12.9933333+00:00

I cannot go to monitor because I'm trying to setup the projection and I got that error without any description. I cannot save this pipeline in order to run or debug
Subashri Vasudevan 11,306 Reputation points Volunteer Moderator

2024-02-20T03:54:09.5433333+00:00

Hi, You are right, without running, we cannot go to monitor. As mentioned in my earlier comment, please do a schema import from data set and then come to data flow to import projection. This way, you can catch any issues with the dataset. And, you can delete the data flow source and add freshly. (after schema import from dataset). If the data flow fails due to intermittent network issue, this might resolve the issue too. Generally, If there are no proper details in the error, it might be due to improper configuration \ expressions in the source. Please post screenshots of source settings and source option tabs of your dataflow along with the dataset settings.
Emilio Salas Roura 65 Reputation points

2024-02-20T08:21:53.8033333+00:00

Hi, importing previously the schema in dataset, the import projection has worked. Please, See screenshot

But I had an error in data preview

The delimitor should by \n by default thanks,
Smaran Thoomu 34,960 Reputation points Microsoft External Staff Moderator

2024-02-22T04:34:26.5633333+00:00
@Emilio Salas Roura It's great to hear that the import projection has worked after importing the schema in the dataset. Regarding the error in data preview, it seems like the delimiter is not set correctly. You can change the delimiter to \n by following these steps:

Click on the source transformation in the data flow.

In the source transformation settings, go to the 'Source options' tab.

Under 'File format settings', change the 'Row delimiter' to \n.

Click on 'Preview data' to check if the data is now displayed correctly.

Please let me know if this resolves the issue.

Answer 3

Subashri Vasudevan 11,306 Volunteer Moderator

Hi, you may go through this link, where there's a detailed information to process fixed length files. https://learn.microsoft.com/en-us/azure/data-factory/how-to-fixed-width If your requirement is different, as Nandan has requested, please share a sample file with details. Thanks.

0 comments

Share via

Data factory read file by position

3 answers

Your answer