The basic idea is simple: garbage in, garbage out. :-)
Seriously, a stupid spreadsheet data is still data. If you give up working with it, it's perfectly fine, and the issue is closed. If you want to accept it somehow, you need to understand: there are two source of data: the spreadsheet itself, and the information on how the data is organized and interpreted, which is called meta-data
. In your case, the meta-data is the artifact which represents some knowledge
used for interpretation of something "stupid" as something which makes sense. If it does not make sense… — see above.
So, this is something I call "junk knowledge". Dealing with it is probably somewhat more challenging. You need to develop some data system representing meta-data used to define layout of data and everything needed to perform the whole workflow of converting it to the data you need. You will need to persist the meta-data in some media (say, XML file; so you can use Data Contract
which will do everything related to persistence for you, automatically, see http://msdn.microsoft.com/en-us/library/ms733127.aspx
]), you can even add some UI for meta-data authoring, so some trained personnel (I mean, not engineers, which is a conception) could express the data layout and processing rules in some graphical and easy-to-understand way. Actually, this problem can be fairly easy because the system of rules can be simple enough.