Litmus receives a regular feed of monthly sales data from around 60 suppliers of food services, which it reviews, analyses and reports upon as a service to its clients, and uploads into a data warehouse.

Extraction of this data for analysis is performed manually, which takes a considerable amount of time, and hence Litmus were keenly interested to find ways to automate this process if possible.

The challenge was that whilst a standard template exists for this data, only some suppliers follow this, whilst others provide data with a wide diversity of formats, degree of completeness and semantics.

I first undertook a study of some 40 or so example files, categorising them into types, and reviewing the formats of each.

I developed a modular design for a tool to able to convert and extract data from a wide-range of formats, based on custom-written modules to convert each overall type.

At the client's direction, I built the data extraction tool, along with data conversion modules for their standard template and the most popular formats in use, leaving open the option of developing code for the less common types for a future development.

The solution automates much of the data extraction stage for processing monthly sales data provided by food services suppliers, despite a wide variation in formats.

It supports the user by making a first pass through these files, extracting data from those it can, and diverting others to a "Needs Manual Attention" area.

Some 20-40 files are automatically processed this way in a few minutes, a task that would previously taken many hours if performed by hand.

The tool has a highly modular and configurable design, and can be extended to convert new file formats in the future.