Import Bank Statements into Business Central (CSV)

TL;DR Microsoft Dynamics 365 Business Central can import bank statement files into a bank account reconciliation or a payment reconciliation journal, but it reads them through an import format that someone has to set up for each bank account. For CSV files that usually means a data exchange definition describing the columns, the date format and how fields map to statement lines. Once that's in place, the work is producing a consistent file: one row per transaction, one date format, a signed amount, no header clutter, and a balance check that proves the file is complete before you import. Get the file right once, save it as a template, and monthly imports become routine.

Business Central is widely used by small and mid-sized companies, often with automatic bank feeds through an extension or a bank statement service. Manual imports still come up all the time: a bank without a feed, a new account, historic months during a migration, a credit card, or a feed that went quiet. When that happens, you're usually holding PDF statements and need a file Business Central will accept.

This guide explains how statement import fits into Business Central's reconciliation features, what the import setup involves, how to build a clean CSV from PDF statements, a worked example and the fixes for common errors. Business Central is configurable and its pages change between releases and localisations, so we describe things generally. Your partner or administrator can confirm specifics for your environment.

Where bank statement imports fit

Business Central typically uses imported bank statement lines in two places:

• Bank account reconciliation. You import the statement lines, Business Central suggests matches against bank ledger entries already posted, you confirm or adjust the matches, and then post the reconciliation once the statement balance agrees.
• Payment reconciliation journal. Here imported lines can be matched to open customer and vendor entries, so that posting the journal applies payments, for example marking an invoice as paid when the customer's money arrives.

Both rely on the same idea: the bank account has a bank statement import format assigned, and that format determines how a file is read. Business Central generally ships with some predefined formats, often aimed at specific standards or regions, and lets you create your own for CSV files using data exchange definitions.

That's the crucial point. There is no generic "upload any CSV" button. The file has to match the definition assigned to that bank account. When imports fail, nine times out of ten the file and the definition disagree.

Setting up a CSV import format

If your bank account doesn't already have a working format, an administrator or partner will usually create one. Conceptually, a CSV definition answers these questions:

• File type and delimiter. Comma, semicolon or tab separated.
• Header lines to skip. How many lines at the top of the file are headers rather than data.
• Columns. Which column number holds the date, the amount, the description and any reference.
• Date format. For example MM/dd/yyyy or dd/MM/yyyy. This must match the file exactly.
• Decimal and thousands separators. Usually a dot for decimals and no thousands separators.
• Field mapping. Which column feeds which field on the imported statement line.

Once the definition exists, it's linked to a bank statement import format, which is then assigned to the bank account.

If you're not the person who maintains the setup, you only need two things from them: the exact column layout the definition expects and the date format. A sample file that imported successfully is even better.

Step 1: convert the PDF statement to rows

You need a clean table: one row per transaction, nothing else.

• Copy and paste from the PDF is fine for a handful of lines but usually breaks columns and signs.
• Power Query in Excel can extract PDF tables. See our Power Query bank statement guide.
• SheetStatement converts PDF statements to rows with signed amounts and checks the opening and closing balances before export. The free plan includes 3 pages a month, with Starter ($9), Pro ($19) and Business ($39) monthly plans for more.

A simple layout that's easy to map:

Deposits positive, withdrawals negative, in a single Amount column. If your definition expects separate debit and credit columns, produce those instead. See separating debit and credit columns in Excel.

Step 2: run the balance check

Before importing, prove the file is complete:

=ROUND(Opening + SUM(D2:D500) - Closing, 2)

The result must be zero. Business Central's reconciliation compares the statement ending balance with the lines you import and the entries in the ledger, so an incomplete file just produces a reconciliation that won't balance for reasons that are hard to see inside the system. Fix it in Excel first.

Step 3: format the file for the definition

Dates. Every row in exactly the format the definition expects. If the bank uses day/month/year and the definition expects month/day/year, convert in Excel with TEXT(A2,"MM/dd/yyyy") or by setting the column format before saving. Look out for text dates, which often happen when a file from a European bank is opened on a US-configured computer. Our guide to fixing bank statement dates covers the fixes.

