Some people find it hard to let go of Excel even when the situation is ripe for a database. And so dozens of different versions of the same spreadsheet edited by many people are floating around Sharepoint, and nobody quite knows which one to use.

Instead of changing everyone’s mind, I found the best I can do is maintain my own version of the spreadsheet in a database table. Eventually, when people get thoroughly confused, they’ll come to me.

I needed a shell script to take an arbitrary Excel spreadsheet and convert it into a database table. And I didn’t want to create the table manually either: I wanted it to be generated dynamically based on the spreadsheet itself.

And so here it is below (and you can also get it from my GitHub repo). You can install it like so:

The script relies on your spreadsheet having column headers. The script will also sanitize the input data as best as it can to work with MySQL. All the columns created will be VARCHAR(255). Additionally, you need to have unoconv installed to allow the script to convert XLSX to CSV. I have some instructions for this here.

The syntax is simple:

And here’s the script: