How to Set Up a Bank Statement Import Format in Business Central

Short answer: In Dynamics 365 Business Central you define a bank statement import format by opening the Bank Account card and running the assisted setup called Set up a bank statement file format. It creates a data exchange definition where you number each column in your file in the order it appears, map those numbered columns onto fields on the Bank Acc. Reconciliation Line table, and set a negative sign identifier so money out is stored as a negative amount. Once that definition is attached to the bank account, the Import Bank Statement action on a reconciliation accepts your CSV.

CAMT.053 files need none of this. Business Central reads ISO 20022 statement XML natively, so if your bank issues it, that is the shorter road. The setup below is for everyone whose bank sends a CSV, or whose statement started life as a PDF and had to be converted for Business Central first.

What a bank statement file format actually is

Behind the friendly name, a bank statement file format is a data exchange definition. Business Central uses data exchange definitions all over the place for reading and writing external files, and bank statements are one use of the same engine. The definition holds three things: how to read the file at all (type, delimiter, how many header rows to skip), what the columns are, and where each column belongs in Business Central.

The important consequence is that the definition maps by column number, not by header name. Column 1 is the date because you said column 1 is the date. If next month's file has an extra column inserted at position two, nothing errors, the data just lands in the wrong fields. This is the single biggest reason to feed the importer a file that comes out identically every month rather than one somebody assembles by hand.

How do I set up a bank statement import format in Business Central?

Open the bank account you are configuring, then run the Set up a bank statement file format assisted setup from the Bank Account card. Work through the pages in order:

1. Name the definition. Use something you will recognize later, such as the bank name plus the word CSV. You will attach it to the bank account at the end.
2. Set the file type and delimiter. Variable text for a delimited file, then comma or semicolon to match what your file actually uses. Open the file in a text editor rather than Excel to check, because Excel hides the delimiter.
3. Skip the header rows. If your file has a title row, tell Business Central how many lines to ignore before the data starts. Getting this wrong makes the first transaction disappear or the import fail on a date conversion.
4. Number the columns. Enter one line per column in your file, in file order, with a name and a data type. Date columns need the date format spelled out so a US style month first date is not read as day first.
5. Map the columns to fields. Point each numbered column at a field on the Bank Acc. Reconciliation Line table: the transaction date, the description, and the amount are the minimum. A reference or check number column is worth mapping too, because it gives the matching logic something precise to work with.
6. Set the negative sign identifier. This tells Business Central which marker in the data means money out, so those amounts get reversed to negative on import.
7. Attach it to the bank account. The definition is selected on the bank account, which is why each account can read a different bank's layout.

The two settings that break most imports

Nearly every support thread about Business Central statement imports comes back to one of two things.

The debit and credit case deserves a note of its own. Plenty of US banks print two money columns, one for withdrawals and one for deposits. Business Central expects a single signed amount on the reconciliation line, so those two columns have to become one before the file goes anywhere near the importer. Doing it with a formula in Excel works until the month somebody sorts the sheet and breaks the relative references.

How do I import a bank statement into Business Central once the format exists?

Go to the bank account, choose Bank Account Reconciliation, and create a reconciliation for the statement date. Enter the statement ending balance from the PDF, then choose Import Bank Statement and select your file. The rows load as reconciliation lines. From there you match them against bank account ledger entries, using the suggest lines action, matching by hand, or letting Copilot propose matches for you where that feature is enabled.

Copilot is a genuine improvement to the matching step, and it is worth turning on. It does not change anything about this article, though: it reasons over statement lines that are already imported, so the file still has to get into the reconciliation first.

Can Business Central import an Excel file?

Not an .xlsx workbook directly through the bank statement importer. Save the sheet as a comma or semicolon delimited CSV and import that against your file format. In practice most teams keep both: the Excel copy for eyeballing the period before it goes in, and the CSV for the actual import. If you are pulling data out of several systems to build that sheet, the same thinking applies as anywhere else you wire systems together with a repeatable integration rather than a monthly manual export.

What if the statement only exists as a PDF?

That is the normal case for smaller US banks, for accounts that came with an acquisition, and for any period before you connected the Envestnet Yodlee bank feed, since a feed starts on the day you switch it on and does not backfill history. Convert the PDF into a CSV with a stable column order, define the file format once, and every subsequent month imports without touching the definition again.

This is also the workflow for a go live. Business Central implementations rarely start on January 1, so the bank account opens empty while the company has months of activity behind it. Convert each monthly statement, concatenate them, import once per bank account, and reconcile forward from there. The same pattern is covered for other platforms in the NetSuite bank statement converter and the Xero bank statement converter guides, and the guide to reconciling multiple bank accounts covers running several accounts through the same close.

A sensible column layout to standardize on

If you get to choose the shape of the file, use five columns and never change them: transaction date, description, reference or check number, signed amount, and running balance. The first four map straight onto reconciliation line fields. The fifth is not imported, but keeping it means you can prove the file is complete by checking that the last balance in the sheet equals the statement closing balance. That check takes five seconds and catches the dropped row problem before it becomes an unexplained reconciliation difference. Extracting it reliably is what running balance extraction is for.

Once the lines are in and matched, coding them is the remaining work, and categorizing transactions from the bank statement before import saves a pass through the general journal later.