Import a bank statement CSV into Excel without breaking the data

Almost every bank lets you download your transactions as a file. Open that file the wrong way and the trouble starts quietly: every line lands in one column, reference codes lose their leading zeros, and 06/12/2026 turns into June 12 when the bank meant 6 December. This page explains what a bank export really is, how importing differs from opening or pasting, and how to set each column so the table you get is one you can trust for a reconciliation or a monthly report.

What a bank export is

A bank export, often called a CSV file, is plain text. There are no cells, no formats and no formulas inside it. Each line is one transaction, and the fields on a line (date, description, reference, amount, balance) are separated by one chosen character, the delimiter. The first line usually holds the column names.

CSV stands for comma-separated values, but the comma is only one option. You will meet three delimiters most often:

• Comma, the classic CSV: 06/12/2026,Cheque deposit,R005705,4514.96
• Semicolon, common where the comma is used as the decimal mark: 06/12/2026;Cheque deposit;R005705;4514,96
• Tab, which shows up as wide gaps when you open the file in a text editor.

A comma-separated file has a problem with amounts like 1,234.50, because the thousands separator is itself a comma. The fix is quoting: a field wrapped in double quotes is read as one value, commas and all. So "1,234.50" is a single field, and the importer removes the quotes. The bank only quotes the fields that need it, so seeing quotes on some amounts and not others is normal.

Importing versus opening or pasting

There are three ways to get the file into a spreadsheet, and they are not equal.

• Opening the file directly lets the spreadsheet guess everything: the delimiter, which fields are numbers and which are dates. The guess follows your computer's settings, not the bank's, so a day-first file opened on a month-first computer comes out wrong without any warning.
• Pasting the text into a sheet often puts each whole line into column A. You then have to split it afterwards and still fix the types.
• Importing with the import tool shows you a preview and lets you decide: which character separates the fields, what type each column should be, which columns to leave out, and which cell the table starts in.

For bank data, where one wrong date or one lost digit can make a reconciliation fail, importing is worth the extra seconds.

The steps

Whatever spreadsheet you use, an import follows the same order:

1. Open the import tool and load the file. Look at the raw text in the preview before you change anything.
2. Choose the delimiter: comma, semicolon or tab.
3. Set each column's type, as described below.
4. Choose where to put the data: a start cell such as A4, keeping the header row so every column is labelled.
5. Check the result before you build anything on it.

Choosing the delimiter

Look for the character that repeats the same number of times on every line.

The symptom of the wrong choice is unmistakable. When the importer splits at a character that never appears in the file, nothing gets split, and every whole line lands in column A. Columns B onward stay empty. If you see that, go back and pick another delimiter.

Column types: General, Text, Date and Don't import

General

General lets the importer recognise numbers and dates. For amounts this is what you want. A field written as "$1,234.50" or "1,234.50" becomes the number 1234.5: the currency sign and the thousands separator are removed and the value is stored as a number you can add up. Negative amounts such as -355.11 stay negative.

Imported as Text instead, "1,234.50" stays a string of characters that a SUM ignores.

Text

Text keeps every character exactly as written. That matters for reference codes. Many banks give each transaction a six-digit reference such as 004321. Read as General, that looks like a number, so it is stored as 4321 and the leading zeros are gone. Later, when you try to match the bank's references against your own records, which still say 004321, nothing matches. Set the Reference column to Text and the zeros survive.

The same goes for account and cheque numbers. A good test: if you would never add the values up, they are probably Text.

Date, with the right order

Dates are where a quiet import goes most wrong. The text 03/10/2026 means March 10 in a month-first file (M/D/Y) and 3 October in a day-first file (D/M/Y). The file itself does not say which. You have to know, either from the bank or by looking at the data.

Here is the clue. A month can never be above 12, so a field such as 14/12/2026 can only be day-first. If you read a day-first file as month-first, two things happen:

• Rows whose day is 12 or below turn into the wrong dates. 06/12/2026 becomes June 12 instead of 6 December.
• Rows whose day is above 12 stay as text, because there is no month 14. You end up with a column that is half dates and half text.

Setting the Date column to Date (D/M/Y) fixes both at once. If the bank writes month first, choose Date (M/D/Y) instead.

Don't import

Many exports include a running balance after each transaction. In a list you plan to sort, filter or match, it gets in the way: once the rows are sorted, the balances no longer make sense. Set that column to Don't import and it is skipped.

Worked example: a day-first export with a balance column

Here are the first four lines of a bank file for December 2026. The bank writes dates day first and adds a running balance:

Date,Description,Reference,Amount,Balance
06/12/2026,Cheque deposit,R005705,"4,514.96","12,245.04"
12/12/2026,Card payment Swift Couriers,R536445,"-3,367.99","8,877.05"
14/12/2026,Transfer from savings,R567380,"2,096.33","10,973.38"

• Delimiter: commas sit between every field, and the amounts with a thousands separator are quoted. Choose comma.
• Dates: the fourth line has 14/12/2026. There is no month 14, so the file must be day first. Choose Date (D/M/Y).
• Reference: these codes start with a letter, so General keeps them as written.
• Amount: General, so "4,514.96" becomes the number 4514.96 and "-3,367.99" becomes -3367.99.
• Balance: not wanted. Choose Don't import.
• Destination: start cell A4, with the header row.

The table that arrives in A4:D10 looks like this (dates spelled out so there is no doubt which day is meant):

The full file continues the same way to 26 December. Column E stays empty because the balance was skipped.

Now see what the wrong date order would have done to the same rows. Read month first, 06/12/2026 turns into June 12, 2026, six months too early, and 12/12/2026 happens to come out right only because day and month are both 12. The rows dated 14/12/2026 and 15/12/2026 would not become dates at all; they would sit in the column as text. A December statement would appear to start in June.

Checking the result

A minute of checking now saves an hour later:

• Amounts are right-aligned. In General format, numbers line up on the right and text on the left. An amount sitting on the left is text, and a total will skip it.
• The amounts add up. Put a quick SUM under the column and compare it with the statement.
• Dates sort correctly. Sort by the date column. The first row should be the earliest transaction and the last row the latest. Text dates sort alphabetically, so they end up in odd places.
• References still look like the file, especially any that start with zero.

Once the table is clean, the next steps are usually tidying the descriptions (see cleaning text data in Excel), grouping transactions by month (converting dates to months) and spotting repeated lines (finding duplicate transactions).

Common mistakes and how to fix them

These are the mistakes our exercises check for.

Wrong delimiter

Each whole line landed in column A because the file was split at the wrong character. A common version is choosing comma for a file that uses semicolons. Check the raw text and choose the character that really separates the fields.

Amounts imported as text

The amounts and balances look like numbers but can't be added up. Import those columns as General so "1,234.50" becomes 1234.5.

Leading zeros lost

References such as 004321 arrived as 4321 because they were read as numbers. Import the Reference column as Text to keep every digit.

Dates read month first

A day-first file read as M/D/Y turns 03/10/2026 into March 10, and days after the 12th don't become dates at all. Set the Date column to Date (D/M/Y).

Balance column imported

The running balance came in as an extra column that the task didn't want. Set that column to Don't import so the table ends where it should.

Where this fits in MO-220 practice

Bringing outside data into a workbook is part of preparing accounting data for analysis. Our MO-220 practice tasks describe the result you need, such as "references must arrive exactly as written", rather than naming the setting, so you learn to recognise which choice produces it. Accounting Exercises is not affiliated with Microsoft or Certiport.