Your bank dropped a fresh BAI2 file, and you need clean numbers in Excel like… yesterday. You open it and boom—headers mixed with transactions, weird decimals, mystery signs, and those 88 continuation lines that chop descriptions in half.

The real question: how do you turn BAI2 into a tidy CSV or Excel file without wrecking your reconciliation or losing half a day?

Here’s the plan. We’ll quickly cover what BAI2 actually is, why Excel chokes on it, and your options for conversion—Power Query, a script, or a dedicated converter. Then we’ll walk through BankXLSX step by step. You’ll see how to fix signs, decimals, continuation lines, and control totals. We’ll also show automation options (API/SFTP), and a short troubleshooting guide so you’re not stuck when something looks off.

Quick answer: the fastest ways to convert BAI2 to CSV or Excel

You’ve got three workable routes: Excel Power Query, a small script, or a BAI2 to CSV/Excel converter with control totals checks. Power Query is fine for a one-off, just expect to spend time handling 88 continuation lines and implied decimals. Scripts scale well but you own the code and testing.

If you care about speed and accuracy every day, a converter built for BAI2 usually wins. Upload the file, it detects the dialect, validates counts and sums, then exports clean CSV/XLSX. Simple.

Most bank docs agree: transaction details sit on 16 records and totals are confirmed by 49 (account), 98 (group), and 99 (file) trailers. When your conversion checks those numbers before export, you catch truncated files and bad lines before they hit your ERP.

Think of conversion as a control, not just a file task. No export unless totals match. That single rule cuts down on messy reconciliations and saves you from awkward audit questions.

What is a BAI2 file and why it doesn’t open cleanly in Excel

BAI2 is a plain-text bank standard. The structure goes like this: 01 (file header), 02 (group), 03 (account), 16 (transactions), 49 (account totals), 98 (group totals), 99 (file totals), with 88 used for continuation lines.

If you try to open it straight in Excel, everything lands in one grid. Headers, transactions, trailers—all jumbled. You’ll lose context, mess up columns, and ignore control totals without meaning to.

Amounts may come as integers with implied decimals, and signs often come from transaction codes or debit/credit indicators—not a minus sign. Descriptions can span several 88 lines, so you need to stitch them back together.

Pro tip: keep Posting Date and Value Date as separate fields. Posting Date helps with ledger matching. Value Date tells you when cash actually moves. Mixing them creates headaches in forecasting and month-end.

Decision guide: choose the right conversion approach

Pick your lane based on volume and risk. Power Query works if you only convert once in a while. Just know you’ll maintain logic for continuation lines, decimals, and little bank quirks. A script is great for automation, but you’re on the hook for testing and updates.

A SaaS converter focuses on accuracy and repeat work. It handles dialects, validates 49/98/99, and exports clean CSV/XLSX. That means fewer fire drills when you’re closing the books.

Also, keep Excel’s row limit in mind: 1,048,576 rows per sheet. Big days should go to CSV first, then into Excel for review if needed.

One more angle: include the cost of bad data in your decision. A single delay or posting error can easily cost more than a year of subscription fees when you add labor and audit follow-ups.

Step-by-step: converting BAI2 to CSV/XLSX with BankXLSX

Here’s how most teams run this reliably, including automation via API or SFTP:

• Upload: Drop in your .bai/.bai2/.txt files, or a whole folder for batch runs.
• Detect: BankXLSX recognizes record types, implied decimals, and continuation lines automatically.
• Preview: Check that 16 records are merged with 88 lines. Confirm signs and column mapping.
• Validate: Parsed counts and sums must match 49/98/99 trailers. If not, it flags the issue.
• Export: Pick CSV or Excel and apply a reconciliation or ERP-ready template.
• Automate: Schedule daily conversions or wire up SFTP/API to land files in your ERP or warehouse.

Multi-account files (multiple 03 records) are common. BankXLSX outputs a clean Account column so you can filter, split, or pivot quickly.

Nice touch for audit season: store the source file hash (like SHA-256) with every export. You can prove exactly which input produced which spreadsheet.

Customize your output for reconciliation and ERP upload

