Excel-to-DAT File Conversion Mistakes: Getting the Header, Detail, and Trailer Records Wrong

Every BIR DAT file — whatever return it supports — is built from three record types: a header that identifies the filer, one detail record per payee or transaction, and a trailer that closes the file with control totals, most importantly a count of how many detail records it contains. A DAT file can have perfectly correct data in every row and still fail the BIR’s Alphalist Data Entry and Validation Module if that underlying structure is wrong — the trailer’s declared count doesn’t match the actual rows, the header carries the wrong TIN or RDO code, or the wrong delimiter breaks how the module reads the file at all.

This is a cross-cutting mechanical problem, not a per-form one — it applies equally to RELIEF, SAWT, QAP, and alphalist DAT files. This guide focuses specifically on record-layout mistakes: what header, detail, and trailer records actually do, the mistakes that break them, and a worked example of a trailer count going stale after a late edit. For file naming and packaging once a file is already structurally valid, see BIR DAT File Naming Convention and Folder Structure; for why a plain CSV export isn’t a substitute for this structure in the first place, see BIR DAT File vs CSV File.

What do header, detail, and trailer records actually do? #

A BIR DAT file’s three record types each carry a different job: the header identifies who is filing and for what period, detail records carry the actual payee or transaction data one row at a time, and the trailer closes the file with control totals the BIR’s system uses to confirm nothing was lost or added in transmission. Every BIR DAT filing type — RELIEF, SAWT, QAP, or annual alphalist — is built on this same three-part shape, even though the specific fields inside each record type differ by filing type.

The trailer’s declared count is the field that causes the most trouble in practice, because it’s a number that has to be kept in sync with the file’s actual content every single time a row is added or removed — and nothing about opening a spreadsheet or a text file visually warns you when it’s fallen out of sync.

What record-structure mistakes actually cause a rejection? #

Record-structure mistakes are errors in the file’s mechanics — not in any individual payee’s data — and they can cause the BIR’s Validation Module to reject an entire file even when every detail row is individually correct. These are distinct from the data-content errors covered in this site’s per-form error guides (a wrong TIN, an incorrect ATC, a mismatched amount on one row); a record-structure error breaks how the file is read before the module ever gets to judging individual rows.

• Trailer record count not matching the actual number of detail rows — the most common failure, covered in the worked example below.
• Header record carrying the wrong TIN, RDO code, or return period — often the result of copying a previous quarter’s or a different client’s file as a starting template and not updating every header field.
• Wrong field delimiter — a file built or edited with the wrong separator character (for example, a comma where the layout expects a pipe or fixed-width fields) causes the module to misread every field after the point of divergence, not just one row.
• Extra or blank rows breaking the trailer count — a stray blank row left in the source spreadsheet before export can be picked up as an empty detail record by some export methods, or silently dropped by others, either way leaving the trailer’s declared total wrong relative to what the file actually contains.
• Mismatched record-type indicators — some DAT layouts tag each line with a code identifying it as a header, detail, or trailer row; a row tagged with the wrong indicator (for instance, a detail row still carrying the header’s tag after being duplicated from it) confuses the parser about what kind of record it’s reading.

None of these show up by simply eyeballing the data in Excel — the values can all look correct there. They only surface when the exported file is actually parsed against the fixed layout the Validation Module expects, which is exactly why validating the generated file (not just reviewing the spreadsheet) is the step that catches them.

Worked example: a QAP DAT file rejected over a stale trailer count #

A bookkeeper preparing a client’s Q2 QAP DAT file deletes one payee row late in the editing process — a duplicate entry caught just before export — without regenerating the trailer record, and the file is rejected on a row-count mismatch that has nothing to do with any payee’s actual data. The spreadsheet started with 45 payee rows for the quarter. The bookkeeper spots a duplicate near the bottom of the list, deletes that one row, and exports the DAT file directly from the now-44-row sheet using a process that had already fixed the trailer’s declared count at 45 from an earlier draft.

Submitting that file to the BIR’s Alphalist Data Entry and Validation Module produces a row-count discrepancy error, because the trailer promises 45 detail records and the module only finds 44 when it reads through the file. The fix isn’t editing the trailer line by hand to say “44” — a manually patched trailer value carries the same risk of a fresh mismatch if anything else changed since. The reliable fix is regenerating the entire DAT file from the corrected 44-row source data, so header, detail rows, and trailer are all produced together from the same final dataset, rather than patching one piece of an already-exported file.

