Cleaning raw scan data: de-duplicating, filling gaps and merging

At eight in the evening, the exports from six handheld scanners arrive on the same laptop. Many people's first instinct is to merge them at once and look at the total. But raw records often hold double scans, bins that were missed, items booked to the wrong bin and test scans from before the start. Merge without cleaning and the total cannot be trusted. Here is a cleaning routine, from the easy steps to the harder ones.

Step zero: leave the raw files alone

First copy every export into a read-only folder, and do all later work on copies. Put the warehouse, device number, date and time in each file name. If a figure later looks odd, you can return to exactly what the device exported.

Step one: standardise the format

This is the simplest step and removes a whole class of problems:

• Merge all files into one table, adding two columns for the source file and the device number.
• Set barcodes and bins to text, strip leading and trailing spaces and invisible characters, use upper case, and replace look-alike Cyrillic letters with Latin ones.
• Use one date-and-time format, and sort by device, then by time.

Step two: clear out the obvious junk

• Test scans from before the official start and after the finish.
• Records with an empty barcode.
• Barcodes that are neither in the mapping table nor the item list. Do not delete them; list them separately for someone who knows the products to decide whether each is a new product, a mistyped code or a read error.
• Records for a bin that is not on the master bin list, which are usually typing slips.

Step three: two kinds of "duplicate"

Double scans. Same device, same bin, same barcode, with the same time stamp or less than a second apart, are most likely a trigger pulled twice. Be careful, though: when items are scanned one at a time, the same barcode appearing many times at different moments is normal, because each scan is one piece. So treat a record as a duplicate only when even the time matches, and only if your method really is one scan per piece. If you scan once and type a quantity, a repeated whole line is the duplicate.

Double-counted areas. If the same bin appears in the files of two devices, two people scanned it. Compare the totals of the two sets. If they match, keep one. If they differ, keep the more complete one or arrange a recount, and mark the other as not used.

Step four: fill the gaps

• Compare the list of scanned bins with the master bin list. A bin on the master list but not in the scans was missed and needs a recount.
• Items with book stock but no scan at all need checking: is the quantity really zero, or was it missed?
• For records without bin information, check the order of the bin scans for signs of bleed.

Fill gaps through a real recount, not an estimate, and note "recount" in the record.

Once the recounts are in, total the cleaned records by bin and SKU to get counted quantities that can be compared with the books. Then run a check in reverse: does the number of bins in the totals equal the number on the master bin list, and is every SKU in the totals on the item list? Only when both agree is the data ready for finance.

A worked example

Suppose six devices export 4,820 raw rows.

• Remove 35 test scans, leaving 4,785.
• Remove 12 double scans whose time stamps are identical, leaving 4,773.
• Find 9 bins in the files of two devices, 126 rows in all, and keep the more complete set, leaving 4,647.
• Compared with the master list, 4 bins have no records at all, so a recount is arranged for them.

The cleaned file has 4,647 usable rows, and every reduction has a number and a reason.

Create a separate "changes" sheet: what was changed, why, on what basis and who decided. It supports internal review and answers finance or head office when they ask how a figure came about.

At step four of its process, InventoryHub merges and de-duplicates the scan records from all sites, compares them with your books SKU by SKU, lists the largest gaps by product and bin and arranges a second count.