Import JSON into Excel 2019

sfluegge

New Member
Joined
Oct 7, 2013
Messages
9
I have a Powershell script that pulls all of the Outlook Email Rules in our Office 365 Domain. I would like to output it to JSON as CSV drops columns. I have the export working fine but cannot seem to get the data to load into Excel.

I have followed several videos on how to go to Data --> Get Data --> From File --> JSON and select my datafile. This part works fine. Using the attached sample JSON file I open it, select List then Convert to Table --> Close and Load. It runs and the output shows no usable results...

Below is the content of my JSON file... (didn't see a way to attach it here).

Any help is greatly appreciated!

{"Rules":[ {"Danielle":[
{"priority":1,"user":"danielle@mycompany.com","name":"Auto-Receipt (3)","condition1":"the message was received from \u0027Auto-Receipt\u0027","action1":"move the message to folder \u0027Company.net\u0027","action2":"and stop processing more rules on this message"}
,{"priority":2,"user":"danielle@mycompany.com","name":"Auto-Receipt (2)","condition1":"the message was received from \u0027Auto-Receipt\u0027","condition2":"and the message includes specific words in the subject \u0027Merchant Email Receipt\u0027","action1":"move the message to folder \u0027Company.net\u0027","action2":"and stop processing more rules on this message"}
,{"priority":3,"user":"danielle@mycompany.com","name":"Auto-Receipt (1)","condition1":"the message was received from \u0027Auto-Receipt\u0027","condition2":"and the message includes specific words in the subject \u0027Successful Report.\u0027","action1":"move the message to folder \u0027Company.net\u0027","action2":"and stop processing more rules on this message"}
]},
{"Scott":[
{"priority":1,"user":"scott@mycompany.com","name":"Synchronization with your iPhone (Migrated)","condition1":"the message includes specific words in the subject \u0027Synchronization with your iPhone\u0027","action1":"move the message to folder \u0027Junk Email\u0027","action2":"and stop processing more rules on this message"}
,{"priority":2,"user":"scott@mycompany.com","name":"Synchronization with your iPhone failed (Migrated)","condition1":"the message includes specific words in the subject \u0027Synchronization with your iPhone failed\u0027","action1":"move the message to folder \u0027Junk Email\u0027","action2":"and stop processing more rules on this message"}
,{"priority":3,"user":"scott@mycompany.com","name":"888VoIP: New Order (Migrated)","condition1":"the message includes specific words in the subject \u0027888VoIP: New Order \u0027","action1":"forward the message to \u0027Purchasing\u0027"}
,{"priority":4,"user":"scott@mycompany.com","name":"Synchronization with your iPhone failed (Migrated)","condition1":"the message includes specific words in the subject \u0027Synchronization with your iPhone failed \u0027","action1":"move the message to folder \u0027Junk Email\u0027","action2":"and stop processing more rules on this message"}
]}
]}
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
something like this?

priorityusernamecondition1action1action2
1​
danielle@mycompany.comAuto-Receipt (3)the message was received from 'Auto-Receipt'move the message to folder 'Company.net'and stop processing more rules on this message
1​
scott@mycompany.comSynchronization with your iPhone (Migrated)the message includes specific words in the subject 'Synchronization with your iPhone'move the message to folder 'Junk Email'and stop processing more rules on this message
 

sfluegge

New Member
Joined
Oct 7, 2013
Messages
9
OMG Yes! That is exactly what I WANT to see but am somehow UNABLE to... It is likely user error but I SWEAR I have tried every combination of buttons. In fact, I felt like a monkey banging on the keyboard in the end just HOPING to get it to work! Please tell me how you did it! (and clearly I need STEP BY STEP instruction as I am not a SKILLED monkey...)
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
first: change your file.json to file.json.txt
then: from file - txt:
Code:
[SIZE=1]// json
let
    Source = Table.FromColumns({Lines.FromBinary(File.Contents("[I][COLOR="#FF0000"]path to the file[/COLOR][/I]\[COLOR="#0000FF"]file.json.txt[/COLOR]"), null, null, 1252)}),
    Parse = Table.TransformColumns(Source,{},Json.Document),
    RemError = Table.RemoveRowsWithErrors(Parse, {"Column1"}),
    Expand = Table.ExpandRecordColumn(RemError, "Column1", {"priority", "user", "name", "condition1", "action1", "action2"}, {"priority", "user", "name", "condition1", "action1", "action2"})
in
    Expand[/SIZE]
 
Last edited:

sfluegge

New Member
Joined
Oct 7, 2013
Messages
9

ADVERTISEMENT

OK, now I get to embarrass myself further and tell you that your answer confuses me... Changing the file name is easy (though the purpose is mysterious). Then you lose me. Are you advocating that I add VM Code to an empty spreadsheet to load in the information? If so, then this greatly reduces the usefulness of using Excel as a solution... In your example you explicitly stated the number of rows in your Expand statement. I will have a variable number in a real use scenario and it will be in the hundreds of entries... I was hoping I could use the data import which calls the Power Query Editor. Will that NOT work?
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
too many words for too little action :)

Data - New Query - From File - From Text
you will get new query then open PQ Editor and Parse this column as JSON, next Remove Errors
you will get records then Expand.
that's all​

edit:
based on your example from the first post

btw. layout is a little different 2016 vs 2019
 
Last edited:

sfluegge

New Member
Joined
Oct 7, 2013
Messages
9

ADVERTISEMENT

Hmm... Maybe my issue is using Excel 2019? I don't HAVE that option (New Query). When I expand the Data --> Get Data I see the following: https://snag.gy/lx60wK.jpg

In my previous attempts I selected From JSON.

Following your suggestions I tried From Text/CSV and open the newly named JSON (now O365_Review_Rules - Copy.json.txt). This is what I get: https://snag.gy/RtYbNA.jpg

It is either a versioning issue OR I am missing something REALLY basic...
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
second snap ! that's it !
now

Parse = Table.TransformColumns(Source,{},Json.Document),

screenshot-65.png

then Expand
 
Last edited:

sfluegge

New Member
Joined
Oct 7, 2013
Messages
9
Sorry. Still no love...

Loading as specified (Data --> Get Data --> From Text
I am at a screen similar to what you show above (https://snag.gy/0mreRg.jpg)

From here I selected Edit --> Transform --> Parse --> JSON (https://snag.gy/TGguCB.jpg)

That is where Excel craps the bed and turns out nothing but errors... (https://snag.gy/7vKpkY.jpg)

I am sure you are regretting offering assistance byt this point, but it IS appreciated!!
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
you did these steps:


  1. Source = Table.FromColumns({Lines.FromBinary(File.Contents("json.txt"), null, null, 1252)}),
  2. Parse = Table.TransformColumns(Source,{},Json.Document),
but where are the next?
should be two more :


  • [3]Remove Errors
    [4]Expand


RemError = Table.RemoveRowsWithErrors(Parse, {"Column1"}),
Expand = Table.ExpandRecordColumn(RemError, "Column1", {"priority", "user", "name", "condition1", "action1", "action2"}, {"priority", "user", "name", "condition1", "action1", "action2"})
 

Watch MrExcel Video

Forum statistics

Threads
1,127,316
Messages
5,623,955
Members
416,002
Latest member
t10k14

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
Top