import json files

[pyclen]

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

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

 

[James006]

Hi,
If you want to master the topic of JSON files, below is a complete explanation :

How to Import JSON to Excel Using VBA | Excelerator Solutions

Learn to Import JSON to Excel! Convert your JSON strings to data tables for analysis by using VBA-JSON and your own code!

excelerator.solutions

 

[jdellasala]

JSON files can be imported quickly, reliably, and safely with Power Query using just the UI.

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.

 

[pyclen]

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

 

[jdellasala]

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.