Amounts. Plain numbers with the expected decimal separator. No currency symbols, no thousands separators, no parentheses for negatives. See converting parentheses to negative numbers if your statement uses them.

Header rows. Exactly as many header rows as the definition skips. If it skips one, your file needs exactly one header row. An extra title line or a blank first row causes misreads.

No extra rows. Remove subtotals, "balance forward" lines, page footers and blank rows.

Descriptions. Keep the full text, including references. Business Central's matching can use text and amounts to suggest matches, and references often contain invoice numbers that make payment matching much more accurate.

Delimiter and encoding. Save with the delimiter the definition expects. In some regional settings Excel saves "CSV" files with semicolons, which will break a comma-based definition. UTF-8 encoding avoids problems with accented characters in payee names.

Step 4: import, match, post

Import the file into the bank account reconciliation (or payment reconciliation journal) for the right bank account. Then:

1. Compare counts and totals. The number of imported lines and their total should match your file.
2. Check the statement ending balance entered on the reconciliation matches the PDF.
3. Run automatic matching, then review. Confirm correct matches, match the rest manually, and investigate anything left over.
4. Deal with unmatched bank lines. Bank fees, interest and similar items usually need a journal entry or a transfer to an account in the payment reconciliation journal.
5. Post once the difference is zero.

If the reconciliation won't balance and your file passed its balance check, the issue is on the ledger side: a missing posting, something posted to the wrong bank account, or a prior period that was never reconciled. See what to do when the opening balance doesn't match the bank statement.

Worked example

An illustrative month for a small distributor. All figures are made up.

Statement opening balance: €24,610.00. Closing balance: €27,183.90.

The bank's PDF uses day/month/year and the company's CSV definition expects dd/MM/yyyy with a comma delimiter and one header row. The converted file:

Sum: 6,200.00 − 2,145.60 + 4,380.00 − 3,500.00 + 1,912.30 − 4,240.00 − 32.80 = 2,573.90.

Check: 24,610.00 + 2,573.90 − 27,183.90 = 0.00.

Before saving, the Amount column is formatted without thousands separators (so 6,200.00 becomes 6200.00), because a comma inside an amount would break a comma-delimited file.

In the payment reconciliation journal, the first line matches invoice INV-10412 by reference and amount. The 7 October line contains two invoice numbers; Business Central may suggest one, so you apply it to both open invoices manually. The rent, freight and packaging lines match posted vendor payments. The card settlement needs a closer look: it's likely several days of card sales net of processing fees, so you match it to the related entries and record the fee. The €32.80 account fee has no ledger entry, so you transfer it to a bank charges account. Difference: zero. Post.

The card settlement pattern is the same as with any payment processor; reconciling Stripe payouts shows it in detail.

Common import errors and what they usually mean

A date error on the first row. The file has a header row the definition doesn't skip, or an extra line above the header. Make the header count match.

Date errors on some rows only. Dates are mixed: some real dates, some text, or two formats in one file. Typically days above 12 work and days 1–12 get swapped, or the reverse. Normalise every date.

Amounts imported as zero or rejected. Thousands separators, currency symbols, the wrong decimal separator, or a comma inside an amount splitting a comma-delimited row.

Columns shifted by one. A description containing a comma wasn't quoted, or the delimiter is wrong. Re-save the CSV and check it in a text editor.

Signs reversed. The definition expects the opposite convention. Flip the sign in Excel rather than editing every imported line.

Duplicate lines. The period overlaps something already imported. Delete the lines, trim the file and re-import. See removing duplicate bank transactions.

When in doubt, open your file and a known-good sample in a plain text editor side by side. Differences in delimiters, quotes, headers and dates are obvious there in a way they aren't in Excel.

Testing a new import format safely

If you or your partner have just created a CSV definition, test it before relying on it for a real month-end. A careful test takes ten minutes and saves hours later.

