How do I convert a CAMT.054 XML bank statement to CSV or Excel?

Dec 14, 2025

Open a CAMT.054 XML and it’s just walls of angle brackets. Perfect for machines, not so fun for recon. Most finance folks want tidy rows in CSV or Excel, not nested tags. Here’s a down‑to‑earth walkthrough to turn a CAMT.054 bank file into clean CSV/XLSX you can actually use.

We’ll hit the basics of what CAMT.054 is, which fields you actually need, and a practical column layout that plays nice with accounting systems. Then three ways to do the conversion: quick and dirty in Excel Power Query, code it with XSLT or Python, or let BankXLSX handle it on repeat. Along the way we’ll flatten Ntry/TxDtls into one row per transaction, pull remittance info (Ustrd and structured SCOR), deal with fees and FX, split multi‑account files by IBAN, avoid duplicates, and sanity‑check totals. By the end, you’ll have a setup that gets you from XML to Excel in minutes, not a lost afternoon.

Looking at a CAMT.054 thinking “this belongs in a spreadsheet”? Same. Converting CAMT.054 to CSV or XLSX helps with same‑day recon, invoice matching, and audits. In Europe, ISO 20022 has been standard for SEPA for years, and more banks now send detailed notifications. Once you convert CAMT.054 XML to CSV, you get the good stuff—EndToEndId, remittance, charges, FX—in a format you can pivot. One team cut daily recon from 3.5 hours to 35 minutes just by settling on a simple mapping and an automated export. Tiny trick that pays off: add a SignedAmount column early; life gets easier everywhere else.

What is CAMT.054? The essentials

CAMT.054 is the ISO 20022 BankToCustomerDebitCreditNotification. Think “notifications of credits/debits,” often the same day, with rich transaction detail. It sits next to camt.052 (intraday report) and camt.053 (end‑of‑day statement with balances). Many banks send CAMT.054 for SEPA credit transfers, direct debits, and wires. Compared to old MT9xx or PDFs, you get clearer references and remittance info, plus charges and FX fields.

Why convert it? Most of your work happens on rows. An ISO 20022 CAMT.054 debit/credit notification CSV export carries the fields ERPs and spreadsheets care about: EndToEndId for invoice matching, MandateId for SDD, BkTxCd for rules/categorization, and both Booking and Value dates. Real example: a subscription business processing thousands of SDDs daily matched 97% of payments automatically using EndToEndId from TxDtls. One note: treat CAMT.054 as “lots of events,” not “final balances.” Use camt.053 for close‑of‑day totals and 054 for the nitty‑gritty matching.

Inside a CAMT.054 file — structure you need to know

Picture the path: Document → BkToCstmrDbtCdtNtfctn → Ntfctn → Ntry → NtryDtls → TxDtls. For conversion, aim for one row per TxDtls. You’ll usually grab Ntry.Amt/@Ccy, CdtDbtInd, BookgDt/Dt, ValDt/Dt, BkTxCd, plus TxDtls details: Refs (EndToEndId, TxId, InstrId, MandateId), RmtInf (Ustrd and structured SCOR references), RltdPties (Debtor/Creditor and “Ultimate” parties), RltdAgts (BIC), plus charges and FX. Keep the account info (IBAN) and notification metadata (Notification Id, Creation time) for splitting files and audits.

Banks do things differently. Some put multiple TxDtls under one Ntry; others tuck odd references into AddtlNtryInf. Always flatten CAMT.054 Ntry/TxDtls to rows so you don’t miss invoice‑level data. Performance heads‑up: large corporates can send 20MB+ files with tens of thousands of transactions—plan for batch or streaming. One marketplace got hourly CAMT.054s; flattening TxDtls gave them near‑real‑time checks on incoming payouts. Also, store the BkTxCd triplet (Domain/Family/SubFamily) in separate columns. It improves auto‑tagging later. Yes, “flatten camt.054 ntry txdtls to rows” is exactly what you’re doing here.

Decide your target schema — recommended columns for CSV/XLSX

Design the CSV once and reuse it. A practical layout:

  • File metadata: SourceFile, NotificationId, CreationDateTime
  • Account: IBAN/Account, AccountName (if present)
  • Entry: BookingDate, ValueDate, EntryAmount, Currency, CreditDebit, BkTxCdDomain/Family/SubFamily, ProprietaryCode
  • Transaction (TxDtls): EndToEndId, InstructionId, TransactionId, PaymentInformationId, MandateId
  • Parties: Debtor, Creditor, UltimateDebtor, UltimateCreditor, CounterpartyIBAN, CounterpartyBIC
  • Remittance: Ustrd, StructuredType (e.g., SCOR), CreditorReference
  • Charges/FX: ChargesAmount/Currency, OriginalAmount/Currency, TransactionAmount/Currency, ExchangeRate
  • Derived: SignedAmount, Category, DuplicateKey

