MT942 files are super handy for intraday insight… until you open one and it’s a wall of tags and commas. If you need to convert MT942 to CSV or Excel (XLSX) for analysis, reconciliation, or quick imports, you’re in the right place.

We’ll cover what MT942 actually is, how it differs from MT940, and which fields you really want in tidy columns. Then I’ll show three ways to get it done: a fast SaaS route with BankXLSX, a no-code Excel Power Query option, and a simple Python approach if you like scripts.

We’ll also talk through parsing :61 and :86, fixing decimal commas and D/C signs, mapping value date vs entry date, cleaning duplicates, and the basics of validation and security. By the end, you’ll have a reliable MT942 to Excel/CSV process—whether you’re doing a one-off or setting up something you can run every day.

Quick answer: three reliable ways to convert MT942 to Excel/CSV

If you’re in finance ops and just need this done, here’s the quick rundown on how to convert MT942 to CSV or Excel without digging through docs all day:

• BankXLSX (fastest): Drop in your MT942, preview the parsed lines, pick a saved mapping, and export to XLSX/CSV. It handles decimal commas, D/C signs, and those multi-line :86: narratives that go on forever. If you’re processing multiple intraday files, it’s minutes start to finish.
• Excel Power Query (no-code): Import the text, split by tags, pair each :61: with its :86:, fix dates and amounts, and load to a table. Perfect if you live in Excel and your bank format doesn’t change much.
• Python (scripted): A short parser that walks the tags and writes CSV. Maximum control, easy to plug into a daily job. Great for teams that like repeatable scripts.

A real example: a mid-market treasury team converting 10–20 intraday reports per day cut prep from about two hours to under ten minutes by standardizing a mapping and turning on sequence-level dedup.

The surprise benefit wasn’t just speed—consistent columns, predictable signs, and a clear audit trail make reviews and audits a lot less painful. If you’ve got a deadline today, start with BankXLSX. If you’re tinkering or building a prototype, try Power Query first.

What is an MT942 and how it differs from MT940 and camt.052

MT942 is SWIFT’s intraday “Interim Transaction Report.” Think snapshots during the day, sometimes multiple per business day. MT940 is the end-of-day statement—you usually get an opening and closing balance and a definitive list for that day.

In ISO 20022 land, camt.052 is the closest match to MT942. Why this matters for conversion:

• Partial data: MT942 can overlap across sequences (:28C:), so you’ll want deduplication.
• Balances are optional: Some banks include :60F: / :62F:, some don’t. You can’t always do balance checks the same way you would with MT940.
• Free-form text: :86: narratives vary a lot by bank, so your parser needs to be flexible.

For example, :61: amounts use decimal commas (1250,75). The D/C marker tells you the sign. If you skip normalization and pull this into Excel as-is, your formulas won’t behave. Treat MT942 like a feed for visibility and mid-day workflows; use MT940 for final day-end reconciliation and tie-outs. That alone saves a lot of head-scratching with auditors.

Anatomy of an MT942: tags you’ll convert into spreadsheet columns

Most of the action is in block 4. These tags map nicely to the columns you’ll want:

• :20: Message reference (unique per message)
• :25: Account identification (IBAN or domestic)
• :28C: Statement/sequence number (intraday sequence)
• :13D: Report date/time (e.g., 2406111105+0100)
• :61: Statement line (transaction details)
• :86: Narrative for the preceding :61:
• Optional: :60F: / :62F: (opening/closing balances), and :60M: / :62M: intraday variants

A typical :61: might look like: 2406110611D1234,56NTRFNONREF//REF123. Here’s what you pull out:

• Value date (YYMMDD): 240611
• Entry date (optional MMDD): 0611
• D/C mark: D (debit) or C (credit)
• Amount: 1234,56 → 1234.56 after normalization
• Transaction code: NTRF
• Reference/supplementary: NONREF//REF123

The :86: lines often wrap across several lines. Pair and join them with their preceding :61:. Pro tip: stash the raw :61: and :86: text in hidden columns on first load. When someone asks “Where did this value come from?”, you can show the original lines without digging up the source file. Saves a ton of back-and-forth.

Decide your target columns and data model before you convert

Before exporting anything, decide what your ledger or BI tool expects. A solid default schema:

• Account, Message Ref, Sequence, Report Timestamp
• Value Date, Entry Date, Posted Date (your policy)
• Amount (signed), Currency, Debit/Credit
• Transaction Code, Bank Reference / End-to-end ID
• Narrative / Remittance, Counterparty fields (if you parse them)
• Source File, Import Batch ID

Two choices drive most downstream friction:

• Posted date policy: Post by Value Date (common for interest) or Entry Date (operational timing). Keep both columns; make your Posted Date decision obvious.
• Currency rules: Mono-currency? Set a default. Multi-currency? Require Currency per row and handle FX outside the parser.

One controller cut exceptions by ~40% after they agreed on “map value date vs entry date from MT942,” then set Posted Date as a calculated choice. Also, include Sequence (:28C:) in your export and dedup key. When a bank resends overlapping intraday segments, you’ll know exactly why a line was flagged.

