On rare occasions I'll have a request from management or customers to "data mine" and appropriate the data into sorted criteria based on their needs. The majority of the time the process is tedious and obnoxious and I'm always trying to find quick solutions and answers to problems.

Today was one of those days. I work in a Logistics firm, and as such our Warehouse/Transport Management system is the whole back bone of our business, however getting data out of it in a nice view is an absolute PITA.

We have a list called our Delivery Point locations which we show customers and potential customers, showing our market reach, however this list hasn't been updated since 2007 and is in need of refreshing.

Simplistic output however the process is a nightmare, and I've found a nice tidy solution to fix this data mine. The data is going to be collected from Orders despatched, focusing on "Delivery Point Code" and "Delivery Address"

The process as follows.

1. WMS System > Transport > Orders Despatched

2. Select date range to show 01/07/09-30/07/09

3. Export Data

4. 25 Colums and 171,343 lines of Data. Most of this is duplicated deliveries as we deliver to multiple locations every day.

Herein lies the problem, grabbing individual data and stripping duplicates.

1.) Open the CSV and remove any fields/columns that aren't needed leaving on the two required. Delete anything local (I.e. Collections from XYZ to Base)

2.) Notepad++ - Convert everything to uppercase

3.) Copy CSV onto Linux System

4.)

Sort filename.csv > output.csv

(Putting all the duplicates next too each other)

5.)

uniq -w 6 output.csv finished.csv

Reason behind the counting of words to 6 characters. Our delivery point location codes are 6-digits long, however the Address can be different even for the same location as this is a manual process. For example.

TESDID, Tesco Didcot

TESDID, Tesco Didcot Warehouse

TESDID, Tesco Didcot

When using "uniq" it would only remove one TESDID and leave the two uniqs alone, and this in itself is wrong, hence only counting the first 6 characters of the code and stripping/ignoring the rest.

In the end, the process took 3 minutes, compared to what seemed like forever in Excel sorting fields, filtering and manually deleting duplicates. I got bored with 169,000 lines left and search for an alternative.

End result. Task completed faster than expected and my sanity remains intact.

Just wanted to share this, albeit not very relevant, but the tedious/tasks that we are sometimes asked to provide.