Excel to BIR DAT File: The Most Common Validation Errors and How to Fix Each One

A BIR DAT file converted from Excel almost always fails for one of six reasons: a delimiter that doesn’t match the layout, a file or subject-line name that doesn’t follow the BIR’s naming convention, a header/detail/trailer field or record count that’s out of sync, an invalid TIN, a date or return-period format mismatch, or a trailing blank row picked up as a phantom record. Each one is a formatting problem in the source spreadsheet or the export step — not a data problem — and each has a specific, mechanical fix.

What actually happens when a DAT file gets “rejected”? #

“Rejected” can mean three different things, and knowing which one you’re dealing with changes where to look for the fix. A converter can flag a problem before it ever generates a file; the BIR’s own Alphalist Data Entry and Validation Module can refuse to certify an already-generated file with a zero-error report; or the BIR’s eSubmission facility can bounce back an error reply after a file that passed the module gets emailed to esubmission@bir.gov.ph.

• Pre-conversion check — a tool like BIR Online Tools checks your Excel data against the target layout before it writes the DAT file at all.
• Validation Module rejection — the BIR’s own free desktop tool reads the generated .dat file and produces a line-referenced error report; see How to Validate a BIR DAT File Before eSubmission for the full workflow.
• eSubmission bounce — the BIR’s mailbox at esubmission@bir.gov.ph replies with its own validation result after receiving the file, which can still flag a problem the desktop module missed, especially after the BIR updates the module version.

This guide covers the formatting-level causes behind all three, ordered from most file-breaking (delimiter, structure) to most row-level (TIN, date, blank rows).

Quick reference: six common Excel-to-DAT errors #

Most rejections trace back to one of six mechanical causes, each with a specific fix — this table is the fast-scan version; the sections below walk through each one in more detail.

Why does the delimiter matter, and can I just pick one? #

No — the delimiter isn’t a setting you choose in Excel; it’s fixed by the BIR’s record layout for that specific filing type, and a file built with the wrong one becomes unreadable past the first mismatched field, not just wrong in one spot. Some layouts use a pipe character, others a comma, and hand-editing a generated file in a text editor is one of the easiest ways to introduce a stray delimiter character inside what should be a single field (for example, a comma left inside a company name that has “Inc., Ltd.” in it, splitting one field into two).

This is also why “just export as CSV” is not a safe substitute for a real DAT file — a generic CSV export follows whatever delimiter and formatting Excel’s regional settings happen to default to, not the BIR’s fixed layout. See BIR DAT File vs CSV File: Which Format Does Each BIR System Actually Require? for the full breakdown, and Can You Just Rename an Excel File to .DAT? for the related mistake of skipping the conversion step entirely.

Why does the file name or subject line get flagged? #

A naming or packaging error can hold up an otherwise clean, already-validated file, because the BIR’s eSubmission workflow reads the TIN, branch code, RDO code, and period from the file name or email subject line as a cross-check against the file’s actual content. The most frequent mistakes are a TIN left with hyphens or a merged branch code, a subject line missing a required element, or a period tag that doesn’t match what the DAT file’s records actually cover.

Specific filing types (RELIEF SLSP, SAWT, Form 1604-C) each have their own documented naming pattern rather than one universal format — see BIR DAT File Naming Convention and Folder Structure for the filing-type-by-filing-type table and a worked naming example.

Why does a “field count” or “record count” error show up on an otherwise correct file? #

Every BIR DAT file is built from header, detail, and trailer records, and the trailer’s declared count of detail rows has to exactly match the number of rows actually in the file — a mismatch here fails the whole file even when every individual row’s data is correct. This happens most often when a row is added, deleted, or duplicated late in editing without regenerating the file from scratch, so the trailer still reflects an earlier version of the data.

