Extracting Data from Excel File to JSON using Azure Data Factory

syam kumar 0 Reputation points
2024-03-15T07:44:37.9833333+00:00

I am reaching out to seek guidance on extracting data from an Excel file utilizing Azure Data Factory and converting it into JSON format.

The data we're working with is stored in xlsx format and has a variable structure, including dynamic numbers of rows and columns with varying values, like destination and origin columns, etc.

To illustrate the data format, here's an example:

User's image

Our goal is to configure Azure Data Factory to handle this dynamic structure efficiently and transform it into JSON, structured as follows:

{

  "values": [{

  "origin_port": "India",

  "origin_port_code": "IND",

  "destination_port": "Singapore",

  "destination_port_code": "SIN",

  "values": {

    "P1": "123",

    "P2": "456",

    "P3": "789"

  }

},

{

  "origin_port": "India",

  "origin_port_code": "IND",

  "destination_port": "United Kingdom",

  "destination_port_code": "UK",

  "values": {

    "P1": "234",

    "P2": "567",

    "P3": "891"

  }

},

{

  "origin_port": "United States of America",

  "origin_port_code": "USA",

  "destination_port": "Singapore",

  "destination_port_code": "SIN",

  "values": {

    "P1": "789",

    "P2": "123",

    "P3": "456"

  }

},

{

  "origin_port": "United States of America",

  "origin_port_code": "USA",

  "destination_port": "United Kingdom",

  "destination_port_code": "UK",

  "values": {

    "P1": "891",

    "P2": "234",

    "P3": "567"

  }

}]}
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,488 questions
Azure AI Document Intelligence
Azure AI Document Intelligence
An Azure service that turns documents into usable data. Previously known as Azure Form Recognizer.
1,342 questions
{count} votes