How to Import and Validate Music Sales Reports Without Spreadsheets

A practical guide to ingesting distributor CSVs, XLSX statements, and DDEX DSR files: metadata mapping, template libraries, validation rules, error handling, unmatched sales, and why the audit trail matters more than the fix. With a readiness checklist.

It is the fourth working day of the month. Your inbox holds eleven sales reports: six CSVs from distributors and DSPs, three XLSX statements from physical and sync partners, one DDEX file from an aggregator, and one PDF somebody will have to retype. Two of the CSVs have changed column order since last month without notice. One statement is in Japanese yen, stated only in the filename. Before a single royalty can be calculated, all of it has to become one consistent set of sales lines.

For most labels that job happens in a spreadsheet. Someone opens each file, deletes the header rows, renames columns, converts currencies with a rate they looked up that morning, pastes everything into a master tab, and saves it as "Q2 sales FINAL v3.xlsx". It works until it does not, and then nobody can say which of the eleven files caused the problem.

This guide describes how ingestion works inside a royalty system instead, from the files you receive to a validated, matched, auditable set of sales lines ready for calculation.

What you are actually receiving

Distributor and DSP reports arrive as CSV or XLSX, and no two senders agree on layout. One distributor sends a row per ISRC per territory per month with net revenue in your payout currency. Another sends a row per transaction type with gross revenue in local currency and a separate column for its fee. Some put the reporting period in a column, some in the filename, and some only in the covering email. Column names for the same concept vary: "ISRC", "isrc_code", "Recording ID", "Track Identifier". Quantities may be called units, streams, plays, or in one memorable case "Qty (Net of Returns)", which was not net of returns.

DDEX files are the exception that proves the rule. DDEX is the industry body that publishes standard message formats for exchanging data between labels, distributors, and DSPs. Its Digital Sales Reporting standard, usually called DSR, defines a flat-file format for sales and usage reporting with a fixed set of record types (header, summary, release and resource records, sales and usage lines, footer) and defined fields within each. A system that has parsed one DSR file can parse another from a different sender with far less setup than a bespoke CSV needs.

Two cautions apply. DSR has several profiles and versions, and senders choose which they implement, so a file from one DSP is not directly comparable to a file from another. Senders also interpret optional fields differently: one populates the commercial model type on every line, another leaves it blank. The standard reduces the mapping work without removing it, and a DDEX feed still needs its own template and validation.

Metadata mapping: columns to fields

Importing a file means telling the system, once, how the sender's columns correspond to the fields your royalty engine needs: at minimum an identifier for the recording or product, a territory, a sale type, a quantity, a revenue amount, a currency, and a reporting period. Most sources also carry a retailer name, a release title, an artist name, and sometimes a sub-label code.

Identifiers are the most important mapping decision. ISRC for recordings and UPC or EAN for products are the fields your matching logic will use first, so map them even if the file also carries the sender's internal IDs. Where a source only sends its own catalog number, map it into an alias field so the system learns it against the correct asset once and matches automatically afterwards.

Currencies need a rule, not just a column. If the file states currency per line, map the column. If the whole file is in one currency, set it on the template. If the file carries both a local and a converted amount, decide which is authoritative for your accounting and map that one.

Sale types rarely match your contract language. A distributor's "Premium Stream", "Free Stream", and "Download - Track" need to map to the categories your contracts pay on, which might be as simple as streaming, download, and physical, or as granular as subscription streaming by tier. The mapping should be a lookup table you maintain, so a new value from the sender surfaces as an unmapped code rather than landing silently in a default bucket.

Territories should be normalised to ISO 3166 two-letter codes at import. Senders variously use full names, three-letter codes, and their own groupings ("Europe", "ROW"), and a grouping needs an explicit decision, because a UK-specific contract rate cannot be applied to a line whose territory is "Europe".

Template libraries

Every mapping you build should be saved as a template tied to that source, one per distributor, DSP, sync agent, and physical retailer. Next month's file from the same sender then imports with no work beyond selecting the template, or none at all if the system recognises the source.

Senders change layouts, and they rarely announce it. When a distributor adds a column, renames "Net Revenue" to "Net Payable", or moves the period into the header, the old template should fail loudly rather than misalign quietly. The correct response is a new version of the template, effective from the period the layout changed, with the old version kept for re-importing historical files. A library that only stores the current version cannot reproduce an import from eighteen months ago, and reproducing an import is the whole point.

Validation before processing

A file that parses is not a file that is correct. Validation runs after mapping and before any line touches a royalty calculation, and the checks that catch the most real problems are not sophisticated.

Totals should match. Most reports carry a stated total revenue and total units in a summary row, a footer, or a covering statement, and the sum of the imported lines should equal it to the cent. When it does not, the usual causes are a misread decimal separator, a truncated file, or a summary row imported as a sales line.

