How to Import JSON Into Excel

    Nisha GurungBy Excel 5 Mins Read

    JSON (JavaScript Object Notation) files are mostly used by developers as a standard file format to record and exchange programming contents. 

    Although the texts are pretty easy to read even for non-technical users, data comparison is difficult in this file type. If you have a JSON file, you can import them into Excel for data analysis. 

    Excel’s Power Query Tool supports the JSON file format. So, when you load the files, it will automatically detect the table for contents and change them into columns. In short, you can easily convert and organize the texts of .json files in sheets.  

    Using Power Query

    Firstly, if you have existing JSON files on your PC, you can load them directly from the Power Query.

    Power Query is hands down the best-integrated technology in Excel that helps you to import different file formats from external data sources. For example, JSON, XML, PDF, CSV, and many more. While loading the files, you can also transform the data and convert them into a table to shape them in the sheets accordingly. 

    Using the Power Query, you can import a single JSON file or an entire folder containing multiple JSON files. 

    Import Single JSON Files

    To import a single JSON file, we have divided the steps into two. Firstly, we will import the file from the PC to the Power Query Editor tool. Then, we will convert the contents of JSON into a Table.

    Also since the columns are collapsed at first, we will expand all the values. Then, finally, load the data into Sheet. 

    Step 1: Import Data

    1. Launch Excel and open a Blank Workbook.
    2. Go to Data Tab. In the Get & Transform Data section, click Get Data.
      Go to Data Tab. In the Get & Transform Data section, click Get Data
    3. Hover over From File > From JSON. 
      Hover over From File-From JSON
    4. On the Import Data window, locate the JSON file on your PC. Select the JSON File and click Import.
      Select the JSON file and click Import

    Step 2: Transform and Load Data

    1. On the Power Query Editor window, stay on the Convert tab. Then, click on Into Table.
      On the Power Query Editor window, stay on the Convert tab. Then, click on Into Table
    2. If you have a Column with values, select the Expand Icon and choose Expand to New Rows.
      select the Expand Icon and choose Expand to New Rows
    3. Again, click on the Expand icon and hit OK. This will show all values in the table now.
      click on the Expand icon and hit OK
    4. From the Home Tab, click Close & Load.
      From the Home Tab, click Close & Load
    5. You’ll have the data in your Sheet.
      You'll have the data in your Sheet

    Import Multiple JSON Files

    The steps to import the folder with multiple JSON files are pretty similar to loading single files. For users who have JSON files all over the place on their PC, I recommend you create a New folder first. Then, move all of the JSON files to that new Folder. 

    1. On Excel, open Blank Workbook.
    2. From the Data Tab, click on Get Data.
      From the Data Tab, click on Get Data
    3. Select From File > From Folder.
      Select From File-From Folder
    4. On Import Data, pick the Folder with JSON files and hit Open.
      On Import Data, pick the Folder with JSON files and hit Open
    5. On the new window, click Combine > Combine & Transform Data.
      On the new window, click Combine-Combine & Transform Data
    6. In the Power Query Editor window, you’ll see that all contents of JSON files are already converted into Tables and expanded into Columns.
      Transformed JSON file data in Power Query Editor
    7. From the Home Tab, click Close & Load.
      From the Home Tab, click Close & Load

    From Web

    Sometimes, you may wish to import the JSON files directly from the Online source. Now, to do that, you can import Data from the Web. Remember, here, even though we are extracting files from a different source, we will still use the Power Query Editor tool. So, you can transform and load the data. 

    Step 1: Import File

    1. On your Excel Workbook, click on Data.
    2. Hover over the Get & Transform Data group and click on From Web.
      Hover over the Get & Transform Data group and click on From Web
    3. Go to the web browser with the JSON file and Copy the link. Then, paste the link in the URL field and click OK.
      Go to the online JSON file and Copy the link. Then, paste the link in the URL field and click OK
    4. On Access Web content, click Connect.
      On Access Web content, click Connect
    5. On Navigator, select the Result to see the information. Then, click on Transform Data.
      On Navigator click on Transform

    Step 2: Transform and Load Data

    1. On the Power Query Editor window, click the Expand icon on the column and click OK.
      On Power Query Editor window, click the Expand icon on the column and click OK
    2. If you have more columns with Expand icon, click on it. Then, hit OK. Repeat the steps for other column too.
      If you have more columns with Expand icon, click on it. Then, hit OK
    3. Once you have all the Columns in the display, go to Home Tab. Click Close & Load.
      go to Home Tab and Click Close & Load

    Save and Export JSON Files in Excel File Format

    After you’ve edited the JSON contents, you can save the workbook in .xlsx Excel File formats. So, you do not have to use any third-party online tools to convert the JSON file into Excel format.

    Remember, by default, Excel saves all the files in Excel Workbook (*xlsx) format unless you set a different File format.

    If you want to save the file to the drive, enter Ctrl + S. On the Save this File, enter a file name in the Field and click Save.

    However, to export the workbook to your PC, follow these steps.

    1. Go to the File tab > Save As.
      Go to the File tab-Save As
    2. Select Browse.
      Select Browse
    3. On Save As window, enter a Name in the File Name field.
      Enter a File Name
    4. On Save as type, choose a different Excel file format if you want to. Then, click Save.
      On Save as type, choose a different Excel file format if you want to. Then, click Save

    “We found an unexpected character in the JSON input” Error

    Excel will send you the “We found an unexpected character in the JSON input” error if you attempt to import a file that is not in the .json format. During that case, click Edit in the error box.

    Then, on the JSON window, see the file extension name in the File path. 

    If you wish to import a different document, click Browse. Select a .json file and choose Import. Click OK on the dialogue box. This should solve the error and load the file in the Power Query Editor tool. 

    As a Business Student, Nisha first used Excel to outline data trends for her college projects. Later, she started exploring basic Excel functions when she realized it is a powerful tool for all businesses today. Nisha believes she can relate to many of the issues users face when starting their Excel Journey. She uses this knowledge and experience to curate contents that are fit for all types of Excel users. Apart from writing, she enjoys reading books and traveling.

    Related Posts

    Add A Comment