Excel | Data Quality Management | Data Cleansing

The History of the Data Consistency Checker:

In The Beginning

The Data Checker was conceived out of a requirement to mobilise data into a Database from a Microsoft Excel Spreadsheet with the minimal amount of effort and inconvenience - see Data Checker Project image.

In most cases data is usually supplied to us in an Excel spreadsheet format and inevitably this contains numerous data inaccuracies which can then lead to many man hours sense checking the data then changing, modifying and resending the data back and forth to the Database, until it meets the correct standard (as per the Database Data Types) for loading onto the database.

Data Checker Project

The Solution

The Data Consistency Checker was created as a spreadsheet based application that would increase the efficiency of mass data loading and it would ultimately speed up the data checking process of new or existing data during the mobilisation process.
The Data Checker works by scrolling through all of the Spreadsheet data, checking the worksheet, rows and columns against the data types and values that have been pre-defined in the Data Checker.

The Data Consistency Checker is made up of 7 worksheets they are:
- User Interface Form - this is where we define the 'Check Type' to be used, then select the browse button to upload the spreadsheet for Data Validating.
- Data Issues worksheet - where the data errors are logged, a full error description is given including where the issues is located on the Spreadsheet.
- User Guide worksheet - the information on how to use the Data Consistency Checker.
- Data Checks worksheet - this is where we define the data types required for the imported spreadsheet to be checked for.
- Unique Checks worksheet - where one worksheet and column are defined as primary keys then if they must map to data in another column on the same worksheet they are defined here.
- Foreign Key Checks worksheet - the user selects a worksheet and column then maps it to another worksheet and column that contains the same data in the Workbook.
- Worksheet Names - here we define all of the worksheets that are contained within the Imported Excel Spreadsheet.

The Solution Results

When the Data Consistency Checker was run using a Clients imported spreadsheet after it had previously been sense checked the following data errors were returned:
- Inconsistent company names (spelling errors)
- Missing data (blank columns where data was specified)
- Inconsistent data values (where text was specified, but a numeric was entered)
- Data specified but not used (i.e. a data value did not have any data linked to it)
- Fax numbers were entered as mobile numbers (error checking that fax numbers do not begin with ‘07’mobile code)
- All of these errors were populated onto the 'Data Issues' spreadsheet this then allows us to go and rectify the errors from the imported spreadsheet before it got to the Database Loading Stage and thus greatly reducing our time and effort.

Outstanding Benefits

The main benefits of the Data Consistency Checker are:

It checks to see if the imported worksheet is a valid one: correct tabs, is an excel file.
Checks for missing data: no blank rows or columns unless specified.
Checks the consistency of the data: the data type is a specified for the row/column; are there any spelling mistakes, redundant data.
Checks for unique values: data from one column must be the same as specified for other rows/columns (primary/foreign keys).

This will greatly reduce administration time and prevent duplicate or corrupt data being loaded onto the Database.

The Future

The next evolution of the Data Consistency Checker is near

This versions functionality are detailed on the Home Page the new version will be a Windows Based wizard application - making it very powerful and user friendly.

For more information on the Data Consistency Checker Contact Us