Required fields should be present on every line. A line without an identifier, a period, or a currency cannot be processed, and a file with 200,000 lines will usually contain a few.

Currencies should be known. A code the system has no exchange rate for on the relevant date should stop the import, not convert at zero or at last month's rate.

Periods should be unique per source. Importing a distributor's March file twice, or a restated March file without replacing the original, doubles the revenue and is one of the most common ways labels overpay. The system should recognise that the period already exists and require an explicit decision.

Negative units and revenue need attention rather than automatic rejection. Returns, chargebacks, and DSP adjustments legitimately produce negatives, but a file where every line is negative usually means an inverted sign convention in the template.

Error handling: reject, quarantine, or accept with warning

Not every failed check deserves the same response, and the system should let you set the response per rule.

Reject the whole file when the problem makes it untrustworthy as a unit: totals that do not reconcile, a period that already exists, a currency that cannot be converted, or a header that no longer matches the template. Nothing from a rejected file enters the system, and the reason is recorded so the sender can be asked for a corrected version.

Quarantine individual lines when the file is sound but specific lines cannot be processed: a missing ISRC, an unknown sale-type code, a territory that will not normalise. The rest imports; the quarantined lines wait in a review queue with the original row attached, their value visible as an outstanding amount so nobody forgets them.

Accept with a warning when the anomaly deserves a human glance but should not block processing: a month whose revenue is well above the trailing average, a line with negative units, or a release that has not sold in two years suddenly reporting. Warnings are logged against the import for review before statements are finalised.

Custom calculations at import

Some adjustments belong at the import stage because they describe the source, not the contract. A distributor that reports gross revenue and deducts its fee downstream is the common example: applying the fee as a rule on that source's template means every line enters the system at net, once, recorded and repeatable. A source that reports in US dollars while your accounts run in euros can carry a fixed contractual exchange rate on its template, or an instruction to use the month-end rate from your configured rate source.

Anything you would otherwise do in a spreadsheet before pasting the data in should be a named rule on the template, so the gross figure, the rule, and the net figure survive together.

Unmatched sales

Even with clean identifiers, some lines will not match a catalog asset. The causes are familiar: a legacy ISRC from a re-delivered recording, a compilation UPC that was never entered, a typo in the sender's metadata, a track a sub-label released without registering, or a remix delivered under a new ISRC that nobody told the royalty team about.

Resolution is a review workflow. The person working the queue sees the unmatched line and the sender's title and artist fields, and either links the line to an existing asset (which teaches the alias for next time), creates the missing asset, or flags it back to the sender. A label we will call Northlight Records found, on its first structured import, that a meaningful share of one distributor's revenue had been falling into an "Other" line in the old spreadsheet for over a year. None of it had been paid to anyone.

That is why unmatched lines must never be dropped. Dropping them underpays artists, understates income, and leaves a gap between the distributor's statement and your books that the first audit will find. A visible, valued queue turns an invisible error into a task.

Why the audit trail matters more than the fix

The strongest argument against editing source files outside the royalty system is not tidiness. It is that the edited spreadsheet becomes the only record of what happened. Six months later an artist's manager queries a figure, the person who fixed the file has left, and "FINAL v3" contains a number nobody can rebuild from the distributor's original because nobody knows which cells were changed or why. An auditor tracing the payment back to source finds the chain broken at exactly the point where a human intervened.

Ingestion inside the system keeps the original file stored untouched, records the template used, and logs the validation results and matching decisions, so any figure on any statement can be walked back to a row the distributor would recognise. Qlero is built around this model — an import builder with live mapping previews, reusable templates per source, and automatic rollover of unmatched sales into the next period — so the number and its provenance never separate.

Sales-file readiness checklist

Before importing a new source, or when a familiar one starts behaving oddly, work through this list.

• Source identified and a template exists for it, or a new one has been created
• Template matches the file's current header row
• Reporting period identified and confirmed not already imported for this source
• Identifier columns (ISRC, UPC/EAN) mapped, with sender-specific IDs mapped to aliases
• Currency determined per line or per file, with exchange rates available for the period
• Sale-type codes all present in the lookup table, no unmapped values
• Territories normalised to ISO codes, regional groupings resolved by a documented rule
• Stated file totals (units and revenue) compared against imported totals
• Required fields present on every line
• Negative units and revenue reviewed, sign convention confirmed
• Source-level calculations (distributor fee, fixed rate) applied by template rule, not by hand
• Unmatched lines quarantined with original row content attached and value visible
• Warnings reviewed before the import is approved for processing
• Original file stored unmodified alongside the import record

See your own files imported

If your month still starts with eleven files and a master spreadsheet, the fastest way to judge the alternative is to watch your messiest statement go through it. Book a demo at qlero.io/book, bring last quarter's worst distributor file, and we will import, validate, and match it with you.