Extracting Data from Excel File to JSON using Azure Data Factory

Question

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:

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"

  }

}]}