How to Import a Bank Statement CSV into Excel Without Changing Dates or Amounts
Use Excel’s text/CSV import controls, inspect the delimiter and inferred column types, and compare the loaded rows with the source. Importing without changing dates or amounts is the goal of these review steps; Excel does not automatically preserve every CSV correctly.
Why direct opening can change the meaning of a CSV
A CSV contains text values and separators, not workbook column types. Opening it directly can turn an identifier into a number or interpret a date using the wrong regional convention. Decimal and thousands separators can also be read differently from the source. Keep an untouched copy of the CSV and the original statement for comparison.
Microsoft describes the default-format behavior and the import alternatives in its text and CSV import guidance.
Import with explicit column types
- In an Excel version with Power Query text/CSV import, use Data > Get & Transform Data > From Text/CSV and select the untouched CSV. Menu labels and available controls vary by version and platform.
- Check the preview’s delimiter. Each header and value should sit in its intended column; a comma inside a quoted description should not split the description. Confirm that text characters display correctly.
- Choose Transform Data to inspect the columns before loading. Review any automatic type-conversion step, often named Changed Type. If it has already removed leading zeros or misread dates, remove or edit that step so the following choices apply to the original text. Changing a damaged value to Text afterward cannot reconstruct the original.
- Set Reference and other identifiers to Text. Keep Description as Text too. The synthetic reference
001234must remain exactly001234, not1234. - Confirm the statement’s date convention, then use the date column’s Change Type > Using Locale control to choose Date and a locale that matches that convention. For a confirmed DD/MM/YYYY source, English (United Kingdom) is one example; for MM/DD/YYYY, English (United States) is one example. Do not choose based only on your computer’s region.
- Set Amount to a numeric type, such as Decimal Number, and confirm signs and decimal values. Apply the appropriate numeric types to Withdrawal, Deposit, Tax, and Balance if present. Use Using Locale where decimal or thousands separators require it; a text amount that looks right is not enough for calculations.
- Inspect conversion errors and the preview values, then load the table into Excel. Display dates in an unambiguous format, such as
03 Apr 2026, for review. A display format changes appearance; it does not repair an incorrectly parsed date. Save the reviewed workbook separately from the original CSV.
Microsoft’s Power Query locale guidance explains how regional settings affect parsing and how to apply a locale to a type conversion. If your Excel version lacks these controls, use its documented text-import options or an environment that provides explicit column types; do not assume direct opening is equivalent.
A synthetic example: date, reference, and amount
All dates, descriptions, references, and amounts below are invented. This is a subset of fields for illustration, not a complete ExtractMint export. The invented source explicitly uses DD/MM/YYYY and a decimal point, with an opening balance of 800.00 in an invented currency unit.
| Date | Description | Reference | Amount | Balance |
|---|---|---|---|---|
| 03/04/2026 | Paper kite supplies | 001234 | -42.75 | 757.25 |
| 06/04/2026 | Lantern project credit | 001235 | 120.00 | 877.25 |
For this confirmed DD/MM/YYYY example, the first date should display as 03 Apr 2026 after conversion, Reference should remain the text 001234, and Amount should be numeric -42.75. The second date should be 06 Apr 2026, with text reference 001235 and numeric amount 120.00. The closing balance check is 800.00 − 42.75 + 120.00 = 877.25.
In an unknown file, 03/04/2026 could mean 3 April or March 4. Confirm the convention from the source or its provider before choosing a locale. Do not infer it from this example.
Handle dates without a year
Dates that do not include a year need evidence from the statement period or source before conversion. Keep yearless dates as Text until the year and day/month order are confirmed; a locale alone cannot establish the missing year. Do not fill in the current year by default.
In an invented DD/MM statement covering 20 December 2025 through 19 January 2026, the source dates 29/12 and 03/01 resolve to 29 December 2025 and 3 January 2026. Review December/January transitions row by row: applying one year to the whole statement would misdate one of these rows. If the period or source does not settle a date, leave it as text and resolve it before use.
Microsoft’s automatic data conversion guidance describes controls for some conversions, including date-like text, where available. Review your version’s controls and the imported values; these settings do not supply a missing source year.
Convert between signed and split amount columns
First verify the source column labels, numeric types, and sign convention against individual transactions and their running balances. Debit and credit labels alone do not establish how a particular account changes. These formulas apply only after confirming the example convention: positive numeric Debit values are withdrawals and positive numeric Credit values are deposits. Do not apply them blindly to already signed amounts, reversed conventions, or text amounts.
For an Excel row with Debit in B2 and Credit in C2, use =IF(C2="",0,C2)-IF(B2="",0,B2) in D2 to calculate a signed amount: credit positive, debit negative. This treats an empty side as zero; confirm the empty cell means no amount, rather than missing data. In an invented row, Debit 42.75 with an empty Credit gives -42.75; an empty Debit with Credit 120.00 gives +120.00.
If the reviewed signed amount is D2, use =MAX(-D2,0) for a positive Withdrawal output and =MAX(D2,0) for a positive Deposit output. For D2 of -42.75 these return 42.75 and 0; for D2 of 120.00 they return 0 and 120.00. Keep the source columns for comparison and sum either the signed amount or the split outputs, never both.
Check the loaded worksheet against the source
- Count transaction rows against the CSV and original statement, excluding headers and balance summaries. Inspect missing or duplicate rows even when counts match.
- Check the statement period, first and last dates, and date order. Preserve source order while checking running balances; some statements use reverse chronological order.
- Compare descriptions and references character by character where needed, including leading zeros, wrapped descriptions, and punctuation.
- Confirm amounts are numeric, decimal separators were interpreted correctly, and withdrawals and deposits have the intended direction. Compare each value with its source row; do not count Amount and separate debit/credit columns twice.
- Reconcile opening, running, and closing balances. In the invented example, two transactions net to +77.25 and close at 877.25. A matching closing balance does not prove every row or date is correct.
- Resolve conversion errors, blank values, and extraction warnings before using the workbook. Keep ExtractMint’s review and audit columns available for tracing issues.
Limits and privacy
Import settings cannot fix an incorrect source CSV, omitted PDF rows, or an unresolved date convention. ExtractMint is in beta; no bank-specific format has been independently validated against real statements. Review extracted data against the PDF. This guide does not establish compatibility with every Excel version or an accounting import template.
ExtractMint’s core conversion and OCR run in your browser. Optional Ask ExtractMint requests send the question and selected document context to its endpoint and, when configured, an AI provider; site analytics have a separate data flow. Exported files then follow the storage, sync, and sharing settings of Excel and your chosen location. Review the privacy and data-flow details before handling sensitive material.