How PawnCounter reads any pawn POS export
A pawn shop's data arrives as a LeadsOnline file, a Visual FoxPro table, a California XML filing, or a CSV nobody has seen before. Here is how one reader takes all of them.
The hard part of pawn-shop analytics is not the analytics. It is that every shop hands you a different file, and most owners cannot tell you which. So the first rule we set for PawnCounter's reader was that it must never ask the shop what the file is. It works that out from the bytes.
Recognising the format
Detection runs on the first 64 KB of the file, before anything is parsed in full. A few checks settle most cases:
- A DBF header means a Visual FoxPro table, which is how WinPawn stores its data. The table's own name, or failing that its column names, says which table it is: pawn tickets, merchandise, history, layaway.
- An XML root tells CAPSS, the California reporting format, apart from the LeadsOnline ticket web service.
- A CSV whose header carries the column names only LeadsOnline uses (ticket type, ticket number, the customer name fields, item make) is a LeadsOnline export, whichever POS produced it.
- A pipe-delimited text file with Pawn Tracker's layout is Pawn Tracker.
- A firearms bound book, an acquisition-and-disposition CSV, has its own signature and its own reader.
Every one of those rules is a signature we verified against a real sample from that system, so detection is deterministic and each branch has a test. When none matches, the file goes to the generic reader.
The CSV nobody has seen before
Pawn Wizard, PawnSnap and a dozen smaller systems export CSV, and none of them agree on column names. Rather than an adapter per vendor, the generic reader matches columns by meaning: a long list of aliases, compared case-insensitively and ignoring spaces, underscores and hyphens, decides which column is the cost, which the asking price, which the date in, which the description. A file that describes items and costs is inventory; one that describes tickets is a transaction log; some are both, and then both are produced. Columns it cannot place are ignored rather than fatal, and anything it had to guess at comes back as a warning the shop can see. It does not need to have met the system before, which is why the "any other POS" guide is a real path and not a polite refusal.
One item model
Whatever the source, every record is normalised into the same shape: an item with a category, a description, a cost, an asking price, dates in and out, and the loan or sale it belongs to. Rules fill in what a format leaves implicit, and a recovery step joins tables that describe the same item differently. WinPawn is the reason that step exists: its history table, the only one carrying a realised sale price, records a category and no description, so every sold item would arrive as "Firearms" or "Tools" while the same item number in the merchandise table says "Remington 870 shotgun". Joining on the item number gives the sale its description back, and with it the ability to compare like with like. The analytics only ever see that one model, so a fix to the markdown logic applies to a WinPawn shop and a LeadsOnline shop alike.
What it cannot read, said plainly
Bravo and PawnMaster do not, as of today, give a shop an export we can work from. Rather than pretend, the Bravo and PawnMaster guides say so and explain what to ask the vendor for. A shop on either can write to us, and we will build the reader once we have a sample. We want the format more than the sale.
The result is what the product promises on its page here: keep the POS you have, send the file it already makes, and get the Profit X-Ray back.