1. Build a tiny test file. Take five rows from a real statement: at least one deposit, one withdrawal, one row with a day of the month above 12, one with a day of 12 or below, and one description containing a comma. Those five cover the most common failure points.
2. Import it into a test reconciliation, or into a sandbox environment if your company has one.
3. Check each field. Is the date right, including day and month? Is the amount right, with the correct sign? Did the description come through in full, including the part after the comma?
4. Delete the test lines once you're satisfied, and note the result so nobody has to repeat the test.

If one field is wrong, change one thing at a time in the definition or the file and test again. Changing the date format and the delimiter at the same time makes it hard to tell which fix worked.

Once a test passes, save that five-row file as your reference sample. When an import fails months later, comparing the failing file with the sample in a text editor is usually the quickest diagnosis.

Credit card statements

Business Central can also be used to reconcile credit card activity, typically by setting the card up as a bank account in the system or by handling card transactions through journals, depending on how your company has chosen to work. If you import card statements, watch the signs closely:

• On a card, purchases increase the balance owed and payments reduce it.
• Depending on how the card is set up in Business Central, purchases may need to import as negative amounts (money going out of the "account") and payments as positive.

Test with a small file, check that a purchase reduces the account the way you expect, and keep the convention in your template. Our guide to credit card reconciliation in Excel covers both sign conventions and how to reconcile the card statement before import.

Who should own the setup?

In many small companies, the person doing the monthly bank reconciliation isn't the person who configures Business Central. That's fine, but it helps to agree a simple division of work:

• The administrator or partner owns the import format and data exchange definition, and documents the expected layout: columns, date format, delimiter and header rows.
• The person reconciling owns the file: converting statements, running the balance check, formatting to the documented layout, importing and matching.
• Changes are deliberate. If the bank changes its statement layout, the file preparation changes, not the definition, unless both sides agree.

That way, a failed import has a clear first question: did the file match the documented layout? If it did, the definition needs looking at. If it didn't, the fix is in Excel.

Migrations and historic periods

During a move to Business Central from another system, you'll often need to bring in months of bank activity for reconciliation. Some practical advice:

• Agree the cut-over balance. The first reconciliation in Business Central should start from a statement balance that matches the opening balance you migrated.
• Work oldest to newest, reconciling each month before importing the next.
• Keep the original PDFs and your converted files together, named by account and month, for the audit trail.

Our guide to catch-up bookkeeping with months of statements has a batching approach that works well here.

Making it routine

Once the file layout works, lock it in:

1. Save a template workbook with the exact columns, formats and a balance check tab.
2. Name exports consistently: company-bank-account-YYYY-MM.csv.
3. Record who imported what, with line counts and totals.
4. Add the import and reconciliation to your month-end close checklist.

Get a Business Central-ready CSV from your PDFs

Upload bank statement PDFs to SheetStatement, confirm the opening and closing balances tie out, and export a CSV you can shape to your import definition. Try it on a real statement with 3 free pages a month.

FAQ

Can Business Central import bank statements from PDF?

Not as a standard bank statement import. It reads structured files through an import format assigned to the bank account. Convert PDF statements to CSV first.

What is a data exchange definition?

It's the setup that tells Business Central how to read a file: delimiter, header lines, columns, date format and how fields map to statement lines. CSV imports typically rely on one.

Why do only some of my dates fail?

Usually a day/month versus month/day mismatch, or a mix of real dates and text dates. Normalise all dates to the exact format the definition expects.

Should I use the bank account reconciliation or the payment reconciliation journal?

Bank account reconciliation matches bank lines to posted bank entries. The payment reconciliation journal can also apply payments to open customer and vendor entries. Many companies use the journal for receipts and payments and the reconciliation to close the month.

How do I avoid duplicates with an automatic bank feed?

Import only dates the feed didn't cover, and check counts and totals after each import.

Can I import multiple months at once?

You can, but importing and reconciling one month at a time makes errors far easier to find.

Is it safe to upload statements to a converter?

Check the provider's security policy before uploading. SheetStatement's approach is on its security page.

How much does SheetStatement cost?

The free plan includes 3 pages a month. Starter is $9 a month, Pro is $19 and Business is $39.