How to Convert QuickBooks or Xero Data Into a BIR DAT File When You're Not Working From Excel

A BIR DAT file requires a fixed column order and format regardless of where the underlying data came from, and a raw export from QuickBooks Online, Xero, or SAP Business One almost never matches that shape on its own. Accounting systems report by customer, vendor, or ledger account — not in the Alphanumeric Tax Code, TIN, and column sequence the Bureau of Internal Revenue’s (BIR) Alphalist layout expects — so a mapping step always sits between “exported from the accounting system” and “ready to convert.”

This guide is a conceptual mapping walkthrough for getting data out of an accounting system and into the correct Excel template shape — it is not a step-by-step tutorial for one specific accounting package, and it doesn’t re-explain how to generate a DAT file from that template once it’s correct. For the generation mechanics themselves, see What Is a BIR DAT File? and the filing-specific guides for QAP and SAWT.

Why doesn’t an accounting system’s export already match the BIR’s format? #

A QuickBooks “Sales by Customer” report, a Xero “Aged Payables” export, or an SAP Business One vendor ledger is organized around that system’s own reporting logic — not around the fixed field order the BIR’s Alphalist Data Entry and Validation Module requires for QAP, SAWT, RELIEF SLSP, or annual alphalist filings. The BIR’s electronic alphalist framework, established under Revenue Regulations (RR) No. 1-2014 as clarified by Revenue Memorandum Circular (RMC) No. 5-2014, requires data to be prepared through a specific Data Entry Module and submitted in that module’s layout — not as a free-form export from whatever system produced the underlying transactions.

An RMC describing the BIR’s electronic filing FAQs puts the 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.”

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

That “prepared using the Data Entry Module” requirement is why the accounting system’s own report layout is never the finish line. QuickBooks, Xero, and SAP Business One were built to run a business’s books — accounts receivable aging, vendor payment history, general ledger detail — not to output the BIR’s Alphalist column order. The gap between the two has to be closed by hand (or with a converter that expects the BIR template as its input), regardless of how accurate the underlying accounting data is.

What should you export from your accounting system first? #

The right starting export depends on which BIR DAT filing you’re building, but it’s almost always a general ledger detail report, a sales journal, or a vendor payment/withholding report filtered to the filing period — not a summary dashboard view. Common Philippine SME accounting systems each expose this data slightly differently:

• QuickBooks Online — use the “Transaction List by Vendor” or “Vendor Balance Detail” report for withholding data (QAP, SAWT support), or “Sales by Customer Detail” for RELIEF SLSP sales data. Export as CSV or XLSX, not PDF.
• Xero — use the “Account Transactions” report filtered to the relevant expense or revenue accounts, or the “Aged Payables/Receivables Detail” report, exported to Excel.
• SAP Business One — run the relevant AP/AR aging or general ledger query from the Financials module, exported to Excel through the standard report-export function.

Whichever system and report you use, export at the transaction level, not a pre-aggregated summary — the BIR’s Alphalist layout needs one row per payee/transaction for the period, and a summarized report collapses the detail you’ll need to remap.

How do you map accounting-system columns into the BIR’s required template? #

Mapping means matching each column your accounting system exports — customer/vendor name, TIN if captured, amount, and category — to the specific column the BIR’s Alphalist template expects for that filing type, filling in the fields your accounting system doesn’t track at all. No mainstream accounting system’s default export lines up column-for-column with the BIR layout, so this step is manual (or handled by a converter’s own column-mapping screen) every time.

For a QAP or SAWT filing specifically, see the column layouts in How to Convert Excel to BIR DAT File for QAP and How to Convert Excel to BIR DAT File for SAWT — once your accounting export is remapped into those layouts, the rest of the conversion process is identical to a filer who started in native Excel.

What pitfalls are specific to converting from an accounting system? #

Three problems show up disproportionately in accounting-system exports rather than in Excel data entered by hand: TIN fields stored as unvalidated free text, no native ATC field at all, and vendor or customer names exported in a reversed “Last, First” format. Each one passes silently through the accounting system (it has no reason to flag them) and only surfaces once the file reaches the BIR’s Validation Module.

