Data factory read file by position

Emilio Salas Roura 25 Reputation points
2024-02-18T10:38:20.94+00:00

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,

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,914 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,669 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Subashri Vasudevan 11,206 Reputation points
    2024-02-18T12:37:37.1766667+00:00

    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 No comments

  2. Emilio Salas Roura 25 Reputation points
    2024-02-19T05:40:36.19+00:00

    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)


  3. Pinaki Ghatak 4,285 Reputation points Microsoft Employee
    2024-04-08T08:32:03.82+00:00

    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:

    1. Create a new dataset in your Azure Data Factory and select the Azure Blob Storage connector as the source.
    2. In the dataset settings, specify the path to your text file in the File path field.
    3. In the Format section, select Custom delimited as the format.
    4. 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.
    5. In the Schema section, specify the column names and data types for each column in your text file.
    6. Create a new dataset for your destination table in Azure SQL Database and select the Azure SQL Database connector as the sink.
    7. In the dataset settings, specify the table name and the Azure SQL Database linked service.
    8. 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.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.