A related, narrower version of this is a field-length error: BIR-adjacent software support documentation has flagged an “Invalid Length” error specifically on a taxpayer or branch code field when a value doesn’t match the expected digit count for that field in the current module version — a good reminder that field lengths, not just field order, are checked. For the full mechanics of header/detail/trailer structure and a worked example of a trailer count going stale, see Excel-to-DAT File Conversion Mistakes: Getting the Header, Detail, and Trailer Records Wrong.

Why does the Validation Module reject a TIN that looks correct? #

A TIN error almost never means the underlying number is wrong — it means the TIN field in the exported file isn’t a clean digit string in the length the layout expects. Excel is the usual culprit: formatting a TIN column as a Number instead of Text can silently drop a leading digit or convert a long TIN to scientific notation, and pasting a TIN straight from a document often leaves the hyphens (123-456-789-000) in place instead of splitting it into a 9-digit base TIN and a separate branch code.

Fix: format the TIN column as text before typing or pasting anything into it, strip every non-digit character, and keep the branch code (commonly 000 for a head office) in its own field rather than merged into the base TIN. This exact fix pattern shows up across every filing type — see the type-specific deep dives for QAP, SAWT, and RELIEF SLSP if a TIN error is the one you’re actually stuck on.

Why do date and return-period fields fail even when the date “looks right”? #

A date or period value can display correctly in Excel and still fail validation, because Excel’s on-screen formatting and the DAT layout’s actual stored format are two different things — the layout typically expects the return period as MM/YYYY, not a full calendar date, and not in the day-first order common in Philippine date conventions. A cell showing 09/2026 can still export incorrectly if the underlying value is a full date that Excel is just displaying in a truncated custom format, rather than a genuine MM/YYYY text value.

Worked example: a return period that fails because of date formatting, not data #

Bayview Print Solutions (a fictional supplier) is preparing its Q3 2026 SAWT DAT file, and one detail row fails validation over the return period and amount fields — even though the payee’s TIN and ATC are both correct. The source spreadsheet was built by pasting the period as the last calendar day of the quarter and the amounts straight from an invoice PDF, including the peso sign.

Corrected row before regenerating the DAT file:

Once the return period column is reformatted to a plain MM/YYYY text value and the amount columns are reformatted as Number with no currency symbol or thousands separator, regenerating the DAT file and re-running it through the BIR’s Alphalist Data Entry and Validation Module clears both errors on that row.

Why does a blank row at the bottom of my spreadsheet cause a rejection? #

A stray blank row left below your last real record can be read as an empty detail record by some export methods, which throws off the trailer record’s declared row count relative to what the file actually contains — or it can be silently dropped by a different export method, leaving the trailer’s count too high instead. Either direction produces the same symptom: a row-count mismatch that has nothing to do with any payee’s actual data.

Fix: delete the blank row from the source Excel sheet itself — not from the already-exported .dat file — and regenerate the whole file, so the header, every detail row, and the trailer are all rebuilt together from the same final dataset. Trailing blank rows are one of several ways a file’s record count can drift out of sync; see Excel-to-DAT File Conversion Mistakes for the others, including a duplicate-row-deleted scenario.

Where do these formatting requirements actually come from? #

The requirement to submit alphalist and payee data as a structured file built to the BIR’s own layout — not an ad hoc export — is documented across several issuances, and it has stayed consistent even as the submission channel itself has changed over the years. Revenue Memorandum Circular (RMC) No. 19-2015, covering FAQs on the electronic platform for filing tax returns, describes the underlying expectation directly:

“…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.”

That’s the source of the whole problem this guide addresses: “prepared using the Data Entry Module” means the file’s structure — delimiter, field order, field lengths, date format — is fixed by the layout the module produces, not left to whatever a spreadsheet export happens to generate.

Two later circulars matter for staying current, not just for the initial format:

• RMC No. 25-2024 revised the file structures and standard naming convention for alphalist submissions (published as Annexes A and B), so taxpayers using their own extract program instead of the BIR’s module had to update to the revised layout.
• RMC No. 15-2025 announced Version 7.4 of the Alphalist Data Entry and Validation Module, adding new alphanumeric tax codes and updated withholding tax rates — and required taxpayers who had already submitted using the older Version 7.3 to re-validate and resubmit.

