Extracting JSON data from an API and then reconstructing it in a CSV

Anonymous — Sat, 16 Nov 2024 04:55:44 GMT

HI guys,

I'm writing a job which will call an API for an eCommerce site which will return all unprocessed orders. I then need to write this information to a CSV file (1 CSV per order). The CSV should contain an initial row with header information (eg. customer & address info) and then a line per order item.

I've currently managed to extract the information using several tExtractJSONFields but now I'm struggling to find a way to create the required CSV.

I have also tried using ThMap with no success, I would love to hear your thoughts on the way best to achieve this and if I'm completely in the wrong ballpark.

API Response:

{
  "message": "Fetched order(s)",
  "data": [
    {
      "orderId": 1,
      "orderNumber": "ORDER0001",
      "createdAt": "2019-08-13T14:58:07+01:00",
      "deliveryAddress": {
        "id": 1,
        "town": "dasd",
        "country": "ada",
        "lastName": "asdasd",
        "postcode": "asda",
        "createdAt": "2019-08-13T14:57:47+01:00",
        "firstName": "sakdmsad",
        "updatedAt": "2019-08-13T14:57:47+01:00",
        "thoroughfare": "adasd",
        "houseNameNumber": "12",
        "isDefaultBillingAddress": true,
        "isDefaultShippingAddress": true
      },
      "orderLines": [
        {
          "orderLineId": 1,
          "idealUin": "DELIVERY",
          "supplierUin": "DELIVERY",
          "quantity": 2,
          "unitPrice": "5.00"
        },
        {
          "orderLineId": 2,
          "idealUin": "product0004",
          "supplierUin": "product0004",
          "quantity": 2,
          "unitPrice": "5.00"
        }
      ]
    },
    {
      "orderId": 2,
      "orderNumber": "ORDER0002",
      "createdAt": "2019-08-13T14:58:07+01:00",
      "deliveryAddress": {
        "id": 1,
        "town": "dasd",
        "country": "ada",
        "lastName": "asdasd",
        "postcode": "asda",
        "createdAt": "2019-08-13T14:57:47+01:00",
        "firstName": "sakdmsad",
        "updatedAt": "2019-08-13T14:57:47+01:00",
        "thoroughfare": "adasd",
        "houseNameNumber": "12",
        "isDefaultBillingAddress": true,
        "isDefaultShippingAddress": true
      },
      "orderLines": [
        {
          "orderLineId": 1,
          "idealUin": "DELIVERY",
          "supplierUin": "DELIVERY",
          "quantity": 2,
          "unitPrice": "5.00"
        },
        {
          "orderLineId": 2,
          "idealUin": "product0005",
          "supplierUin": "product0005",
          "quantity": 2,
          "unitPrice": "5.00"
        }
      ]
    }
  ]
}

Talend job currently:

An example of the information returned in the tLogRow is below;

.-----------+------------+---------------+--------+---------+----------+---------------+---------------+---------------------+--------------+------------+------------------+-------+------------+---------------------------------------------------------.
|                                                                                                                        tLogRow_3                                                                                                                         |
|=----------+------------+---------------+--------+---------+----------+---------------+---------------+---------------------+--------------+------------+------------------+-------+------------+--------------------------------------------------------=|
|orderLineId|idealUin    |supplierUin    |quantity|unitPrice|firstName |lastName       |houseNameNumber|thoroughfare         |town          |postcode    |country           |orderId|orderNumber |createdAt                                                |
|=----------+------------+---------------+--------+---------+----------+---------------+---------------+---------------------+--------------+------------+------------------+-------+------------+--------------------------------------------------------=|
|1          |PRODUCT0001 |PRODUCT0001    |1       |5.00     |["Test"] |["Test"]       |["11"]         |["West Road"]         |[London"]     |["NE1 2DW"]  |["UK"]           |[1]    |["ORDER0001"] |["2019-07-26T09:39:21+00:00"]|

Thanks in advance

Re: Extracting JSON data from an API and then reconstructing it in a CSV

Anonymous — Mon, 09 Sep 2019 06:30:38 GMT

Hello,

Is there any update for your issue?

Best regards

Sabrina

Re: Extracting JSON data from an API and then reconstructing it in a CSV

Anonymous — Mon, 09 Sep 2019 09:46:00 GMT

Hi Sabrina,

Due to a lack of support we had to totally bypass Talend and just change the data from the source, so it was received in a csv format rather than JSON.

Re: Extracting JSON data from an API and then reconstructing it in a CSV

Anonymous — Tue, 26 Nov 2019 15:20:04 GMT

We have experience in solving problems JSON data.
If you need support, feel free to ask for help - https://whidegroup.com/

topic Re: Extracting JSON data from an API and then reconstructing it in a CSV in Talend Studio

Extracting JSON data from an API and then reconstructing it in a CSV

Re: Extracting JSON data from an API and then reconstructing it in a CSV

Re: Extracting JSON data from an API and then reconstructing it in a CSV

Re: Extracting JSON data from an API and then reconstructing it in a CSV