Processing tabular data is easy in JavaScript. Spreadsheet are a convenient way to enter, edit and store tabular data. I've tried various open source alternatives, but Excel is still the simplest solution. Unfortunately, Excel spreadsheets are in XLSX files. This is a rather complex, somewhat open XML format, that is difficult to parse. I use NodeJS for a lot of stuff, I haven't found NPM modules to read XLSX directly, so I decided to look for a convertor. There are lot's of options if you want a commerical product. An open source program to do this is difficult to find.

After searching for a while I realized R (https://www.r-project.org/) is specifally designed to process tabular data from all sorts of sources. I decided to write a simple script.

The readxl R package can read XLSX files into a table. Assuming the data does not contain a tab character, the table can be output to a Tab Separated Values (TSV) file. A TSV file is easy to parse in JavaScript.

Using the following R program from the command line will do the conversion:

require('readxl')
a <- readxl::read_xlsx("inventory_data.xlsx")
write.table(a, file = "inventory_data.txt", sep = "\t", na = "", quote = FALSE, row.names = FALSE)

This program is executed from the command line using this command:

R -f xltotv.R

At this point, the TSV file can be read using NodeJS, into an array and the rows split into fields. It's even possible to use the first row as the attributes of an object. There are NPM packages to do this, but it's rather simple. Let's create a getDB() function to return the db and field names:

// Location	Container	Box	Item	Qty	Value
import fs from 'fs';

// convert TSV to JSON using first line as field names
function getDB() {
  const invTSV = fs.readFileSync('inventory_data.txt', 'utf-8');
  const lines = invTSV.split('\n');
  const fields = lines[0].replace('\r', '').split('\t').slice(0, 6);
  const db = [];
  for (let i = 1; i < lines.length; i++) {
    const line = lines[i].replace('\r', '');
    const entry = {};
    for (let j = 0; j < fields.length; j++) {
      const values = line.split(/ *\t/g);
      entry[fields[j]] = values[j];
    }
    db.push(entry);
  }
  return [db, fields];
}

Converting each line to rows in an array is easily done by "splitting" the long string at the newline character at the end of each line.

The TSV files are in DOS format, so the \r character needs to be removed.

In this example each row is converted to an object using {} to create the JSON object, then adding attributes using [attribute] notation. This creates the objects in familiar JSON notation:

{attribute:"value"}

Processing the array of objects is now a simple matter of using the Array methods, such as map, forEach, etc.