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"}
]}
]}
 

sfluegge

New Member
Joined
Oct 7, 2013
Messages
9
Getting closer. I was indeed missing both Remove Errors and Expand. I found Expand but for the LIFE of me cannot find Remove Errors. I found REPLACE Errors and set that to blank but that didn't help. I am ALMOST there now... https://snag.gy/EPkxiK.jpg
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
when you start with PowerQuery ? :p

see this

parsejson.gif
 

sfluegge

New Member
Joined
Oct 7, 2013
Messages
9
Getting closer! I found the Expand command and that brought in the data. It still shows errors on all the "rows" from the text file that didn't have data. I also had to delete the leading coma's from the input lines of the file else Excel only saw the first line under each user.

So, where the heck is the Remove Errors option??? https://snag.gy/opEXQB.jpg
 

sfluegge

New Member
Joined
Oct 7, 2013
Messages
9
Winner Winner, Chicken Dinner!! Woo Hoo! Thanks a ton! I finally got through all the required steps and have output I can use. I will write myself a cheat sheet so as not to need to bother you again!!
 
Last edited:

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
I think you should learn where/what/how in PQ Editor

now you can click Like/Thanks buttons on the left bottom corner in post(s) which helped you

have a nice day
 

Watch MrExcel Video

Forum statistics

Threads
1,127,612
Messages
5,625,842
Members
416,138
Latest member
Pizzaman22

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