Here’s how you can import a .json File in Excel in just 8 steps and be able to analyze your data easily.
Introduction
Recently I took some advice from the guys over at Income School about blog writing. They suggested looking at your own browser history for topics. Being the nerd that I am, I wanted to pick over my entire history in excel if possible.
Once I figured out how to export my chrome history, I found it was in a .json format. The file extension .json stands for JavaScript Object Notation
Step 1: Install Power Query in Excel
Go to the Microsoft website at this link
Step 2: Install Power Query
Follow the prompts to install the software.
Step 3: Open Power Query
Open Excel, and at the end of the ribbon, click Power Query. This. will open a new window.
Step 4: Open the .json File
Now click from other sources and choose Blank Query. Next, click advanced, editor. A new window will open.
Step 5: Enter Code
Click Home on the ribbon and choose Advanced Editor
When the window opens copy and paste this:
let
Source = Json.Document(File.Contents(“C:\Users\Name\Desktop\JSONTest.json”)), #”Converted to Table” = Record.ToTable(Source)
in
#”Converted to Table”
into the advanced editor. Put your path in the area with the quotes. Be sure to have the quotes in place. One easy way to get the path is to go to the file’s location in file explorer and right-click the path and choose copy as text. Now, you can paste it and then add a backslash and the file name.
Step 6: Expand the Data
This is where our paths may differ. I was searching through my chrome history for blog topics. If this is what you’re doing, you should be good to go!
Click here and choose “Expand to new Rows”
Step 7: Choose Your Data
Choose your expanded data by checking the boxes and click OK.
Step 8: Close and Load
On the left choose close and Load
Conclusion
That’s it! that how to import a .json File in Excel. Not to bad huh? What kind of data are you converting? Leave a comment and sign up below.
Check out our latest posts for other tutorials and reviews!
