The Distributor Data Problem: Automating Secondary Sales Extraction Across Dozens of File Formats

If you sell through distributors, one thing breaks first: visibility. I can ship product, book primary sales, and still miss what stores or dealers actually sold unless I build a pipeline that handles file chaos from start to finish.

Here’s the short version: secondary sales automation comes down to 4 jobs done in order:

• Ingest every file as-is and tie it to the right distributor, date range, and source
• Detect the actual format before parsing, because file names lie
• Map every layout into one shared schema so Qty Sold, Units, and Sale Qty mean the same thing
• Validate hard and route failures by cause so parser issues, mapping issues, and business-rule issues don’t get mixed together

In plain English: I need a system that can take Excel, CSV, PDF, EDI, fixed-width files, and even email text, turn them into invoice-line data, standardize dates, product codes, and units, then block bad records before they hit reports or ERP. If I skip any step, small file changes can distort demand, stock views, and trade claims.

A few points matter most:

• Raw files should stay unchanged so I can replay failed jobs
• Correlation IDs, timestamps, and file hashes should follow each file end to end
• Format detection should use content checks, not just .csv or .xls
• Distributor mappings should live in versioned config, not hardcoded logic
• Validation should include structure, type, master-data, and reconciliation checks
• Drift detection should flag layout changes early

Below is a quick side-by-side view of the pipeline.

Bottom line: the job is not just data extraction. It’s building a traceable, modular flow that keeps working when distributor templates change from one month to the next.

Stage 1: Build an ingestion layer that captures every file with full traceability

The first control is intake: capture the file before anything else touches it. Stage 1 stores every incoming file unchanged and links it to a traceable source record. Without that base, the rest of the pipeline gets tough to audit.

Register each source and attach business metadata at intake

Every file that enters the pipeline should link right away to a known distributor. In practice, that means keeping a source registry that maps each intake channel to a verified distributor ID. Whether the file comes in through a dedicated email inbox, an SFTP drop, a portal upload, or an API endpoint, the system should resolve the sender to a canonical identity before anything else.

At intake, capture:

• Distributor ID
• Reporting period
• Received timestamp
• Original filename
• File format
• Intake channel
• Territory or facility context

Assign a unique correlation ID at this stage and carry it through parsing, normalization, validation, and ERP posting. That way, every downstream record can be traced back to the original intake event. ^[4]

Store raw inputs unchanged and log every ingestion event

Once metadata is attached, write the original file to a raw landing zone – object storage where nothing is modified, reformatted, or enriched. The file should remain exactly as the distributor sent it: pipe-delimited text, EDI, or a scanned PDF. Keeping raw and processed storage separate makes reprocessing possible without asking the distributor to resend anything.

Two controls should be in place from day one. First, use hash-based duplicate detection to flag repeat files before they skew reporting. Second, idempotency controls in the ingestion service should make sure that if the ingestion event fires more than once due to a retry, only one record gets written. ^[4]

Log each ingestion event with the timestamp, file hash, distributor ID, and correlation ID. That creates a clear chain of custody from arrival through downstream processing, and it lets any parser failure be replayed against the original file without data loss.

With the original file preserved, the next step is to detect its actual format and send it to the right parser.

sbb-itb-51b9a02

Stage 2: Detect the format first, then route to the right parser

After intake, the first job is to classify the file by what it is, not by what it’s called. A file ending in .csv can still be an Excel workbook. If you send that file to the wrong parser, things can fail quietly, which is often worse than a hard error. Once you know the format, route it to the parser built for that structure.

Use file-type and layout detection instead of trusting file extensions

Check the file’s magic bytes and inspect the content before routing. Clean up the input first: normalize encoding, remove BOMs, and standardize line endings before parsing starts. If a distributor’s old ERP system exports Windows-1252 or Latin-1 instead of UTF-8, detect that early and convert it to UTF-8 before the parser touches the file.

Excel files need another layer of inspection. Don’t assume the first sheet holds the data, or that headers sit in row one. In plenty of cases, the actual dataset starts on a later sheet, and the headers appear below title rows or notes. The routing logic should find the sheet with the data and identify the real header row before parsing begins.

Parse Excel, CSV, ERP exports, PDFs, and email content with separate modules

Once the format is confirmed, hand the file off to a dedicated parser module. One parser that tries to do everything sounds neat at first. In practice, it becomes brittle fast.

Add parser fallbacks for invalid or incomplete files

Even when format detection is correct, distributor files often show up with messy structure. Merged header cells, blank rows at the top, hidden sheets with stale data, and split columns aren’t rare cases. They’re normal.

PDFs bring a different kind of headache. Many distributor-made PDFs are image scans with no text layer underneath. A standard text-extraction pass gives you nothing back. The parser needs to detect that case and send the file to an OCR-enabled engine that can rebuild the table from the page layout.

Each parser should produce a standardized intermediate table and flag low-confidence fields before the file moves into normalization. Parsing gives you structure, not business meaning. The canonical secondary-sales schema comes after that.

Stage 3: Normalize every distributor layout into one canonical secondary-sales schema

