cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Phormiga
Level: Power Up

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
Phormiga
Level: Power Up

Re: Insert multiple json objects into an Excel file

looks like this is not that simple 😕

Community Support Team
Community Support Team

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
thirdimage

Power Automate Community User Group Member Badge

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

firstImage

Incoming: New and improved badges!

We've given our badges an overhaul and also added some brand new ones!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Kudoed Authors
Users Online
Currently online: 323 members 5,812 guests
Recent signins:
Please welcome our newest community members: