One of the greatest sources of inefficiency in business computing has to do with the storage and manipulation of lists. Lists are ubiquitous – they show up in everything. As a result, there are a bewildering variety of ways you can store a list on a computer. Most people don’t know their options very well, and are failing to take advantage of all the time-saving features packed into the software on their computers.
I’d like to take a little time to review the various programs available.
Text editors
Text files are the simplest way to store a list. The benefits of storing data in text files are manifold:
- The size of the file is minimal. In the modern era, where megabytes of disk space are priced in terms of cents, this is no longer much of a concern. However, if you are paying per kilobyte to store or transfer data, then using text files can save you money.
- The data can be read by any program. Once every few months, I speak to someone who has a problem; a critical document is stored in an old version of some obsolete file format, and they need to convert it to a modern document. The conversion is usually difficult and expensive. While use of open source programs like Open Office, which have a pretty good library of converters can mitigate the problem, but why take the risk? Why go through the panic and headeaches and lost productivity when a little forethought can avoid it?
- The data is human readable. This can be very handy if, for example, you need to examine the raw data for whatever reason.
A text editor will produce text files. It requires minimal memory, won’t crash, and is very fast. Modern text editors have some pretty amazing features, they will highlight text according to rules you specify, and can perform some very dramatic sophisticated operations on the text, such as sorting or complicated search and replace operations.
The drawback with a text editor is that it cannot, generally, be used to perform sophisticated operations like filtering and performing calculations on data. So if you need to calculate totals, or want to perform queries on your data, a text editor is not a good idea. Moreover if you want to format your text, the
Word Processors
Word processors are a step up from text editors in that they permit you to format your list. Most word processors now support macros which allow you to write and execute custom programs that manipulate the list or perform actions based on the contents of the list, such as sending out emails to everyone in a list. However, all of these extra features are also found in spreadsheet programs. As a result, a word processor is generally not the best tool for maintaining a list.
Spreadsheet Programs
Programs like Microsoft Excel, Openoffice Calc, and Quattro Pro are generally the best option for working with lists. Spreadsheets are designed to store lists of data and to perform calculations on elements of the list. All modern spreadsheet programs allow you to format the entries, use formulas to calculate values, embed macros that automate the lists, to perform sophisticated sorting and filtering of the data.
The major drawback of these programs is the amount of data they can store. Microsoft Excel, famously limits a list to 256 columns and 65,000 rows. Furthermore, when a great deal of data is loaded into them, operations like searches can be very slow.
For most business tasks, though, these limitations are not an issue – and spreadsheet programs are, as a result, the workhorses with respect to storing lists.
Desktop Database Programs
The next step up in power or feature sets may be found in desktop database programs like Microsoft Access. These programs permit you to store vast quantities of data for long periods of time and to perform any imaginable type of analysis on it.
The price that one pays for this power is in ease of setup. Unlike a list stored in a spreadsheet or in a text file, format changes can be very difficult. If one does not invest thought into designing the database, the result can be comething that is less usable than a spreadsheet.
Another major drawback of desktop programs are that they don’t easily allow multiple people to share the same data. Additionally, most desktop based programs suffer from size limits. For example, Microsoft Access 2003 has a 2.4 GB limit on the amount of data it can store.
Server Based Databases
Server based databases are the most powerful, most advanced class of programs used to store lists. These programs permit you to store unlimited amounts of data, allow multiple people to access the data simultaneously, and allow you to perform any conceivable form of analysis on the data.
As with desktop based databases, a significant amount of thought must go into designing the database. In fact, unless you have previous experience with database design, you should hire a professional to design the database. Preparing a well designed and properly tuned database on a server can easily cost $10,000 (in 2009 dollars).
Choosing the Right Program
In general, you should use a spreadsheet program like Microsoft Excel. If there is a substantial amount of data to store, or if you want to have multiple people to work with the data simultaneously, or if you need sophisticated operations to be performed every time new data is entered, then you should use a database program.
If multiple people need to have access to the data, then you should use a server based program.
If you are unsure, start storing your data in a spreadsheet. The spreadsheet will allow you to make changes easily as you work ou the most useful scheme for storing and using the information. When the data becomes too large for the spreadsheet, or your demands for reports or data analysis exceeds the capabilities of the spreadsheet program, you can build or comission the construction of database to store the data. It will be a very simple task to import the contents of the spreadsheet into the database.