Common MT942 conversion pitfalls (and how to avoid them)

• Decimal separators: Handling decimal commas in MT942 amounts is mandatory. Replace “,” with “.” before converting to numbers. If a value contains both, stop and review.
• Signs: Apply D/C sign to MT942 amounts the same way every time: D = negative, C = positive. Store a signed Amount, not just a separate D/C flag.
• Narratives: :86: is free-form and multi-line. Join with single spaces, normalize whitespace, and strip control characters.
• Duplicates: Sequences overlap. Use Account + ValueDate + SignedAmount + TCode + Reference + Narrative + Sequence to catch dupes reliably.
• Oddities: Missing :60F:/ :62F:, tag reordering, or local add-ons. Assume non-tag lines continue the prior tag unless a new “:dd[A]?:” starts.

Example: card transactions often show NMSC in :61:, with merchant details only in :86:. If you don’t pair them, you’ll lose who the counterparty was and misclassify spend. Keep both the raw and cleaned narratives around—it’s worth it when finance asks for context.

Method 1 — Convert MT942 to Excel/CSV with BankXLSX (step-by-step)

If you want the fastest, least fussy path and good audit support, do this:

• Upload: Drag-and-drop one file or a folder. BankXLSX detects encoding and common MT variants. Zip files work too.
• Parse preview: It pairs :61:/ :86:, applies D/C signs, fixes decimal commas, and shows a clean table with dates, amounts, codes, and narratives.
• Map: Use a preset or build your own column layout. Set Posted Date, timezone rules from :13D:, and currency defaults per account.
• Validate: Turn on duplicate detection across sequences (:28C:). Check numerics, valid dates, and missing descriptions.
• Export: Click to get XLSX or CSV. Save the mapping for next time. You can also schedule runs or use the API.

A finance team handling intraday feeds across Europe cut manual steps by about 70% after saving templates and wiring webhooks into their import process.

When audit season hit, they leaned on Source File and Import Batch ID columns to show lineage. That’s the kind of small detail that saves meetings.

Method 2 — Convert MT942 to Excel using Power Query (no code)

Prefer to stay in Excel? Power Query can handle MT942 if you’re careful with the steps:

• Import: Data > Get Data > From Text/CSV. Pick the right encoding (UTF-8 or ANSI).
• Tokenize: Split into lines. Detect tags by lines starting with “:”. Group :61: with all following lines until the next tag; attach :86: to that :61:.
• Parse fields: From :61: pull YYMMDD (Value Date), optional MMDD (Entry Date), D/C, Amount (replace “,” with “.”), transaction code (NTRF, NMSC, etc.), and reference.
• Normalize: Create a Signed Amount using D/C, cast to decimal, convert dates to ISO, trim whitespace in narratives.
• Enrich: Fill down :25:, :28C:, :13D:, :20:. Set currency rules and add Source File.
• Load: Send to a table and save the query so you can reuse it.

Tip for large files: filter by date or sequence early in the query to keep things snappy. I like adding a diagnostic column with the raw :61:/ :86: text—makes reconciliation faster when someone questions a line. If you end up automating later, your Excel Power Query MT942 parser logic translates nicely into ETL steps.

Method 3 — Convert MT942 to CSV using a lightweight Python script

If you’re comfy with scripting, a compact parser gives you control and repeatability:

• Read the file line by line and detect tags with a regex like ^:(\d{2}[A-Z]?):.
• On :61:, start a new record. On :86:, append text to that record’s narrative until the next tag appears.
• Extract value date, optional entry date, D/C, amount, transaction code, and reference. Replace commas with dots and apply the sign.
• Carry header fields (:25:, :28C:, :13D:, :20:) down to each row.
• Write CSV with a fixed column order, include Source File, and add a dedup hash key.

Most teams start with a simple Python MT942 to CSV example/script, then add unit tests for tricky cases: long :86: narratives, missing balances, overlapping sequences, mixed encodings. One approach that works well: make two passes—first build clean (:61:+:86:) records, then apply “business rules” like currency inference and duplicate windows. That keeps the parser stable while you tweak logic.

Deduplication, validation, and reconciliation checks you should implement

Intraday feeds can be noisy, so put guardrails in place before anything hits your GL:

• Dedup: Use Account + ValueDate + SignedAmount + TCode + Reference + Narrative + Sequence as your composite key. If your bank reissues adjusted sequences, add a 48-hour window.
• Validate: Enforce YYMMDD parsing (e.g., YY < 70 → 20YY), numeric amounts after decimal normalization, and required D/C markers.
• Balance-aware: If you see :60F:/ :62F:, verify arithmetic. If not, at least reject malformed amounts.
• Narrative hygiene: Collapse whitespace, remove control characters, and cap length so downstream imports don’t quietly cut off text.

One team cut duplicate postings by roughly 90% after adding :28C: to the dedup key and hashing the cleaned narrative instead of the raw text. Also helpful: keep both a machine-friendly hash and a human-friendly “match reason.” When someone asks why a row was flagged, you can answer in seconds.

Handling multi-currency, time zones, and large files

