Maya is a freelance illustrator in Leeds, and every quarter she sends her bookkeeping to her accountant in one clean package — no loose PDFs, no gaps, no guessing. The part that used to wreck that plan was the afternoon she spent trying to import your Lloyds Bank statement into Xero by hand. Xero's bank feed covered the recent weeks just fine, but her accountant needed six months of older transactions the feed never pulled, and every CSV she built herself bounced back with a cryptic error. After one particularly painful quarter, her accountant sat her down and explained the real lesson: Xero does not care how careful you are. It cares about exact column order, exact date formats, and exact numbers. Get those right, and the import takes thirty seconds. This guide is the checklist they built together.
What Xero's CSV import really expects
Before touching a file, know the rules Xero plays by. Xero accepts bank transactions from a CSV with three required columns, in this order: Date, Description (Xero calls it Payee in some views), and Amount. That is the minimum. You can add optional columns like Reference or Check Number, but they go after the required three, and optional columns are the usual reason an import silently misaligns — one stray column shifts everything and Xero maps amounts to dates.
The Amount column is where most imports die. Xero wants a single amount column with a sign: positive numbers for money in, negative numbers for money out, or the reverse depending on how your Xero bank account is configured. What it does not want: separate debit and credit columns (a format Lloyds statements often imply visually), brackets around negative numbers, currency symbols like £ or GBP, or thousands separators like 1,234.56. Strip every comma from amounts. If your source has separate columns for money in and money out, combine them into one signed column first.
The Date column must match the date format set in your Xero organization — for a UK company that is DD/MM/YYYY, for example 14/03/2026. This is where UK freelancers get lulled into a false sense of security, which brings us to the trap below. Dates must be real calendar dates; Xero rejects anything it cannot parse, including two-digit years and date ranges like "Mar 1–31."
Download your statement from Lloyds Bank
Lloyds Bank customers download statements as PDF from Lloyds online banking or the Lloyds mobile app — Lloyds does not push full statement CSVs for business accounts in every case, so the PDF route is the reliable one. Log in to online banking, open the account, and look for the statements section; choose the statement period you need and download the PDF. On the mobile app, the equivalent option lives in the account's document or statement area. Download one PDF per statement period rather than a multi-month bundle if you are offered the choice: shorter files convert more accurately and are easier to validate before import.
A quick note on the bank itself: Lloyds Bank is part of Lloyds Banking Group alongside Halifax and Bank of Scotland. If you hold accounts across those brands, each brand's PDFs have slightly different layouts, so convert and import each brand's statements separately rather than merging them. For more on Lloyds-specific formats, see our Lloyds Bank statement overview.
Turn the Lloyds PDF into a clean CSV
Here is the workflow Maya and her accountant settled on. First, upload the Lloyds PDF to a converter and get the raw transactions into a spreadsheet — our guide to converting bank statement PDFs to Excel walks through the extraction step. Export or save the result as CSV, then open it in Excel or Google Sheets and reshape it into Xero's three-column layout: Date, Description, Amount. Delete everything else — balances, running totals, header rows from the statement, and any footer text the converter carried over. Xero reads every row as a transaction, so a stray "Opening balance" line becomes a phantom payment in your books.
Next, normalize the dates. Force the whole Date column into text formatted as DD/MM/YYYY. Do not trust Excel's auto-detected date format here: a Lloyds statement shows 05/06/2026 meaning June 5, but a spreadsheet set to US locale will silently display it as May 6. Select the column, set the format to Text, and type the dates out — or use Text to Columns with the DMY option — then eyeball the first and last rows against the original PDF.
Then normalize amounts. Remove the £ symbol and all thousand-separating commas. Convert separate in/out columns into one signed Amount column: for a normal bank account, money spent is negative and money received is positive. Check the totals: sum your CSV's Amount column and compare it to the net change on the Lloyds statement. If the sums match, your signs are right; if they are the mirror image, flip them. This two-minute check catches the most embarrassing mistake — importing six months of transactions backwards — before it happens. Our step-by-step CSV conversion guide covers the cleanup in more detail.
The DD/MM/YYYY trap and other formatting pitfalls
The date trap deserves its own warning because it bites UK users specifically. Your Xero organization is set to DD/MM/YYYY, and your Lloyds statement is DD/MM/YYYY, so you assume they agree. They do — until your spreadsheet re-interprets a date while you edit. The danger dates are the ambiguous ones: any day from 1 to 12, like 07/08/2026. If Excel flips that to August 7 instead of July 8, Xero imports it happily and your accountant finds transactions in the wrong month. Dates after the 12th (like 14/03/2026) are safe because only one interpretation is a real date. The fix is boring but effective: convert the Date column to Text before you do anything else, keep the DD/MM/YYYY order visible, and spot-check three ambiguous dates against the PDF.
Other pitfalls from the accountant's hit list: descriptions containing commas must be wrapped in quotes or the comma splits one transaction across two columns; multi-line descriptions from the PDF become broken rows — merge them into single-line text; blank rows between transactions cause Xero to skip or error, so delete all empty rows; and never leave the file open in Excel when you upload it, because Xero cannot read a locked file. Finally, save as plain CSV (UTF-8, comma-delimited), not XLSX and not a semicolon-delimited variant that some European Excel installs produce by default.
The 4 Xero import errors everyone hits (and the fixes)
Even careful files bounce sometimes. These are the four errors Maya met in the wild, with the fix for each.
1. "There is an error with the dates in your file." Xero found a date it cannot parse. Open the CSV in a plain text editor (Notepad, not Excel) and look at the Date column. Common culprits: a two-digit year (14/03/26), a header row repeated mid-file from a merged statement, or dates Excel rewrote into MM/DD/YYYY. Fix the offenders in text format and re-upload.
2. "Some transactions may already be recorded in Xero." This is the overlap error: your CSV range includes dates already imported by the bank feed. It is actually good news — Xero is protecting you from duplicates. Shrink the CSV's date range to start after the last feed-imported transaction, or delete the overlapping rows, then import again. Maya's accountant now keeps a simple log of each import's date range to prevent this.
3. "The file format is not supported" or "We couldn't read this file." Almost always an encoding or delimiter problem: the file is semicolon-delimited, saved as XLSX renamed to .csv, or contains invisible characters from the PDF extraction. Re-save as plain comma-delimited CSV (UTF-8), open it in a text editor, and confirm the first line looks like Date,Description,Amount with commas doing the separating.
4. Amounts import with the wrong sign — everything looks reversed. This one shows no error at all, which makes it dangerous. Your spent money shows as received and vice versa. The fix: check the net change against the Lloyds statement (the earlier totals check), flip the signs in the Amount column, and re-import. To undo the damage, Xero lets you delete the imported batch from the bank account's statement lines before you reconcile anything — but only if you catch it before reconciling, so always review the imported lines first.
Frequently asked questions
Can I import a Lloyds Bank statement PDF directly into Xero?
No. Xero's manual import accepts CSV (or OFX/QIF) but not PDF. You must convert the Lloyds PDF to CSV first, reshape it to Date, Description, Amount, and then upload it through Accounting > Bank accounts > Manage account > Import a statement.
Why does Xero reject my CSV even though it opens fine in Excel?
Excel is forgiving; Xero is not. The usual causes are hidden formatting (dates stored as serial numbers, amounts with currency symbols), semicolon delimiters from regional Excel settings, or extra columns shifting the layout. Open the file in a text editor to see what Xero actually sees.
Should I use Xero's bank feed instead of manual CSV import?
Use the feed for day-to-day transactions — it is automatic and less error-prone. Manual CSV import is for the gaps: older statements the feed never pulled, corrected periods, or accounts you choose not to connect. Maya uses both: feed for the current quarter, CSV for everything her accountant needs historically.
How do I avoid duplicate transactions when combining feed and CSV imports?
Never let the date ranges overlap. Note the date of the oldest transaction your feed imported, and start your CSV the day after the newest transaction the feed already covers. If an overlap slips through, Xero's duplicate warning will catch it — delete the overlapping rows rather than overriding the warning.
What date format does Xero expect for a UK company?
DD/MM/YYYY, matching UK convention — for example 14/03/2026. The risk is not Xero's setting but your spreadsheet silently reinterpreting ambiguous dates (day 1–12) into US order. Keep the Date column as plain text and verify a few dates against the Lloyds PDF before importing.
Maya's quarterly routine now takes under an hour: download the Lloyds PDFs, convert and reshape to Xero's three columns, run the totals check, and import with dates in plain-text DD/MM/YYYY. Her accountant gets clean, gap-free books, and the only emails between them are about the business — never about broken CSVs. If you want the same routine without the spreadsheet surgery, ConvertStatement turns your Lloyds Bank PDF into a clean, Xero-ready CSV in minutes — and if you are converting statements regularly, check our pricing for plans that cover heavy bookkeeping months.