3

Apologies if this doesn't make sense, English is not my first language.

I'm working on an application where workflow is split into 'rules' that are defined on several pages of an Excel spreadsheet, a VB script strips out the descriptions of the states etc and leaves only numbers, then exports this configuration as MySQL that is then imported into the database from which the application works.

We have 2-3 developers working on the spreadsheet. Often the spreadsheet is opened from it’s location on a shared network drive, minor changes made and sometimes the version number is not incremented.

This makes it very hard to track changes and also who made them and why. Ideally we would pull the spreadsheet to a working directory, do a diff and commit changes then push them to a master repository. It seems like there are no viable tools to do such a thing with Excel.

So my question is what would be a better way to support a very large configuration, make it version controllable and still human readable?

blarg
  • 151
  • 5
  • Human readabale by who? Also, do you have formulas in your spreadsheet? – Florian Margaine Nov 20 '14 at 15:08
  • What I mean by that is the states are stored in DB tables as numbers. In the spreadsheet they are linked to a table with descriptions of what the numbers mean. You can select the number and description from a dropdown box. So yes there are many formulas. – blarg Nov 20 '14 at 15:10

2 Answers2

7
  1. The first thing to do is to move to version control without modifying anything. Just get the files in there. If you discover any breakage later you can still retry.
  2. Enforce a "lock" on the sources until you reach step 4.
  3. Convert everything to a non-binary format. In the case of Excel you should be able to convert to OOXML, or you could go for a simpler format like ODS.
  4. Verify that it all still works.

You can do this on a per-file basis if you want the files to be available as much as possible while doing the conversion. Or you could automate the conversion and (unless you have truly insane amounts) get it done in seconds.

At this point you're one step away from separating code and data properly (as @FrustratedWithFormsDesigner suggested), at which point you can leave the desert of horrible embedded code for the luxury of code reuse, non-procedural code and automated testing.

l0b0
  • 11,014
  • 2
  • 43
  • 47
  • I would just add to this: As step #5: If possible, have the spreadsheet load the input data from a formatted text file, to try to better separate the data from the "code". The input data files can be versioned and tracked even easier than the spreadsheet, which could be useful if most of the changes are "data" changes rather than "code" changes. – FrustratedWithFormsDesigner Nov 20 '14 at 17:33
0

I'd like to post some ideas and information, which you can pick and choose from, as it's partly a matter of taste, opinion and what resources you have available.

Personally, I recommend using git in situations where you need to group files together. It's the only version control software I know of, that can safely do this. I recommend having your own git-server (gitolite is very good for this purpose).

I have the following setup:

  • gitolite + git for version control + gitweb for an easy overview.

  • A CGI-script on the same server, which is basically a "visitor counter".

The CGI-script opens a file, reads a number from it, increments the number and stores it back into the file; it also packs the date, the seconds/minutes/hours and other information into one single packet and sends to the requesting user. The requesting user is a shell-script, which is executed every time I compile (build) using Xcode (on Mac). If you're not using Xcode, you can put it in your Makefile.

The reason it's a CGI-script that bumps the version number, is that you need one central place (and only one) with atomic access, so that you always get a unique number.

It does not matter if you get version 0.1.4 and your buddy gets version 0.1.5 and your buddy submits version 0.1.5 before you submit your version; the most important thing here is that the version numbers are always unique. -And if you don't like your version number, just request another one. ;)