cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
New Member

Insert multiple json objects into an Excel file

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,

 

excel_map.png

 

 

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
    }
  }
}

 

 

 

 

2 REPLIES 2
Highlighted
New Member

Re: Insert multiple json objects into an Excel file

looks like this is not that simple 😕

Highlighted
Community Support
Community Support

Re: Insert multiple json objects into an Excel file

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:

  1. Add a trigger “When a HTTP request is received”.
  2. 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.
  3. Add an initialize variable 2, name the variable as “finalArray”,choose the Array in the “Type” field, leave the Value field blank.
  4. 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:

 1.png2.png

 

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

3.png

 

The data would be created in the excel as below:

 4.png

 

 

 

Regards,
Alice Zhang

Helpful resources

Announcements
firstImage

Super User Program Update

Three Super User rank tiers have been launched!

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

Top Solution Authors
Top Kudoed Authors
Users online (4,717)