Most teams need ERP-ready CSV from BAI2 and a single Excel view for quick checks. Use a template that includes Date, Value Date, Account, Currency, Amount, Debit/Credit, Transaction Code, Description, Bank Reference, Opening/Closing Balance, and Source File.

Got multiple entities? Add Business Unit, Cash Pool, or Location. Map account numbers to those fields so your pivots take seconds, not hours. Lock templates so every export looks the same and matches your ERP import format.

For big volumes, export a consolidated CSV for ingestion and a human-friendly Excel workbook with filters and a control totals tab. That’s a solid balance of performance and readability.

One more idea: create a stable Transaction ID (hash of account + posting date + amount + bank reference). It makes deduping and variance checks consistent across re-exports and BI refreshes.

Data integrity: control totals and validation

Validate BAI2 49/98/99 control totals before any export leaves staging. 49 gives you account-level counts and amounts; 98 and 99 cover group and file totals. If your parsed data doesn’t match, stop and inspect the file.

Go further: compute debit and credit sums using proper sign logic and compare. Confirm record counts per account match the 49 trailers. These checks catch most issues early.

To avoid duplicates across days, deduplicate BAI2 transactions by a reference hash—account, posting date, normalized amount, and bank reference or sequence. Store it and compare on load.

Treat these validations like a contract with your bank feed. If a bank changes decimals or adds an optional field, your checks fail fast and you fix mappings before bad data slips into your ERP.

Handling multi-account, multi-currency, and continuation lines

BAI2 often packs several accounts in one file. Each account starts at 03 and ends at 49. Always include Account Number and Currency in your output so you can pivot or split sheets cleanly.

Currency may be on the 02 group line. Keep it as a column. Don’t guess later. It matters for reporting and FX calculations.

Continuation lines (88) hold long remittance info. Merge every 88 into the related 16 record. Use a clear separator like “ | ” so AR and auditors can read it easily.

Keep opening and closing balances too. If something’s off, you can spot it by account right away in Excel.

And keep both Posting Date and Value Date. Posting Date for ledger work. Value Date for forecasting cash and interest.

Correct signs, decimals, and transaction code mapping

Most mistakes come from signs and decimals. Many banks send amounts as integers with implied decimals. If the scale is 2, then 1234 means 12.34. Get that wrong and your totals are off by 10x or 100x.

Don’t rely on a minus sign for direction. Map BAI2 transaction codes to debit/credit (or use the debit/credit indicator in the 16 record) and then apply the sign.

Keep the original transaction code and a human-readable description. It helps when someone asks why a line posted as a debit.

One more safety net: add a “Sanity Checks” tab in Excel that totals debits and credits per account and compares to opening/closing balances. You’ll catch scaling issues in seconds.

If you want a reversible trail, store the raw amount, the scale, and the final currency amount. That makes reinterpreting a bank’s decimal rule painless later.

DIY option: outline for Excel Power Query

Staying in Excel? Power Query can handle this, but be precise. Import the text file, split by line, then split columns by comma. Keep rows where column1 = 16 for transactions. Then, merge 88 continuation lines into the prior 16 using an index or a small custom function.

Apply sign logic using the transaction code or indicator, scale amounts for implied decimals, and parse dates. Add a “Checks” sheet that compares your totals to the 49 trailers so you don’t miss mismatches.

Excel tops out at 1,048,576 rows per sheet. If you’re pushing that limit, export to CSV instead of loading the whole thing into a worksheet.

To make this repeatable, wrap steps in a parameterized query and create a tiny “dialect switch” table (bank name -> implied decimal, field hints). You can adjust for bank quirks without editing M code every time.

Automation: scheduling, SFTP/API, and batch processing

If BAI2 lands daily, automate everything. Set up a folder or SFTP drop, process new files, validate totals, and place outputs where your ERP expects them. Send a short daily summary: how many accounts, totals matched, any exceptions.

Common pattern: export CSV for ingestion and generate a lighter Excel “manager view” with per-account tabs and a control dashboard for humans.

Prevent duplicates by storing a processed-file hash and a per-transaction hash. If a bank resends a day, you can reprocess safely or flag differences.

One build choice that pays off: use streaming parsers. Big BAI2 files load faster and use less memory when you process line by line. Even if you don’t code it yourself, pick a converter that does.