That bkTxCd (bank transaction code) mapping in CSV export pays for itself: you can auto‑classify card settlements vs payouts with a couple of rules. If you import to an ERP, mirror its header names and formats (ISO 8601 dates, dot decimals) so nobody fixes files by hand. Example: an AP team mapped SCOR references to vendor invoice numbers and cut exceptions by 60%. Also keep both EntryAmount and TransactionAmount—entries can be net of fees while TxDtls holds the original. Having both prevents confusion when charges show up.

One-off conversion with Excel Power Query (no code)

Doing it once or twice? Excel Power Query is plenty. Quick steps:

  1. Data → Get Data → From File → From XML, pick the CAMT.054 file.
  2. Drill to Document → BkToCstmrDbtCdtNtfctn → Ntfctn, expand Ntfctn → Ntry → NtryDtls → TxDtls into rows.
  3. Select and rename columns (amounts, dates, references, RmtInf). Replace nulls with blanks.
  4. Add a signed amount: if [CdtDbtInd] = "CRDT" then [Amount] else -[Amount].
  5. Merge multiple Ustrd lines into one cell; keep the SCOR reference in a separate column.
  6. Close & Load to a table. Refresh to run the same steps on new files.

Reality check: Excel does fine on small/medium files; 100k+ TxDtls starts to slow down. Expect 15–30 minutes to build the first query, then a few seconds per refresh. Handy tip: parameterize the file path so the same query works for future files. Another: build a DuplicateKey (e.g., IBAN & EndToEndId & BookingDate & Amount) inside Power Query to catch re‑sent notifications. For many folks, an excel power query camt.054 xml import is the fastest proof‑of‑concept before you lock it down.

Programmatic conversion (XSLT or Python) — for technical teams

Want full control and repeatability? Code it. Two common paths:

  • XSLT: Iterate over /Document/BkToCstmrDbtCdtNtfctn/Ntfctn/Ntry/NtryDtls/TxDtls and write CSV rows. Fast and declarative, but CSV escaping and bank quirks can be fussy. Some teams keep one stylesheet per bank variant.
  • Python: Use lxml or ElementTree with a namespace map. For big files, stream with iterparse. Write safe getters that return empty strings when nodes are missing. Output with csv or pandas. Lots of folks search “python parse camt.054 to csv (lxml/ElementTree)” to start.

Skeleton in Python:

ns = {"ns": "urn:iso:std:iso:20022:tech:xsd:camt.054.001.04"}  # adjust version
for event, elem in etree.iterparse("file.xml", events=("end",), tag="{*}Ntry"):
    for tx in elem.findall(".//ns:TxDtls", ns):
        row = {
            "BookingDate": elem.findtext(".//ns:BookgDt/ns:Dt", "", ns),
            "ValueDate": elem.findtext(".//ns:ValDt/ns:Dt", "", ns),
            "Amount": elem.findtext(".//ns:Amt", ""),
            "Currency": elem.find(".//ns:Amt", ns).get("Ccy") if elem.find(".//ns:Amt", ns) is not None else "",
            "CdtDbtInd": elem.findtext(".//ns:CdtDbtInd", "", ns),
            "EndToEndId": tx.findtext(".//ns:Refs/ns:EndToEndId", "", ns),
            "Ustrd": " ".join([u.text for u in tx.findall(".//ns:RmtInf/ns:Ustrd", ns) if u.text]),
            # ...more fields...
        }
        # write row to CSV
    elem.clear()

Case study: one treasury team unified five banks’ CAMT.054 variants with ~800 lines of Python and a pile of sample files for unit tests. Adding a new bank is usually a small XPath tweak. Nice extra: write a tiny JSON sidecar with source XPaths per row so audits and bug hunts don’t turn into archaeology. If you prefer a pure XML approach, an xslt transform camt.054 xml to csv also works well.

Fast, repeatable conversion with BankXLSX (recommended)

