What is the easiest way to convert an Excel spreadsheet with tabular data to JSON?

Question

I want to convert spreadsheet data from Excel or Open Office saved as *.xls to *.json

Data is not sensitive
File is not very large

Found this macro, not yet tested: https://gist.github.com/aaronhoogstraten/49b9c0f5e4ac705ebe51 — Lee Goddard, Feb 19 '15 at 12:54
There's also this: http://thdoan.github.io/mr-data-converter/ — thdoan, Sep 19 '16 at 09:51
try this free app from windows store https://www.microsoft.com/en-us/store/p/excel-into-json/9p3b2s2k2qhb — Pomodoro Technique Game, Nov 03 '17 at 14:56
Mr. Data Converter. https://shancarter.github.io/mr-data-converter/ — mg1075, Aug 29 '20 at 02:49
There's a simple way which involves no tools other than Excel. Use a formula to output JSON objects like so: `="{ ""thingA"":""" & A2 & """, ""thingB"":" & B2 & """ }, "`. This will output your JSON object rows which you can copy and paste inside `[ ]` to get your JSON array. e.g. if the A2 value is `cat` and the B2 value is `dog` you would get: `{ "thingA":"dog", "thingB":cat" }, ` for this row. Drag the row down to fill out the spreadsheet. Double `""` will escape the single `"` character. Because it's a formula you can be selective about what you include and name properties as well. — Aaron Newton, Mar 03 '22 at 03:15
Slight improvement to Aaron's formula, uses first row headers as property names / keys: `="{ """&A$1&""":""" & A2 & """, """&B$1&""":""" & B2 & """ }, "` — James Daily, Jun 26 '23 at 15:06
I am not sure why this question is closed, my first search brought me to this question and it is exactly what I was looking. — Vivek Shukla, Aug 30 '23 at 17:48

score 185 · Accepted Answer · edited Jul 21 '20 at 19:02

Assuming you really mean easiest and are not necessarily looking for a way to do this programmatically, you can do this:

Add, if not already there, a row of "column Musicians" to the spreadsheet. That is, if you have data in columns such as:
```
Rory Gallagher      Guitar
Gerry McAvoy        Bass
Rod de'Ath          Drums
Lou Martin          Keyboards
Donkey Kong Sioux   Self-Appointed Semi-official Stomper
```
Note: you might want to add "Musician" and "Instrument" in row 0 (you might have to insert a row there)
Save the file as a CSV file.
Copy the contents of the CSV file to the clipboard
Go to http://www.convertcsv.com/csv-to-json.htm
Verify that the "First row is column names" checkbox is checked
Paste the CSV data into the content area

Mash the "Convert CSV to JSON" button

With the data shown above, you will now have:

[
  {
    "MUSICIAN":"Rory Gallagher",
    "INSTRUMENT":"Guitar"
  },
  {
    "MUSICIAN":"Gerry McAvoy",
    "INSTRUMENT":"Bass"
  },
  {
    "MUSICIAN":"Rod D'Ath",
    "INSTRUMENT":"Drums"
  },
  {
    "MUSICIAN":"Lou Martin",
    "INSTRUMENT":"Keyboards"
  }
  {
    "MUSICIAN":"Donkey Kong Sioux",
    "INSTRUMENT":"Self-Appointed Semi-Official Stomper"
  }
]

With this simple/minimalistic data, it's probably not required, but with large sets of data, it can save you time and headache in the proverbial long run by checking this data for aberrations and abnormalcy.

Go here: http://jsonlint.com/
Paste the JSON into the content area
Pres the "Validate" button.

If the JSON is good, you will see a "Valid JSON" remark in the Results section below; if not, it will tell you where the problem[s] lie so that you can fix it/them.

Doesn't really work if you want to create a property with an array of items. such as having multiple instruments. — Coded Container, Oct 02 '15 at 17:25
I found the answer to converting large amounts of data online, for free: Upload your files to your Google Drive, to make it a Google Sheets document. Then follow this blog post from Pamela Fox http://blog.pamelafox.org/2013/06/exporting-google-spreadsheet-as-json.html — conor909, Dec 14 '15 at 15:36
I use http://www.csvjson.com/csv2json. Easy to convert from CSV to JSON — jprism, Jul 05 '17 at 18:41
If you have UTF-8 encoded data, exporting to CSV from Excel will mess them up. I prefer to just copy the data from Excel, paste them in a decent editor (vscode, sublime text, notepad++) and use regular expressions to convert them to JSON. — AsGoodAsItGets, Mar 29 '19 at 10:37

What is the easiest way to convert an Excel spreadsheet with tabular data to JSON?

1 Answers1

Linked