• Multi-currency: If the account supports multiple currencies, make Currency a required column. If it’s not on :61:, infer from balances or account info. Store Signed Amount and Currency; do FX later in your warehouse.
• Time zones: :13D: includes an offset (e.g., +0100). Normalize report timestamps to UTC for cross-region comparisons, but keep the original value too. Value Date is a date—don’t force a timezone on it.
• Scale: Big files (100k+ lines) do better with streaming. In Excel, use folder queries with filters. In scripts, process line-by-line and write as you go.

A global payments team shaved ~35% off month-end reconciliation after standardizing time zone handling and currency rules. During DST changes, they avoided odd one-hour slips by anchoring report timestamps to UTC and leaving Value Dates alone. Small detail, big sanity saver.

Security, privacy, and compliance when processing bank statements

Bank files are sensitive. Treat secure bank statement conversion as a first principle:

• Transport and storage: TLS in transit, encryption at rest, ideally with customer-scoped keys.
• Access: SSO/MFA, least-privilege roles, per-file permissions. Use service accounts and scoped API keys for automation.
• Auditability: Immutable logs for uploads, mappings, exports, deletions. Include Source File and Batch ID in every dataset.
• Retention: Keep raw files briefly, then purge. Hold only what audit requires. Publish and follow a clear deletion policy.
• PII minimization: Extract only what you need. Mask or skip sensitive counterparty fields if they don’t help accounting.

One SOX-audited org passed testing with no issues after centralizing conversions, turning on dual-approval for exports, and versioning mapping changes. Bonus: fewer shadow copies, cleaner handoffs, and easier auditor walkthroughs because data lineage is built in.

Automating recurring MT942 conversions and integrating with your stack

If you get intraday files every day, automation pays back fast and keeps weekends quiet:

• Scheduling: Watch a secure folder or SFTP. On file arrival, parse, validate, and drop exports in cloud storage or a staging schema.
• Templates: Version your mappings. When formats change, increment the template instead of overwriting, so history stays consistent.
• Notifications: Webhooks or email on success/failure, with links to validation results and outputs.
• Downstream: Send clean CSV/XLSX to accounting imports, treasury tools, or a warehouse. Include metadata (account, sequence, timestamp) so BI can tell intraday from finalized data.

A finance ops team covering five banks across three regions killed their weekend backlog by scheduling conversions and pushing results straight into their data lake. The underrated perk: you get predictable runs and fewer “who converted what, and when?” fire drills.

Troubleshooting: answers to common MT942 conversion questions

• Decimals look wrong in Excel: Replace “,” with “.” before converting to numbers. Also check your locale; CSV imports love to reinterpret separators.
• Signs are flipped: D is debit (negative), C is credit (positive). Don’t guess from text—use the marker.
• No narrative: Some :61: lines truly don’t have :86:. If your system needs a description, fall back to transaction code + bank reference.
• Duplicates after refresh: Deduplicate MT942 intraday sequences (:28C:) with Sequence in the composite key and keep a short “recently seen” cache.
• Weird characters: Wrong encoding. Try UTF-8 or Windows-1252. Strip control characters from :86: narratives.

Example: amounts like “1,234.56” sometimes end up as “123456” due to mixed separators. A simple rule—reject any amount containing both a comma and a dot—forces a quick check and avoids silent errors.

Next steps: choose your path and get your first file converted

Ready to turn raw SWIFT text into a spreadsheet your accounting system will accept? Pick your lane:

• Fastest route: Upload an MT942 to BankXLSX, preview, pick a mapping, turn on dedup, export to CSV/XLSX. Save the template for next time.
• Excel-native: Build an Excel Power Query MT942 parser once, then point it to a folder and refresh whenever new files show up.
• Engineering-driven: Use a Python MT942 to CSV example/script, add tests for :61:/ :86: quirks, and schedule it with your orchestrator.

Write down three things: your Posted Date choice (Value vs Entry), decimal handling rules, and your exact dedup key. That little note will save onboarding time and reduce disputes later. Start with one file, validate with stakeholders, then scale.

Key Points

• MT942 is intraday, not final. Convert tags like :61: (transaction) and :86: (narrative) into columns, fix decimal commas, and use D/C for signs.
• Three solid paths: BankXLSX for the quickest MT942-to-Excel/CSV conversion with templates and dedup; Excel Power Query for occasional, no‑code work; Python for scripted control.
• Avoid traps: join multi-line :86:, convert commas to dots, apply debit/credit signs, and deduplicate overlaps with :28C: plus a composite key.
• Standardize your model: decide Posted Date (Value vs Entry), include Source File and Sequence for traceability, run validations, and automate with proper access and retention.

Conclusion

MT942 is useful but messy. To make it spreadsheet-ready, map the key tags (:61:, :86:, :20:, :25:, :28C:, :13D:), fix decimal commas, apply D/C, and remove duplicates across overlapping sequences.

Set a clear column schema and validations so your reconciliations hold up. You’ve got options: BankXLSX for a fast, supported MT942-to-Excel/CSV workflow, Power Query for an Excel-first setup, or Python for a script you can run daily. If you want results now, let BankXLSX do the heavy lifting—upload, map, export, and move on with your day.