My export has subtotal rows mixed in with the data

The file has real rows, but also rows that are section headings with one cell filled, subtotal rows with a figure and no product, and a grand total at the bottom. Loading it as-is produces totals roughly double the truth.

Why this happens

• The file is a report rather than a table. It was laid out to be read by a person, so it groups rows under headings and interleaves summaries, and the visual structure carries meaning that the grid itself does not record.
• Double counting follows directly. A subtotal is the sum of the rows above it, so a file containing both the rows and their subtotals contains every amount twice, and a grand total makes it three times for the rows it covers.
• The rows are hard to find by rule because they are defined by what they are missing. A subtotal row typically has an amount and no product code, and a heading row has a label and nothing else, but so does a genuine row that arrived incomplete.
• Asking for the underlying data usually works and is usually not tried. The report was produced by a system that had the rows before it formatted them.

What to do right now

• Ask for the raw export rather than the formatted report. Most reporting tools have an export-to-data option beside the export-to-spreadsheet one, and it produces exactly what you want.
• Check the total before and after removing summary rows. If the file is internally consistent, removing them should halve the figure, and if it does not, there is something else in there too.
• Filter on the identifying column rather than on the amount. A row with no product code is a summary or a broken row, and either way it is not a record you want to load.

Stopping it on the next file

Rows can be dropped out of both validation and the export, so subtotals, headings, and the grand total are excluded as what they are rather than deleted out of the source file you were sent. The typing tends to surface them for you first: a subtotal row has no product code and no date, so on a schema where those are required it fails several cells at once and sorts to the top of the issue list, which is a quicker way to find every one of them than reading the file.

The invoice line items template is a sheet with those rules already set, if you want to see the shape of one before building your own.