SOLVED

Import JSON from web to Excel - needing assistance

%3CLINGO-SUB%20id%3D%22lingo-sub-2024572%22%20slang%3D%22en-US%22%3EImport%20JSON%20from%20web%20to%20Excel%20-%20needing%20assistance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2024572%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Community%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20help%20out%20a%20colleague%20of%20mine.%20She%20has%20a%20JSON%20from%20the%20web%20that%20she%20wants%20to%20import%20into%20Excel%20to%20analyze%20the%20data.%20She%20found%20that%20we%20can%20import%20from%20JSON%20on%20the%20web.%20Personally%2C%20I%20have%20never%20worked%20with%20a%20JSON%20file%20before%20(and%20no%20one%20else%20in%20my%20organization%20has%20enough%20experience%20with%20Excel)%20so%20I%20thought%20I%20would%20give%20it%20a%20try.%20I%20went%20through%20and%20watched%20several%20tutorial%20videos%20and%20read%20files%20on%20how%20to%20do%20it.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20followed%20the%20steps%20and%20I%20feel%20like%20there%20is%20a%20lot%20more%20data%20that%20I%20am%20missing%20(I%20could%20be%20wrong).%20I%20attached%20the%20Excel%20spreadsheet%20on%20what%20I%20have%20and%20have%20a%20bunch%20of%20fields%20that%20come%20up%20as%20null.%20Is%20this%20correct%20or%20am%20I%20missing%20information%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20source%20to%20the%20JSON%20is%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.osfhealthcare.org%2Fmedia%2Ffiler_public%2F09%2F06%2F090664c0-95fa-4f19-932f-3a924fb7c67d%2F140067_saint-francis-medical-center_standardcharges.json%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.osfhealthcare.org%2Fmedia%2Ffiler_public%2F09%2F06%2F090664c0-95fa-4f19-932f-3a924fb7c67d%2F140067_saint-francis-medical-center_standardcharges.json%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20suggestions%20would%20be%20greatly%20appreciated.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2024572%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2026762%22%20slang%3D%22en-US%22%3ERe%3A%20Import%20JSON%20from%20web%20to%20Excel%20-%20needing%20assistance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2026762%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F492228%22%20target%3D%22_blank%22%3E%40Jschm001%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20try%20to%20load%20all%20sections%20of%20JSON%20file%20into%20the%20one%20table.%20Actually%20different%20sections%20(more%20exactly%20with%20different%20structure)%20shall%20go%20into%20separate%20table.%20In%20attached%20they%20are%20for%26nbsp%3BGross%20Charges%20and%20for%26nbsp%3BInpatient%20Payer%20Specific%20Charge%20(all%20sections%20since%20they%20have%20the%20same%20structure).%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAfter%20that%20perhaps%20you%20need%20to%20merge%20them%20to%20make%20final%20transformation%2C%20but%20that%20require%20knowledge%20of%20business%20logic%2C%20what%20is%20the%20data%20is%20structured%20and%20what%20we'd%20like%20to%20have%20in%20result.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2029371%22%20slang%3D%22en-US%22%3ERe%3A%20Import%20JSON%20from%20web%20to%20Excel%20-%20needing%20assistance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2029371%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20very%20much%20for%20the%20information!%20This%20will%20help%20a%20lot!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello Community,

 

I am trying to help out a colleague of mine. She has a JSON from the web that she wants to import into Excel to analyze the data. She found that we can import from JSON on the web. Personally, I have never worked with a JSON file before (and no one else in my organization has enough experience with Excel) so I thought I would give it a try. I went through and watched several tutorial videos and read files on how to do it. 

 

I followed the steps and I feel like there is a lot more data that I am missing (I could be wrong). I attached the Excel spreadsheet on what I have and have a bunch of fields that come up as null. Is this correct or am I missing information?

 

Any suggestions would be greatly appreciated. 

3 Replies
Best Response confirmed by Jschm001 (Occasional Contributor)
Solution

@Jschm001 

You try to load all sections of JSON file into the one table. Actually different sections (more exactly with different structure) shall go into separate table. In attached they are for Gross Charges and for Inpatient Payer Specific Charge (all sections since they have the same structure).

 

After that perhaps you need to merge them to make final transformation, but that require knowledge of business logic, what is the data is structured and what we'd like to have in result.

@Sergei Baklan 

 

Thank you very much for the information! This will help a lot!

@Jschm001 , you are welcome