Insert multiple json objects into an Excel file

(0) Share

Report

Posted on by Phormiga

I've done some research but looks like I'm looking for more basic advices than other users on this forum.

What I'm trying to accomplish is:

Read date from a json file provided by a website daily
Insert each Json object as a row in an Excel file

What i could get done:

Created a recurring action
Get the JSON content from the website using an HTTP action
Read the results using the Parse Json action...The JSON Parse action results looks good (Results bellow)
I was able to create an Excel Insert Row action but only the first json objet is included in the excel file. I've tried to use the "Apply to each" action but got the same result.

any help would be appreciated,

JASON PARSE ACTION RESULT

{
  "coins": {
    "Deutsche eMark": {
      "id": 180,
      "tag": "DEM",
      "algorithm": "SHA-256",
      "block_time": "60.0",
      "block_reward": 50,
      "block_reward24": 50,
      "last_block": 1655559,
      "difficulty": 29878389.373624,
      "difficulty24": 28678194.0057912,
      "nethash": 2138778420281150,
      "exchange_rate": 0.00000274,
      "exchange_rate24": 0.00000230328326180257,
      "exchange_rate_vol": 0.268626866198949,
      "exchange_rate_curr": "BTC",
      "market_cap": "$991,849.66",
      "estimated_rewards": "468.23212",
      "estimated_rewards24": "487.69511",
      "btc_revenue": "0.00128296",
      "btc_revenue24": "0.00133628",
      "profitability": 108,
      "profitability24": 111,
      "lagging": false,
      "timestamp": 1519244508
    },
    "Universal": {
      "id": 223,
      "tag": "UNIT",
      "algorithm": "SHA-256",
      "block_time": "60.0",
      "block_reward": 5,
      "block_reward24": 5,
      "last_block": 2993385,
      "difficulty": 152478635.46029,
      "difficulty24": 151109863.229586,
      "nethash": 10914845877344192,
      "exchange_rate": 0.00013,
      "exchange_rate24": 0.000131423347639485,
      "exchange_rate_vol": 2.6570028777474,
      "exchange_rate_curr": "BTC",
      "market_cap": "$17,545,618.88",
      "estimated_rewards": "9.2233",
      "estimated_rewards24": "9.30674",
      "btc_revenue": "0.00119903",
      "btc_revenue24": "0.00120988",
      "profitability": 101,
      "profitability24": 101,
      "lagging": false,
      "timestamp": 1519244505
    }
  }
}

Categories:

Building flows

I have the same question (0)

All responses (2)

Answers (0)

Phormiga 2 on at

Like (0)

Report
Copy link

Link copied!

looks like this is not that simple 😕

Was this reply helpful? Yes No
v-yuazh-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @ Phormiga,

I have made a test on my side , the JSON Parse action results you provide couldn’t be used to insert row to excel directly.

Please refer to steps below to create the flow:
Add a trigger “When a HTTP request is received”.
Add an initialize variable, name the variable as “OriginalJSON”,choose the Object in the “Type” field, choose the “Body” dynamic content of the trigger in the Value field.
Add an initialize variable 2, name the variable as “finalArray”,choose the Array in the “Type” field, leave the Value field blank.
Add a Compose,

variables('OriginalJSON')?['coins']

5. Add an “Append to array variable”，choose the finalArray in the Name field, fill in the expressions below in the Value field:
outputs('Compose')?['Deutsche eMark']

6. Add an “Append to array variable 2”，choose the finalArray in the Name field, fill in the expressions below in the Value field:
outputs('Compose')?['Universal']

7. Add an apply to each, the input field choose the finalArray dynamic content of the Variables.
8. Add an “Insert row” inside the apply to each, choose the file name and the Table name,
The id field fill in expressions below:

items('Apply_to_each')?['id']

The tag field fill in expressions below:

items('Apply_to_each')?['tag']

The timestamp field fill in expressions below:

items('Apply_to_each')?['timestamp']
I have three field in my excel, you could refer to expressions above to insert the others objects into the Excel.

Image reference:

When a HTTP request is received with JSON Parse action results you provide,the flow would run successfully as below:

The data would be created in the excel as below:

Regards,
Alice Zhang

Was this reply helpful? Yes No