title: How to test a program processing large amounts of data stored in an unpredictable format
body: <h3>What I have to do</h3>
  
  <p>I'm trying to manipulate some rather large amounts of data stored in Excel files (one of the workbooks has as much as 150 spreadsheets). The result of these manipulations may yield approximately 800.000 rows in a database table.</p>
  
  <h3>The problem</h3>
  
  <p>Data stored in the spreadsheets has unpredictable format. The company that generated these spreadsheets had no fixed/documented format for exporting these files, and sometimes erroneous data appear. For example most of the years are represented like "2009" but there are cases where a year is represented as "20". Other example, data is not really normalized in these files, so I use separators to split the values of certain cells. Sometimes these separators change.</p>
  
  <p>There are things like these that I couldn't predict and I only discovered them only after running an already evolved version of my program over a pretty large part of the available data.</p>
  
  <h3>The question</h3>
  
  <p>How can one test the correctness of a program in such a situation? Or rather, how to achieve a pretty <em>stable</em> version of the product without running it over the whole available data?</p>
  
  <p>Shall I take a defensive approach and throw exceptions whenever some kind of unexpected issue arises? Then the main loop of the program may catch and log them and continue with the available data? This would yield some processed data, but that means that on a subsequent iteration of the program I have to have checks for what's already inside the database from previous iterations (which I don't really like).</p>
  
  <p>What's your opinion? How would you tackle this problem?</p>