A file that validated cleanly on an older module version can fail once the BIR updates its own checks — which is why confirming the current module or converter version before validating is worth the extra minute, and why “it worked last quarter” isn’t proof a file will pass this quarter.

Pre-submission checklist #

• File generated by a converter built to the exact layout for your filing type — not a generic CSV export or a renamed spreadsheet
• File name and email subject line follow the documented convention for that specific filing type (TIN, branch code, RDO, period)
• Header record’s TIN, RDO code, and period match the taxpayer and quarter actually being filed
• Trailer’s declared detail-record count matches the file’s actual number of rows — re-check after any late edit
• Every TIN is a clean digit string (text-formatted), with branch code in its own field
• Return period formatted exactly as the layout requires (commonly MM/YYYY), not a full date
• Amount fields formatted as Number, no currency symbol, no thousands separator
• No blank rows above, inside, or below the data range before export
• File re-validated through the BIR’s Alphalist Data Entry and Validation Module with a zero-error report before emailing to esubmission@bir.gov.ph

Frequently asked questions #

Why does my BIR DAT file keep failing validation even though the data looks correct in Excel? #

Because the BIR’s Alphalist Data Entry and Validation Module checks the exported file’s structure and formatting, not how the data looks in a spreadsheet — a TIN that displays correctly in Excel can still be a text string with hidden dashes, and a date that looks fine in a cell can be stored in the wrong format for the DAT layout. The module only sees the final delimited text, so errors that are invisible in Excel show up only after conversion.

What is the single most common reason a DAT file gets rejected? #

Across RELIEF, SAWT, QAP, and alphalist filings, format-level errors in the TIN field and in the return period date are reported most often, followed by a header, detail, or trailer record count that no longer matches the file’s actual rows after a late edit.

Does the BIR use a comma or a pipe as the DAT file delimiter? #

It depends on the filing type and the specific record layout the BIR’s Data Entry and Validation Module produces for that return — the delimiter is fixed by the layout, not a choice you make in Excel. What matters practically is that the file is generated by the module or a converter built to that exact layout, rather than exported as a generic CSV and assumed to match.

Can I fix a rejected DAT file by editing it directly in a text editor? #

It’s risky. A DAT file’s fields depend on exact positions, delimiters, and record counts staying in sync with each other. Hand-editing one value can leave a trailer record’s declared count, or a fixed-width field’s length, out of sync with the rest of the file even when the one value you changed looks right. The safer fix is correcting the source Excel data and regenerating the entire file.

How do I know which specific row or field caused a rejection? #

Run the file through the BIR’s own Alphalist Data Entry and Validation Module (or your converter’s built-in validator) and read the error report it produces — it references the sheet, row, and field for each failure, rather than rejecting the file with no detail.

Is a blank row at the end of an Excel export actually a problem? #

It can be. A trailing blank row can be picked up as an empty detail record by some export methods, which throws off the trailer record’s declared row count relative to what the file actually contains — or it can be silently dropped, leaving the count too high. Either way, the fix is deleting the blank row from the source spreadsheet and regenerating the DAT file, not patching the trailer count by hand.

Summary #

Most Excel-to-DAT rejections come down to six mechanical causes — a wrong delimiter, a naming or packaging mistake, a header/trailer count out of sync, a malformed TIN, a date or period format that doesn’t match the layout, or a trailing blank row — and every one of them is a formatting fix in the source spreadsheet, not a data problem. Regenerate the whole file after any fix, and always clear the BIR’s Alphalist Data Entry and Validation Module with a zero-error report before emailing it to esubmission@bir.gov.ph. Start with What Is a BIR DAT File? if the format itself is still unclear, and see How to Correct and Resubmit a DAT File After eSubmission if an error surfaces after the BIR has already acknowledged a filing.