Troubleshooting common BAI2 conversion issues

Numbers look 10x or 100x too large? Your decimal scale is wrong. Fix the scale and re-run. Signs flipped? Revisit the transaction code map or the debit/credit indicator logic. Missing memos usually means continuation (88) lines weren’t merged.

Always check control totals: 49/98/99. If they don’t match, look for a truncated file (missing 99), stray blank lines, or encoding problems. For oversized days, move to CSV before loading into Excel.

DIY tip: include Account from the 03 record, Source File name, and both Posting and Value Dates. Those fields make research much faster when amounts don’t line up.

Keep a “Known Dialects” table with bank quirks—decimal scale, optional fields, proprietary codes. Update it once, and every future run benefits.

Security and compliance checklist

Treat bank data with care. Use TLS in transit and strong encryption at rest. Enforce role-based access, SSO, and MFA. Keep audit logs: who uploaded, when it was converted, which template, control totals status, and where it went. Logs should be immutable and exportable.

Choose data region, set retention, and enforce deletions on schedule. Prefer SFTP with keys or scoped API tokens over email. Limit who can see raw files vs. exports; use masked previews for non-finance users.

Store both source and output file hashes with each run. That gives you a clean chain from bank source to ERP import, which makes audits and rollbacks straightforward.

Also look for a solid compliance posture—documented change control, incident response, and third-party reviews like SOC 2.

Frequently asked questions

Is BAI2 the same as BAI? Most folks say “BAI” and mean BAI2. Still, confirm the layout with your bank.

How does BAI2 compare to other standards? MT940 and CAMT.053 are different formats. Use a converter that normalizes outputs so your ERP mapping doesn’t change per format.

File size limits? Excel caps at 1,048,576 rows per sheet. Use CSV for large days, then build a smaller Excel review file if needed.

Can I combine accounts? Yes. Keep Account and Currency per row and you can pivot or split into tabs easily.

How do I prevent duplicates? Build a reference hash (account + posting date + normalized amount + bank reference) and check it on load.

Can I keep both ledger and available balances? Yes. Include opening, closing, and available balances to support reconciliation and cash forecasting.

Conversion checklist and next steps

• Match 49/98/99 control totals. No export if they don’t match.
• Merge all 88 continuation lines into the related 16 record.
• Apply implied decimals and correct debit/credit signs.
• Keep Account, Currency, Posting Date, Value Date, Transaction Code, Description, Bank Reference, and balances.
• Create a stable Transaction ID (reference hash) and store Source File and hash.
• Lock a reusable template for ERP-ready CSV and version it.
• Schedule daily runs; deliver via SFTP/API to ERP or data warehouse.
• Archive originals and exports per your retention policy.

Try a quick pilot with last month’s files. Compare time spent and exception rates to your current process. If it checks out, turn on scheduling and tell your reconciliation team where to grab the new outputs.

Quick Takeaways

• Don’t open BAI2 directly in Excel. Convert it: merge 88 lines, map transaction codes to debit/credit, scale amounts, and verify 49/98/99 totals. Keep Posting Date and Value Date.
• Choose based on volume. Power Query for ad hoc work, scripts if you want to build and maintain, or BankXLSX for accurate, repeatable exports with ERP-ready templates.
• Make audits easy. Gate exports on matching totals, include Account and Currency, add a stable Transaction ID and source file hash, and keep opening/closing balances. Use CSV for large files.
• Automate the flow. Nightly conversions, batch multi-account files, and delivery via API/SFTP. Provide an Excel review workbook for people and a consolidated CSV for systems.

Conclusion

Converting BAI2 isn’t just “open and save as.” Parse the record types, merge 88 continuation lines, map codes to debit/credit, apply implied decimals, and check 49/98/99 control totals. Keep Account, Currency, both dates, and balances so reconciliations pass audit without drama.

For one-offs, Power Query gets it done. For ongoing work, use BankXLSX for fast, accurate CSV/XLSX, templates that match your ERP, scheduling, API/SFTP, and solid logs. Want to see it in action? Upload a sample BAI2 to BankXLSX, preview a clean Excel export, or book a quick demo to fit your setup.