import json files

pyclen

Board Regular
Joined
Jan 17, 2022
Messages
91
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi there,

I have a bunch of json files that have heart rate data in it. The file when I open it w/Notepad looks like this

[{
"dateTime" : "04/01/23 04:00:03",
"value" : {
"bpm" : 70,
"confidence" : 3
}
},{
"dateTime" : "04/01/23 04:00:08",
"value" : {
"bpm" : 69,
"confidence" : 3

So now when I import this into XL365 via data import --> from json file, I get this
1680438276919.png


showing me a date/time for the particular entry and when I click on the Record I get two values, one for heart rate (bpm) and one for confidence.

What I want is to get the date/time and the heart rate into a simple table that I can graph out

How do I do that?

A link to an example file is here: heart_rate-2023-04-01.json

Any help is appreciated
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
JSON files can be imported quickly, reliably, and safely with Power Query using just the UI.
Power Query:
let
    Source = Json.Document(File.Contents("C:\Temp\heart_rate-2023-04-01.json")),
    ConvertedToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ExpandedColumn = Table.ExpandRecordColumn(ConvertedToTable, "Column1", {"dateTime", "value"}, {"dateTime", "value"}),
    ExpandedValue = Table.ExpandRecordColumn(ExpandedColumn, "value", {"bpm", "confidence"}, {"bpm", "confidence"}),
    ChangedType = Table.TransformColumnTypes(ExpandedValue,{{"dateTime", type datetime}, {"bpm", Int64.Type}, {"confidence", Int64.Type}})
in
    ChangedType
11,914 rows imported in a minute.
 
Upvote 0
Solution
Thank you both for your answers, however the second one works just fine.

I have not tried the VBA solution, it also says it is tested for 2007 and beyond but nothing for XL365.
Also, not sure how to modify the code if needed, so I am going w/jdellasala's post

Thanks again for your help
 
Upvote 0
Thank you both for your answers, however the second one works just fine.

I have not tried the VBA solution, it also says it is tested for 2007 and beyond but nothing for XL365.
Also, not sure how to modify the code if needed, so I am going w/jdellasala's post

Thanks again for your help
Thanks. Glad it help. Just for reference, this should work with Excel 2010 and later. I don't remember which versions of Excel it was an add-in - I think 2010 and 2013. Later versions have it built in.
 
Upvote 0

Forum statistics

Threads
1,223,958
Messages
6,175,628
Members
452,661
Latest member
Nonhle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top