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

Solution 1:

Since this is an operation that you would do once or a few times only, here is a zero-work solution, using the website Mr. 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 the Wayback Machine.

Solution 2:

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;
    }
});

Solution 3:

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.com It 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.