When volume goes up and accuracy matters, BankXLSX gives you a predictable pipeline. Upload CAMT.054 files, it detects the structure, shows a preview, and exports CSV/XLSX that matches your ERP. Start with the CAMT.054 template or tweak columns: IBAN, EndToEndId, the BkTxCd breakdown, remittance (Ustrd + SCOR), charges, FX—whatever you need. Run one file or hundreds. Schedule jobs. Hit an API. Send outputs to SFTP or your storage. Built‑in checks flag missing dates, mixed currencies, blank EndToEndId, and similar gotchas. Access controls, logs, and retention policies are included.

Example: an EU retailer doing sepa camt.054 file conversion to csv across four banks moved from manual Excel steps to a 7:05 AM daily job. Files arrive with consistent headers, and a webhook kicks off recon. They saved ~12 staff‑hours per week; reference errors fell under 0.5%. Quiet bonus: BankXLSX versions your mappings, so if your ERP import changes, you can re‑export old XML into the new format without rewriting anything.

Handling tricky cases in CAMT.054

  • Multiple TxDtls per Ntry: Always flatten to TxDtls. Otherwise you’ll miss invoice‑level references.
  • Remittance: Extract remittance information (Ustrd/SCOR) from camt.054. Keep Ustrd intact; parse SCOR into a clean “CreditorReference.”
  • Net vs gross: Fees may live in Chrgs or AmtDtls. Keep EntryAmount, TransactionAmount, and ChargesAmount.
  • FX: Store OriginalAmount/Currency, ExchangeRate, and TransactionAmount.
  • Multiple accounts: Files can hold several Ntfctn blocks. Split by IBAN and tag rows with the account.

Example: a B2B utility saw Ustrd strings with multiple invoice numbers. Their rule: if SCOR exists, use it; otherwise pull the first “INV-####” pattern, dump the rest into a Notes column for review. Also keep proprietary bank codes next to BkTxCd—those hints often explain odd fee postings. If EndToEndId is flaky, fall back to a composite key of amount, date, and counterparty to catch loose matches.

Data quality, validation, and de-duplication

  • Totals: Sum SignedAmount by Credit/Debit and compare with any subtotals the bank provides.
  • IDs: Check EndToEndId for SCTs and capture MandateId for SDDs. EndToEndId and MandateId mapping in camt.054 csv improves AR matching.
  • Dates: BookingDate and ValueDate should be valid ISO dates. Keep CreationDateTime with timezone for audits.
  • Duplicates: Banks resend files sometimes. Use a DuplicateKey like AccountIBAN + BookingDate + EndToEndId + Amount + Currency. If EndToEndId is empty, fall back to TxId/InstrId.

Example: a fintech killed 99% of duplicates after moving from file‑name checks to row‑level keys. Keep NotificationId and a file hash too—when someone asks “which file created this row?” you won’t be guessing. Another nice touch: add a DataQualityStatus column (OK, MissingID, MixedCurrency, DateAnomaly) and track it. Your exception queue becomes measurable, and fixes get faster.

Performance and automation at scale

Big files need a bit of planning. For 100k+ TxDtls or multi‑tens‑of‑MB XML, stream parse with iterparse to keep memory steady. Batch outputs by day/account so they’re easy to load. Make jobs idempotent so retries don’t create duplicates. And include charges and fx exchange rate fields in camt.054 outputs so you don’t have to enrich by hand later.

  • Scheduled pickups (SFTP/inbox) with predictable ready times (e.g., “available by 7:15 AM”).
  • Notifications (webhooks/email) on success/failure with row counts and basic validation stats.
  • Versioned mappings so imports stay consistent across releases.

Example: a payments platform ran hourly conversions. End‑to‑end per file averaged ~90 seconds with <10‑second variance using parallel workers and streaming XML. Pro tip: store XML gzipped. It compresses nicely, cuts storage and transfer time, and unzips instantly when needed.

Security, governance, and compliance for bank data

  • Access: Least‑privilege roles, MFA, and separation of duties for upload, mapping, export.
  • Encryption: TLS 1.2+ in transit; AES‑256 at rest. Rotate keys on a schedule.
  • Retention: Set how long to keep source XML and outputs (e.g., 90–180 days). Auto‑purge old data.
  • Audit trail: Immutable logs of who ran what, when, and which template version.
  • Data residency: Keep processing/storage in approved regions.

Example: a public company set BankXLSX to hide PII in previews while keeping full data in exports—nice balance of privacy and usability. For “bank statement xml to excel (camt format)” pipelines, include a hash of the source XML in your metadata so lineage is obvious during audits. Also, write down your exception SOP—how bad rows get quarantined and fixed—so you can show a closed loop.