• Malformed TINs from free-text fields. Most accounting systems store a TIN in a generic “Tax ID,” “Business Number,” or custom field with no format validation — a bookkeeper can type 123-456-789, 123456789, or even a supplier’s old TIN before an update, and the system accepts all of them without complaint. The BIR’s Alphalist layout expects a specific digit count and no punctuation, so every TIN pulled from an accounting export needs to be checked against the payee’s actual BIR Certificate of Registration or a recent Form 2307, not trusted as-is.
• No native ATC field. QuickBooks, Xero, and SAP Business One track transactions by chart-of-accounts category — “Professional Fees,” “Rent Expense,” “Contractor Payments” — not by the BIR’s Alphanumeric Tax Code. The ATC has to be assigned manually by matching each expense category (or income type, for sales-side filings) to the correct code from the BIR’s withholding tax table; a category that mixes multiple withholding rates under one account needs to be split before mapping, not lumped under a single ATC.
• Reversed name format. Individual vendor or customer names commonly export as “Dela Cruz, Juan” or “Dela Cruz, Juan M.” for alphabetical sorting inside the accounting system. The BIR’s Alphalist layout expects separate last name, first name, and middle name fields (or, for a business name, the registered name as printed on the Certificate of Registration) — an unsplit name string in one column is a routine cause of a validation rejection that has nothing to do with the underlying data being wrong.

None of these are data-entry mistakes in the accounting system itself — the accounting records can be perfectly accurate for bookkeeping purposes and still fail BIR validation, because the accounting system was never built to enforce the BIR’s field requirements in the first place.

Worked example: mapping a QuickBooks Online vendor payments export to QAP #

A business using QuickBooks Online exports its Q2 vendor payments to build the Quarterly Alphalist of Payees (QAP) attachment to BIR Form 1601-EQ, and the raw export needs four specific fixes before it matches the BIR template. The company runs QuickBooks Online’s “Transaction List by Vendor” report for April–June, filtered to accounts payable payments subject to expanded withholding tax, and exports it to Excel.

The raw QuickBooks export (fictional data) looks like this:

To make this QAP-ready, the bookkeeper has to:

1. Normalize every TIN to the BIR’s expected digit format with no spaces or inconsistent dashes — 123456789000, 234567890000, 345678901000 — verifying each against the vendor’s actual Certificate of Registration or the Form 2307 on file, since QuickBooks never validated these on entry.
2. Split “Dela Cruz, Maria” into last name and first name fields (“Dela Cruz” / “Maria”), since QuickBooks’ single Vendor field doesn’t distinguish them.
3. Map each Account category to an ATC — “Professional Fees” to the ATC for professional/talent fees, “Rent Expense” to the ATC for rental income payments — using the BIR’s withholding tax table, since QuickBooks has no ATC field to pull from.
4. Confirm the Amount column matches gross income payment, not a net-of-tax figure, before the withholding tax amount is computed and added as its own column.

Once those four fixes are applied, the data matches the column shape the QAP module in BIR Online Tools expects, and the conversion into a DAT file proceeds the same way it would for a QAP file built from data typed directly into Excel — the remapping is the accounting-system-specific step; everything after that is standard QAP conversion.

Frequently asked questions #

Can I upload a QuickBooks or Xero export directly to a BIR DAT converter? #

No. A QuickBooks Online, Xero, or SAP Business One export is built around that system’s own report structure — customer or vendor ledgers, not the BIR’s Alphalist layout — so the columns need to be remapped into the BIR-required template (correct field order, TIN format, and an ATC column the accounting system doesn’t natively track) before the file can be converted to a valid DAT file.

Why doesn’t my accounting system have an ATC field? #

Accounting systems track transactions by chart-of-accounts category (e.g., “Professional Fees” or “Rent Expense”), not by the BIR’s Alphanumeric Tax Code (ATC). The ATC has to be mapped manually, usually by matching each expense category or income type to the correct code from the BIR’s withholding tax table, since no mainstream accounting platform stores it as a native field.

Why does a vendor name from QuickBooks or Xero fail BIR validation? #

Accounting systems commonly export individual names in “Last, First” or “Last, First Middle” format for sorting purposes, while the BIR’s Alphalist layout expects separate last name, first name, and middle name fields (or a specific registered-name format for non-individuals). An unsplit “Dela Cruz, Juan” string in a single name column is a common validation failure.

Summary #

Getting data out of QuickBooks Online, Xero, SAP Business One, or a custom ERP is only the first half of building a BIR DAT file — the accounting system’s own export format never matches the Alphalist layout on its own, because RR No. 1-2014 and RMC No. 5-2014 require a specific Data Entry Module structure, not a generic ledger report. The practical work is exporting the right transaction-level report, then mapping vendor/customer names, TINs, and amounts into that structure while manually assigning the ATC field no accounting system tracks natively. Once the remapped data matches the BIR template, the rest of the process — validation and DAT generation — is identical to a filer who started in Excel from day one; see What Is a BIR DAT File? for that shared format baseline, and the QAP and SAWT guides for the filing-specific column layouts this example builds toward.