How To Convert Excel Spreadsheet to JSON

6

We’ve covered a neat trick that leverages Google spreadsheet as a JSON back-end service API for developers who are making any REST applications. However, the use case of this trick is limited if not difficult to adapt in production applications. Often times there is a layer gap within the raw data format between product managements to end developers. Business people tend to use Microsoft office products to store and exchange information, but when those data needs to be used in a product converting to a different format like JSON the process could be tedious. I’ve personally experienced this, and have found a great solution to help automate any Excel spreadsheet data into customized JSON formats.

This is a step-by-step guide on how to convert any Excel spreadsheet into JSON formats leverage Excel’s Developer features

First, we need to enable Developer tab if you haven’t enabled yet. Go to the start page > Options

2015 02 09 1018 - How To Convert Excel Spreadsheet to JSON

Under Excel Options > Customize Ribbon > Developer. Make sure on the right column, Customize the Ribbon the Developer tab is checked.

2015 02 09 0933 600x489 - How To Convert Excel Spreadsheet to JSON

Now, you will see a new tab called “DEVELOPER” showing, click to expand the menu and click Source. This will launch and expand the XML Source configurations. Click XML Maps … to bring up XML sample datasets or XML schema file (.xsd).

Now, it’s important to understand what’s going on here. We are trying to tell Excel to use XML schema to map the data from spreadsheet columns to the data expected from the XML schema. You don’t need to have a .xsd schema file ready, in fact, the easiest way to tell Excel what your data are going to look like is to create a sample XML with at least two repeated nodes.

2015 02 09 1019 600x436 - How To Convert Excel Spreadsheet to JSON

Below is an example of a sample XML that contains a repeated item, each will have a model and description field. This is extremely simple to create and whole lot easier than dig your head into XML schema and learning all the new sets of syntax, which itself could take days.

<xml version=”1.0″ encoding=”UTF-8″ ?>
<items>
<item>
<model>MODEL_ID_100</model>
<description>model descriptions</description>
</item>
<item>
<model>MODEL_ID_101</model>
<description>model descriptions</description>
</item>
</items>
</xml>

2015 02 09 1020 - How To Convert Excel Spreadsheet to JSON

Now click “Add..” to include the sample XML we’ve just created.

2015 02 09 1022 600x338 - How To Convert Excel Spreadsheet to JSON2015 02 09 1022 001 - How To Convert Excel Spreadsheet to JSON

At this point, Excel detected that this is not a XML schema, but it understands this is a valid XML and it will generate a schema for us to use based on this sample.xml.

2015 02 09 1026 - How To Convert Excel Spreadsheet to JSON

Once you have clicked OK. Excel will show you the generated schema with its corresponding names, root and namespace etc.

2015 02 09 1028 600x435 - How To Convert Excel Spreadsheet to JSON

Now all you need to do is drag the filed into corresponding columns. OR you can right-click on the model and select the range of elements map to.

2015 02 09 1029 600x500 - How To Convert Excel Spreadsheet to JSON2015 02 09 1029 001 600x634 - How To Convert Excel Spreadsheet to JSON

What you get after you have successfully mapped the columns is something like above, you will be able to sort and do all kinds things with those data. What we want is to export it into a valid XML file. You can do so by going to DEVELOPER tab and click “Export“.

2015 02 09 1030 600x123 - How To Convert Excel Spreadsheet to JSON

This will export the data into a XML file and from this point on your life should be much easier. There are online tools out there would help you to convert the XML into JSON. Below is a screenshot that uses http://codebeautify.org/ to convert the XML from Excel schema mapping into JSON.

2015 02 09 1006 600x435 - How To Convert Excel Spreadsheet to JSON

Hope you find this guide helpful, data conversions between legacy file formats to modern formats can be tedious and troublesome. I’m always a fan of automation, trying to find the right tool and leverage them coherently isn’t an easy task, but once you’ve masted them your life will be whole lot easier.

SHARE

6 COMMENTS

  1. The code for the Xml should be as follows :

    MODEL_ID_100
    model descriptions

    MODEL_ID_101
    model descriptions

    After this , only it worked for me

  2. Hi there,
    I think I can create a completely json without need to convert from xml to json.
    What I did is to user SUBSTITUTE to replace a string within a schema of json file.
    For example: I have a schema which I put on value of cell A1, like :
    {
    “name”:”string”
    }
    I will create a list of name in excel, and on a certain row (maybe at the last cell at last row) I will use a formula like: =CLEAN(SUBSTITUTE($A$1,”string”,B1)) and what I got is
    {
    “name”:”Anna”
    }

    Just another way to create test data for my work 🙂
    Hopefully that will be helpful.

  3. Didn’t work 🙁 Excel complains about:
    Error in XML :
    Cannot load the specified XML or schema source

    Details:
    Error Code: -1072896766
    URL:
    Reason: A string literal was expected, but no opening quote character was found.
    Line : 1
    Column : 14
    File Offset: 13

    Here’s my XML file:

    MODEL_ID_100
    model descriptions

    MODEL_ID_101
    model descriptions

  4. had to actually restructure the sample XML to this here:

    “MODEL_ID_100”
    “model descriptions”

    “MODEL_ID_101”
    “model descriptions”

LEAVE A REPLY

Please enter your comment!
Please enter your name here