How do I convert an excel table to a JSON array?

I need to enter large amount of data in an array, which will be in this form:

[ [data1,data2,data3,...,data9], [data1,data2,data3,...,data9], [data1,data2,data3,...,data9], ...
]

As it stands, entering the data directly as JSON is a time consuming process that tends to have many errors. Alternatively, I would like to enter the data into an excel sheet and generate the JSON array from the sheet. How can I do this?

4 Answers

Since this is an operation that you would do once or a few times only, here is a zero-work solution, using the websiteMr. Data Converter, apparently created by a Shan Carter.

Here is how I converted this dummy sample spreadsheet of 679 duplicated rows :

1. Select the entire data using Ctrl+A
2. Copy to the clipboard with Ctrl+C
3. Open the website Mr. Data Converter
4. Click in the upper text area and paste-in the data with Ctrl+V
5. Select Output as "JSON - Row Arrays"
6. The result in the lower text area will look like :

7. Click in the lower text area and it will be selected
8. Copy to the clipboard with Ctrl+C

You may now paste the data anywhere and add around it the declaration for the JavaScript array.

Note for the future : If the Mr. Data Converter website ever disappears from the Internet, a working copy of it can be found on theWayback Machine.

I believe you could use Papa Parse, which is a JavaScript tool to parse .csv files.

First, input all your data to Excel, and save the file as a .csv

Next use NPM to install Papa Parse:

$ npm install papaparse

Import your .csv to JS:

var file = '/path/to/your.csv';
var content = fs.readFileSync(file, "utf8");

Then use this code to parse the .csv to an array:

var Papa = require('papaparse');
Papa.parse(content, { header: false, delimiter: "\t", complete: function(results) { rows = results.data; }
});

I used Mr Data Converter as well for a while, but it lacks with missing support of quotes, commas line brakes etc. and the last update was 6 years ago.

Imho a pretty good alternative is convertcsv.comIt has great features (especially considering line brakes, if you take the data easily from an XLS per copy&paste).

For the record from their privacy policy:

We do not collect data inputted or outputted via our conversion services.

Assuming you have , as delimiter in a plain-text .csv file you can simply do this:

var csvarray = [];
var client = new XMLHttpRequest();
client.open('GET', '/mydata.csv');
client.onreadystatechange = function() { var rows = client.responseText.split('\n'); for(var i = 0; i < rows.length; i++){ csvarray.push(rows[i].split(',')); }
}
client.send();

csvarray will contain one array per row of the csv, containing it's comma-separated data.