Parsing gives you structure. It does not give you meaning.

Before this data can power reporting, you still need to map each distributor’s raw columns into one shared reporting schema. That’s what normalization does. It makes sure reports compare the same field across distributors instead of mixing apples and oranges.

Define the canonical fields before writing mapping rules

Set the target schema first. Then write the mapping rules.

If you build the schema while you’re mapping, field names start to drift. One team says invoice_dt, another says sale_date, and before long your reports stop lining up.

Use an invoice-line schema if you want outlet analysis, frequency analysis, and stock-cover analysis. At that level, pharmacy, FMCG, agrochemical, electronics, lubricant, and consumer-durables teams can compare like for like.

One rule matters a lot here: map sku_code to the manufacturer’s master code, not the distributor’s local ID.

If you skip that cross-reference, the same product gets split into multiple codes across distributors. That turns one SKU into several fake versions of itself, and your sales view gets messy fast.

Once the target schema is fixed, each distributor layout can be mapped into it in a steady, repeatable way.

Store distributor-specific mappings as versioned configuration

Distributor files are all over the place. One may send Inv_Date. Another uses Date_Sold. A third hides the date inside a merged header cell.

So don’t hardcode these rules into the pipeline. Store them as external, versioned config files loaded at runtime ^[6][8].

That way, when a layout changes, you update the config instead of rewriting code. You should also version each config set. This helps absorb layout drift without tearing apart the pipeline. If a distributor’s June report used one layout and the July report used another, the system can apply the right mapping version to each file and keep past data accurate ^[2].

Make date and UOM rules explicit in the config too.

For example:

• If a distributor sends 04-Jul-26, the config should convert it to 07/04/2026 before the record enters the canonical schema.
• If 1 Case (12 units) needs to become a quantity of 12, that case-to-unit conversion should be defined per distributor so quantities stay comparable across the network ^[4][7].

With the schema locked in, the next job is validation. Each mapped record should be checked before it reaches reporting or posting, and anything that breaks the rules should be isolated.

Stage 4: Validate aggressively, route exceptions clearly, and plan for format drift

Once records are mapped into the canonical schema, the next step is blunt but necessary: can you trust this data? That answer gets settled in validation, before anything touches reporting or an ERP posting. Normalization gives data a consistent shape. Validation decides whether that data is safe to use.

Run layered validation from structure checks to business rules

Validation works best in layers: structure, mapping, type, business rules, integrity, and reconciliation. If you automate without validation, you just move bad data faster.

Each layer matters. Skip business-rule checks, and bad data can slip into reporting and warp demand signals. That’s often where format drift shows up first – right before it starts poisoning the numbers.

Separate parser failures from mapping failures and business exceptions

When a record fails validation, route it based on why it failed, not just which file it came from. Parser failures should go to engineering. Mapping failures belong with the people who manage source configs. Business exceptions should go to data or sales review.

That split sounds simple, but it saves a lot of time. Otherwise, every issue lands in one pile, and teams waste hours sorting out what broke.

When a mapping or source file gets fixed, reprocess only the affected records. That ability is one of the clearest signs that you’re dealing with a production-grade pipeline instead of a brittle script.

It also helps to build in automated drift detection. If a distributor quietly renames a column or slips in a new one, the pipeline should flag it right away instead of sending data to the wrong place without warning ^[9].

Conclusion: A reliable secondary-sales pipeline is modular, auditable, and built to absorb format drift

The hard part is not just extraction. It’s dealing with distributor-specific variation through a pipeline that stays modular, traceable, and aware of format drift.

FAQs

How do I start automating distributor files without rebuilding everything at once?

Start with a modular pipeline, not a full overhaul. Set up a canonical schema for your internal systems so every incoming distributor file maps to one target. That gives you one shared structure to work from, and lets you reuse the same transformation logic instead of rebuilding it for each source.

Build the pipeline in stages: ingestion, classification, extraction, validation, reconciliation, and export. Keep the first step simple. Run a pilot with bulk spreadsheet imports into your ERP or database, then branch out to more file types and add validation checks before data reaches your core systems.

What should I do when a distributor suddenly changes its report layout?

Stop relying on rigid, location-based templates that fall apart the moment a column moves or a field name changes. Use a canonical schema instead. That means defining the data you need – like order date, SKU, quantity, and unit price – based on what it is, not where it sits in the file.

When a change shows up, schema drift detection can catch it, flag it, alert the right team, and point them toward the mapping updates needed to keep ingestion running without a full manual rebuild.

How can I tell whether a data issue came from parsing, mapping, or validation?

Start by checking where the failure first shows up in the pipeline.

Parsing issues happen during extraction. They usually come from unexpected layouts, structural changes, or OCR errors.

Mapping issues mean the system did extract the data, but matched it to the wrong canonical fields. A common example is treating "Item No." as "SKU" when those fields don’t mean the same thing.

Validation issues show up after parsing and mapping. These happen when the data breaks business rules, such as negative inventory, future sales dates, or unknown product codes.

Related Blog Posts

• Primary vs Secondary Sales Data: Why the “Last Mile” Is the Hardest to Capture in Distribution