This is also why DAT files aren’t meant to be hand-edited directly once generated — a manual edit to fix one problem (like a stale trailer count) can just as easily introduce a new one, such as a delimiter typo or a shifted field position, that wasn’t there before.

How does this connect to BIR’s own eSubmission framework? #

The requirement to submit alphalist and payee data through the BIR’s own Data Entry and Validation Module — not an ad hoc delimited file — traces back to Revenue Regulations No. 1-2014 and Revenue Memorandum Circular No. 5-2014, which set electronic submission of the alphalist of employees and payees as mandatory and describe the module used to prepare and check it. RMC No. 5-2014 clarified how that submission works in practice: three accepted channels (eFPS attachment, the BIR’s eSubmission facility, or email to the BIR’s dedicated eSubmission address), all built around a file produced or validated through the Data Entry and Validation Module rather than a generic export.

A related, slightly later circular on the BIR’s electronic filing FAQs describes the underlying expectation for this class of filings in similarly direct terms:

“…Summary Alphalists of Withholding Tax (SAWT), Monthly Alphalists of Payees (MAP) required under BIR Form Nos. 1600, 1601E, 1601F… shall be prepared using the Data Entry Module… and submitted via email to esubmission@bir.gov.ph.”

— Revenue Memorandum Circular No. 19-2015, FAQs on the electronic platform for filing tax returns

The header/detail/trailer structure this guide covers is exactly what “prepared using the Data Entry Module” is meant to guarantee — a fixed, predictable record layout the BIR’s back-end can parse the same way for every taxpayer nationwide, rather than whatever structure an individual spreadsheet export happens to produce. See How to Validate a BIR DAT File Before eSubmission for the actual validation workflow once a file is built.

Frequently asked questions #

What are header, detail, and trailer records in a BIR DAT file? #

A header record identifies the filer and the filing (TIN, RDO code, return period), detail records are the individual data rows (one per payee or transaction), and a trailer record closes the file with control totals, most importantly a declared count of how many detail records the file contains.

What’s the most common trailer record mistake? #

A mismatch between the trailer record’s declared detail-row count and the number of detail rows actually present in the file. This typically happens when a row is deleted, added, or accidentally duplicated late in editing without regenerating the file from scratch, so the trailer still reflects an earlier version of the data.

How is a record-structure error different from a data-content error? #

A data-content error is wrong information inside an otherwise well-formed row — a mistyped TIN, the wrong Alphanumeric Tax Code, an incorrect amount. A record-structure error is wrong at the file-mechanics level — a bad delimiter, a header field in the wrong position, a trailer count that doesn’t match the file, or a record-type indicator that doesn’t match what that row actually contains. The Validation Module can reject a file for either reason, but the fix is different.

Can a blank row in the middle of a spreadsheet break the DAT file’s trailer count? #

Yes. An accidental blank row inserted before exporting can be picked up as an empty detail record by some conversion methods, inflating the row count the trailer should declare, or it can be silently skipped by others, leaving the trailer’s count too high relative to what actually exported. Either way, the row count needs to be checked against the source data, not assumed correct.

Does hand-editing a DAT file after it’s generated cause record layout problems? #

It’s one of the most reliable ways to cause them. Because header, detail, and trailer records depend on consistent field positions, delimiters, and counts, opening a generated DAT file in a text editor and manually changing a value can misalign fields or leave the trailer’s control totals out of sync with the edited content, even when the edit itself looks small.

Summary #

Header, detail, and trailer records are the shared mechanical foundation behind every BIR DAT file type, and most record-layout failures trace back to one of a small set of causes: a stale trailer count, a header field left over from a copied template, a wrong delimiter, or a hand-edit made after the file was already generated. Catching these means validating the actual exported file — not just reviewing the spreadsheet — through the BIR’s Alphalist Data Entry and Validation Module before eSubmission. For the format basics behind this structure, see BIR DAT File vs CSV File; for naming and packaging once a file is structurally clean, see BIR DAT File Naming Convention and Folder Structure; and for the validation step itself, see How to Validate a BIR DAT File Before eSubmission.