Ensuring ERP/accounting system compatibility

ERPs can be picky about headers, order, and formatting. Copy their import spec exactly: header names, ISO dates, decimal separators, encoding (UTF‑8 without BOM is usually safe). Many systems prefer one account per file; split multi‑account camt.054 by IBAN for ERP import if needed. Always test with a sandbox file first.

Example: a NetSuite team needed “Amount (Signed), Memo, Transaction Date, Entity, External ID.” They mapped SignedAmount, merged Ustrd/SCOR into Memo, used BookingDate for Transaction Date, and set External ID to EndToEndId. After fixing delimiters and turning off thousands separators, imports hit 99.8% success. Lock the column order and don’t change it without versioning—some ERPs map by position, not header. If you can use XLSX, keep actual date types (not strings) to avoid parsing headaches. Keep a changelog of template tweaks.

FAQs about converting CAMT.054 to CSV/Excel

  • Can Excel open CAMT.054 directly? Yes, with Power Query: Data → Get Data → From XML. You’ll still need to expand nodes and make one row per TxDtls.
  • What’s the difference vs CAMT.053? camt.053 vs camt.054 for reconciliation: 053 is your end‑of‑day statement with balances; 054 is granular debit/credit notifications, often intraday, ideal for matching and cash application.
  • How do I get invoice numbers? From RmtInf. Keep Ustrd as‑is and pull structured SCOR creditor references when available.
  • How do I handle fees and FX? Keep EntryAmount vs TransactionAmount, ChargesAmount, ExchangeRate, and the original currency/amount.
  • Can I process multiple files? Yep—batch them and apply the same mapping. Automated runs can handle hundreds at once.
  • How do I prevent duplicates? Use a stable key like IBAN + BookingDate + EndToEndId + Amount + Currency, and log processed keys.
  • What if fields are missing? Parse defensively, default blanks, and flag rows for review. Optional nodes are common.

Quick conversion checklist

  • One row per TxDtls; not at Ntry level if you care about invoice detail.
  • Columns to include: Account IBAN, Booking/Value dates, Credit/Debit, EntryAmount, Currency, BkTxCd triplet, EndToEndId, MandateId, TxId/InstrId, counterparty details, remittance (Ustrd + SCOR), charges, FX, SignedAmount, DuplicateKey.
  • Formats: ISO 8601 dates, UTF‑8, dot decimals, consistent delimiters.
  • Validation: totals by Credit/Debit, required IDs present, dates sane, currencies consistent.
  • Dedupe: stable keys with fallbacks when EndToEndId is missing.
  • ERP fit: exact headers and order; test in sandbox.
  • Governance: access controls, mapping versioning, audit logs, retention policy.
  • Automation: schedule, monitor, alert; keep runs idempotent.

Teams that follow this usually hit >99% auto‑import success after a couple iterations. During early testing, add a “SourceXPath” debug field; remove it later once things are stable. Quick reminder for SEO nerds: convert camt.054 xml to csv.

Quick Takeaways

  • Flatten to TxDtls and build a steady CSV/XLSX schema: Booking/Value dates, Credit/Debit, EndToEndId/MandateId, BkTxCd, remittance (Ustrd + SCOR), fees/FX, a SignedAmount, and a solid DuplicateKey.
  • Pick the approach that fits volume: Power Query for one‑offs, XSLT/Python for full control, BankXLSX for fast, repeatable, audit‑friendly conversions with templates, checks, scheduling, and batch runs.
  • Watch out for gotchas: multiple TxDtls per entry, net vs gross, FX, multi‑account files, and messy remittance text. Normalize dates/currencies, run balance checks, and dedupe with stable keys.
  • For ERP imports at scale: lock headers/order, use regional formats, split by IBAN if required, and stream or batch big files. Teams on BankXLSX often cut recon time by 70–90% and reduce manual errors.

Conclusion — choosing the right path and getting started

Converting CAMT.054 XML to CSV/XLSX gets easy once you flatten to TxDtls, map the must‑have fields (dates, Credit/Debit, EndToEndId, BkTxCd, remittance, fees/FX), validate totals, and dedupe with stable keys. Use Excel Power Query for occasional files, Python/XSLT for custom logic, and BankXLSX when you need speed, scale, and audit‑ready outputs across banks and teams. Want to speed up reconciliation and ERP imports? Upload a sample CAMT.054 to BankXLSX, pick the template, preview rows, export to CSV or Excel, and schedule daily runs or hook up the API. Turn angle brackets into rows you can work with—fast.