Home

Trouble loading JSON to Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-142145%22%20slang%3D%22en-US%22%3ETrouble%20loading%20JSON%20to%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-142145%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20JSON%20file%20that%20I%20would%20like%20to%20import%20into%20Excel%3C%2FP%3E%0A%3CP%3EI%20have%20Office%20365%3C%2FP%3E%0A%3CP%3EI%20am%20using%20the%20following%3A%3C%2FP%3E%0A%3CP%3E%3CA%20target%3D%22_blank%22%20data-topotext%3D%22%5B%7B%26quot%3Battributes%26quot%3B%3A%7B%26quot%3Btombstoned%26quot%3B%3Afalse%2C%26quot%3BuniqueId%26quot%3B%3A2%2C%26quot%3BparagraphStyle%26quot%3B%3A%7B%26quot%3Bstyle%26quot%3B%3A3%7D%7D%2C%26quot%3BcodePoints%26quot%3B%3A%5B85%2C115%2C101%2C32%2C69%2C120%2C99%2C101%2C108%2C39%2C115%2C32%5D%7D%2C%7B%26quot%3Battributes%26quot%3B%3A%7B%26quot%3Btombstoned%26quot%3B%3Afalse%2C%26quot%3BuniqueId%26quot%3B%3A4%2C%26quot%3BparagraphStyle%26quot%3B%3A%7B%26quot%3Bstyle%26quot%3B%3A3%7D%2C%26quot%3BfontHints%26quot%3B%3A1%7D%2C%26quot%3BcodePoints%26quot%3B%3A%5B71%2C101%2C116%2C32%2C38%2C32%2C84%2C114%2C97%2C110%2C115%2C102%2C111%2C114%2C109%2C32%2C40%2C80%2C111%2C119%2C101%2C114%2C32%2C81%2C117%2C101%2C114%2C121%2C41%5D%7D%2C%7B%26quot%3Battributes%26quot%3B%3A%7B%26quot%3Btombstoned%26quot%3B%3Afalse%2C%26quot%3BuniqueId%26quot%3B%3A2%2C%26quot%3BparagraphStyle%26quot%3B%3A%7B%26quot%3Bstyle%26quot%3B%3A3%7D%7D%2C%26quot%3BcodePoints%26quot%3B%3A%5B32%2C101%2C120%2C112%2C101%2C114%2C105%2C101%2C110%2C99%2C101%2C32%2C116%2C111%2C32%2C99%2C111%2C110%2C110%2C101%2C99%2C116%2C32%2C116%2C111%2C32%2C97%2C32%2C74%2C83%2C79%2C78%2C32%2C102%2C105%2C108%2C101%2C46%2C10%5D%7D%2C%7B%26quot%3Battributes%26quot%3B%3A%7B%26quot%3Btombstoned%26quot%3B%3Afalse%2C%26quot%3BuniqueId%26quot%3B%3A5%2C%26quot%3BparagraphStyle%26quot%3B%3A%7B%26quot%3Bstyle%26quot%3B%3A102%7D%7D%2C%26quot%3BcodePoints%26quot%3B%3A%5B67%2C108%2C105%2C99%2C107%2C32%2C116%2C104%2C101%2C32%5D%7D%2C%7B%26quot%3Battributes%26quot%3B%3A%7B%26quot%3Btombstoned%26quot%3B%3Afalse%2C%26quot%3BuniqueId%26quot%3B%3A6%2C%26quot%3BparagraphStyle%26quot%3B%3A%7B%26quot%3Bstyle%26quot%3B%3A102%7D%2C%26quot%3BfontHints%26quot%3B%3A1%7D%2C%26quot%3BcodePoints%26quot%3B%3A%5B68%2C97%2C116%2C97%2C32%5D%7D%2C%7B%26quot%3Battributes%26quot%3B%3A%7B%26quot%3Btombstoned%26quot%3B%3Afalse%2C%26quot%3BuniqueId%26quot%3B%3A5%2C%26quot%3BparagraphStyle%26quot%3B%3A%7B%26quot%3Bstyle%26quot%3B%3A102%7D%7D%2C%26quot%3BcodePoints%26quot%3B%3A%5B116%2C97%2C98%2C44%2C32%2C116%2C104%2C101%2C110%2C32%5D%7D%2C%7B%26quot%3Battributes%26quot%3B%3A%7B%26quot%3Btombstoned%26quot%3B%3Afalse%2C%26quot%3BuniqueId%26quot%3B%3A6%2C%26quot%3BparagraphStyle%26quot%3B%3A%7B%26quot%3Bstyle%26quot%3B%3A102%7D%2C%26quot%3BfontHints%26quot%3B%3A1%7D%2C%26quot%3BcodePoints%26quot%3B%3A%5B71%2C101%2C116%2C32%2C68%2C97%2C116%2C97%5D%7D%2C%7B%26quot%3Battributes%26quot%3B%3A%7B%26quot%3Btombstoned%26quot%3B%3Afalse%2C%26quot%3BuniqueId%26quot%3B%3A5%2C%26quot%3BparagraphStyle%26quot%3B%3A%7B%26quot%3Bstyle%26quot%3B%3A102%7D%7D%2C%26quot%3BcodePoints%26quot%3B%3A%5B32%2C62%2C32%5D%7D%2C%7B%26quot%3Battributes%26quot%3B%3A%7B%26quot%3Btombstoned%26quot%3B%3Afalse%2C%26quot%3BuniqueId%26quot%3B%3A6%2C%26quot%3BparagraphStyle%26quot%3B%3A%7B%26quot%3Bstyle%26quot%3B%3A102%7D%2C%26quot%3BfontHints%26quot%3B%3A1%7D%2C%26quot%3BcodePoints%26quot%3B%3A%5B70%2C114%2C111%2C109%2C32%2C70%2C105%2C108%2C101%5D%7D%2C%7B%26quot%3Battributes%26quot%3B%3A%7B%26quot%3Btombstoned%26quot%3B%3Afalse%2C%26quot%3BuniqueId%26quot%3B%3A5%2C%26quot%3BparagraphStyle%26quot%3B%3A%7B%26quot%3Bstyle%26quot%3B%3A102%7D%7D%2C%26quot%3BcodePoints%26quot%3B%3A%5B32%2C62%2C32%5D%7D%2C%7B%26quot%3Battributes%26quot%3B%3A%7B%26quot%3Btombstoned%26quot%3B%3Afalse%2C%26quot%3BuniqueId%26quot%3B%3A6%2C%26quot%3BparagraphStyle%26quot%3B%3A%7B%26quot%3Bstyle%26quot%3B%3A102%7D%2C%26quot%3BfontHints%26quot%3B%3A1%7D%2C%26quot%3BcodePoints%26quot%3B%3A%5B70%2C114%2C111%2C109%2C32%2C74%2C83%2C79%2C78%5D%7D%2C%7B%26quot%3Battributes%26quot%3B%3A%7B%26quot%3Btombstoned%26quot%3B%3Afalse%2C%26quot%3BuniqueId%26quot%3B%3A5%2C%26quot%3BparagraphStyle%26quot%3B%3A%7B%26quot%3Bstyle%26quot%3B%3A102%7D%7D%2C%26quot%3BcodePoints%26quot%3B%3A%5B46%2C10%2C66%2C114%2C111%2C119%2C115%2C101%2C32%2C116%2C111%2C32%2C121%2C111%2C117%2C114%2C32%2C74%2C83%2C79%2C78%2C32%2C102%2C105%2C108%2C101%2C32%2C108%2C111%2C99%2C97%2C116%2C105%2C111%2C110%2C44%2C32%2C115%2C101%2C108%2C101%2C99%2C116%2C32%2C105%2C116%2C44%2C32%2C97%2C110%2C100%2C32%2C99%2C108%2C105%2C99%2C107%2C32%5D%7D%2C%7B%26quot%3Battributes%26quot%3B%3A%7B%26quot%3Btombstoned%26quot%3B%3Afalse%2C%26quot%3BuniqueId%26quot%3B%3A6%2C%26quot%3BparagraphStyle%26quot%3B%3A%7B%26quot%3Bstyle%26quot%3B%3A102%7D%2C%26quot%3BfontHints%26quot%3B%3A1%7D%2C%26quot%3BcodePoints%26quot%3B%3A%5B79%2C112%2C101%2C110%5D%7D%2C%7B%26quot%3Battributes%26quot%3B%3A%7B%26quot%3Btombstoned%26quot%3B%3Afalse%2C%26quot%3BuniqueId%26quot%3B%3A5%2C%26quot%3BparagraphStyle%26quot%3B%3A%7B%26quot%3Bstyle%26quot%3B%3A102%7D%7D%2C%26quot%3BcodePoints%26quot%3B%3A%5B46%2C10%2C79%2C110%2C99%2C101%2C32%2C116%2C104%2C101%2C32%5D%7D%2C%7B%26quot%3Battributes%26quot%3B%3A%7B%26quot%3Btombstoned%26quot%3B%3Afalse%2C%26quot%3BuniqueId%26quot%3B%3A6%2C%26quot%3BparagraphStyle%26quot%3B%3A%7B%26quot%3Bstyle%26quot%3B%3A102%7D%2C%26quot%3BfontHints%26quot%3B%3A1%7D%2C%26quot%3BcodePoints%26quot%3B%3A%5B81%2C117%2C101%2C114%2C121%2C32%2C69%2C100%2C105%2C116%2C111%2C114%5D%7D%2C%7B%26quot%3Battributes%26quot%3B%3A%7B%26quot%3Btombstoned%26quot%3B%3Afalse%2C%26quot%3BuniqueId%26quot%3B%3A5%2C%26quot%3BparagraphStyle%26quot%3B%3A%7B%26quot%3Bstyle%26quot%3B%3A102%7D%7D%2C%26quot%3BcodePoints%26quot%3B%3A%5B32%2C104%2C97%2C115%2C32%2C108%2C111%2C97%2C100%2C101%2C100%2C32%2C121%2C111%2C117%2C114%2C32%2C100%2C97%2C116%2C97%2C44%2C32%2C99%2C108%2C105%2C99%2C107%2C32%5D%7D%2C%7B%26quot%3Battributes%26quot%3B%3A%7B%26quot%3Btombstoned%26quot%3B%3Afalse%2C%26quot%3BuniqueId%26quot%3B%3A6%2C%26quot%3BparagraphStyle%26quot%3B%3A%7B%26quot%3Bstyle%26quot%3B%3A102%7D%2C%26quot%3BfontHints%26quot%3B%3A1%7D%2C%26quot%3BcodePoints%26quot%3B%3A%5B67%2C111%2C110%2C118%2C101%2C114%2C116%5D%7D%2C%7B%26quot%3Battributes%26quot%3B%3A%7B%26quot%3Btombstoned%26quot%3B%3Afalse%2C%26quot%3BuniqueId%26quot%3B%3A5%2C%26quot%3BparagraphStyle%26quot%3B%3A%7B%26quot%3Bstyle%26quot%3B%3A102%7D%7D%2C%26quot%3BcodePoints%26quot%3B%3A%5B32%2C62%2C32%5D%7D%2C%7B%26quot%3Battributes%26quot%3B%3A%7B%26quot%3Btombstoned%26quot%3B%3Afalse%2C%26quot%3BuniqueId%26quot%3B%3A6%2C%26quot%3BparagraphStyle%26quot%3B%3A%7B%26quot%3Bstyle%26quot%3B%3A102%7D%2C%26quot%3BfontHints%26quot%3B%3A1%7D%2C%26quot%3BcodePoints%26quot%3B%3A%5B73%2C110%2C116%2C111%2C32%2C84%2C97%2C98%2C108%2C101%5D%7D%2C%7B%26quot%3Battributes%26quot%3B%3A%7B%26quot%3Btombstoned%26quot%3B%3Afalse%2C%26quot%3BuniqueId%26quot%3B%3A5%2C%26quot%3BparagraphStyle%26quot%3B%3A%7B%26quot%3Bstyle%26quot%3B%3A102%7D%7D%2C%26quot%3BcodePoints%26quot%3B%3A%5B44%2C32%2C116%2C104%2C101%2C110%2C32%5D%7D%2C%7B%26quot%3Battributes%26quot%3B%3A%7B%26quot%3Btombstoned%26quot%3B%3Afalse%2C%26quot%3BuniqueId%26quot%3B%3A6%2C%26quot%3BparagraphStyle%26quot%3B%3A%7B%26quot%3Bstyle%26quot%3B%3A102%7D%2C%26quot%3BfontHints%26quot%3B%3A1%7D%2C%26quot%3BcodePoints%26quot%3B%3A%5B67%2C108%2C111%2C115%2C101%2C32%2C38%2C32%2C76%2C111%2C97%2C100%5D%7D%2C%7B%26quot%3Battributes%26quot%3B%3A%7B%26quot%3Btombstoned%26quot%3B%3Afalse%2C%26quot%3BuniqueId%26quot%3B%3A5%2C%26quot%3BparagraphStyle%26quot%3B%3A%7B%26quot%3Bstyle%26quot%3B%3A102%7D%7D%2C%26quot%3BcodePoints%26quot%3B%3A%5B46%5D%7D%5D%22%3E%20%3C%2FA%3EUse%20Excel's%20Get%20%26amp%3B%20Transform%20(Power%20Query)%20experience%20to%20connect%20to%20a%20JSON%20file.%3C%2FP%3E%0A%3CDIV%3EClick%20the%20Data%20tab%2C%20then%20Get%20Data%20%26gt%3B%20From%20File%20%26gt%3B%20From%20JSON.%3C%2FDIV%3E%0A%3CDIV%3EBrowse%20to%20your%20JSON%20file%20location%2C%20select%20it%2C%20and%20click%20Open.%3C%2FDIV%3E%0A%3CDIV%3EOnce%20the%20Query%20Editor%20has%20loaded%20your%20data%2C%20click%20Convert%20%26gt%3B%20Into%20Table%2C%20then%20Close%20%26amp%3B%20Load.%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3EI%20only%20receive%20two%20rows%20in%20my%20excel%20spreadsheet%20but%20there%20is%20quite%20a%20bit%20of%20data.%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3EHelp%20appreciated.%3C%2FDIV%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CDIV%3E-JP%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-142145%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analytics%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ENeed%20Help%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-357364%22%20slang%3D%22en-US%22%3ERe%3A%20Trouble%20loading%20JSON%20to%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-357364%22%20slang%3D%22en-US%22%3EUse%20Excel's%20Get%20%26amp%3B%20Transform%20(Power%20Query)%20experience%20to%20connect%20to%20a%20JSON%20file.%3CBR%20%2F%3E%3CBR%20%2F%3EClick%20the%20Data%20tab%2C%20then%20Get%20Data%20%26gt%3B%20From%20File%20%26gt%3B%20From%20JSON.%3CBR%20%2F%3E%3CBR%20%2F%3EBrowse%20to%20your%20JSON%20file%20location%2C%20select%20it%2C%20and%20click%20Open.%3CBR%20%2F%3E%3CBR%20%2F%3EOnce%20the%20Query%20Editor%20has%20loaded%20your%20data%2C%20click%20Convert%20%26gt%3B%20Into%20Table%2C%20then%20Close%20%26amp%3B%20Load.%20I%20think%20XML%20Converter%20can%20do%20it%20easily%20for%20you%20as%20well%20%3CA%20href%3D%22https%3A%2F%2Fwww.coolutils.com%2FTotalXMLConverter%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.coolutils.com%2FTotalXMLConverter%3C%2FA%3E%3C%2FLINGO-BODY%3E
James Palmer
Visitor

I have a JSON file that I would like to import into Excel

I have Office 365

I am using the following:

Use Excel's Get & Transform (Power Query) experience to connect to a JSON file.

Click the Data tab, then Get Data > From File > From JSON.
Browse to your JSON file location, select it, and click Open.
Once the Query Editor has loaded your data, click Convert > Into Table, then Close & Load.
 
I only receive two rows in my excel spreadsheet but there is quite a bit of data.
 
Help appreciated.
 
-JP
1 Reply
Use Excel's Get & Transform (Power Query) experience to connect to a JSON file.

Click the Data tab, then Get Data > From File > From JSON.

Browse to your JSON file location, select it, and click Open.

Once the Query Editor has loaded your data, click Convert > Into Table, then Close & Load. I think XML Converter can do it easily for you as well https://www.coolutils.com/TotalXMLConverter
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies