Barclays Statement to CSV for Xero, QuickBooks and Excel

TL;DR: For recent Barclays activity, the online banking export gives you a CSV quickly. For older months or when the file must match the official statement, convert the PDF. Then shape the CSV for its destination: a signed amount for Xero, three or four columns for QuickBooks, and the simple date, amount, description layout many UK tools use. Keep dates in day/month/year and verify the balance before importing.

Barclays is one of the banks UK small businesses and sole traders bring to us most, usually with a specific destination in mind: Xero, QuickBooks, FreeAgent or a spreadsheet for their accountant. The extraction part is the easy bit. Getting a CSV that the destination accepts first time, with the right dates and signs, is where the effort goes.

This guide covers Barclays current accounts (personal and business) and Barclaycard credit cards.

Two sources

Online banking export. Barclays online banking lets you export transactions for an account over a date range. The resulting file is usually a CSV with columns such as a number or reference, the date, the account, the amount, a subcategory or type, and a memo or description. The exact columns can vary, so open it in a text editor first to see what you have.

PDF statements. Monthly statements show the start balance, money in, money out and the end balance, with a running balance on each day. They're the official record and go back further than the export.

Use the export for quick, recent work. Use statements for history, reconciliation and anything formal.

A common pattern among the sole traders we work with: the bank feed into their accounting software handles day-to-day transactions, the export fills short gaps when the feed hiccups, and converted statements cover anything older or anything the accountant needs to tie to the official record. Each source has a job, and knowing which one to reach for saves time.

Finding the files

In general terms:

1. Log in to Barclays online banking (the app may not offer every export option).
2. Select the account.
3. For CSV, look for an Export or Download transactions option and choose a date range and CSV format.
4. For PDFs, look for Statements.

Barclaycard has its own online servicing; statements and transaction downloads are available there.

Converting PDF statements

1. Upload to SheetStatement's bank statement to CSV converter.
2. Check the balance verification.
3. Review any flagged rows.
4. Export CSV in the layout you need, or go straight to Xero or QuickBooks format.

The Barclays bank page lists tested layouts.

Verifying

start balance + money in − money out = end balance

If the statement shows a balance after each day's transactions, check day by day. A difference that starts on a particular date tells you where to look. Our balance checker runs the check without formulas.

If you're using the online export, the start balance isn't in the file. Take it from the statement covering the same dates, and make sure the export range matches the statement period exactly.

Dates: day first

UK dates are day/month/year. A CSV with 04/03/2026 means 4 March. Problems arise when:

• Excel is set to US regional settings and reinterprets the dates.
• The destination software is configured for a different region.
• Files from several sources use different formats.

Our approach: keep dates as DD/MM/YYYY for UK tools, check a date after the 12th of a month in every import preview, and use ISO (YYYY-MM-DD) for any workbook that combines sources. If Excel has converted your dates, reformat them explicitly with =TEXT(A2,"dd/mm/yyyy") and paste as values before saving.

Building the CSV for each destination

Xero (very common for UK businesses)

Date,Amount,Payee,Description,Reference
04/03/2026,-45.00,British Gas,DD BRITISH GAS,
05/03/2026,1250.00,Client Ltd,FASTER PAYMENT CLIENT LTD,INV-1042

Signed amounts: money in positive, money out negative. Fill Payee for recurring names. See our Xero CSV import format guide.

QuickBooks Online (UK)

Three columns:

Date,Description,Amount
04/03/2026,DD BRITISH GAS,-45.00

Or four: Date, Description, Credit, Debit. Choose the matching date format (dd/MM/yyyy) on the mapping screen. See our QuickBooks Online CSV import errors guide.

FreeAgent

FreeAgent supports bank statement uploads, including CSV. Its CSV layout is simple: date, amount and description, with signed amounts. Check FreeAgent's current help guidance for the exact format it expects (for example, whether a header row is allowed), since that's what decides whether the upload succeeds.

Excel for your accountant

Keep it readable: Date, Type, Description, Money out, Money in, Balance, plus Category and Notes. Accountants often prefer the statement's own layout with your categories added.

Formatting rules that prevent failures

• No £ signs or thousands separators in amounts.
• Minus sign for negatives, not brackets.
• One header row (or none, if the destination requires none), nothing above it.
• No totals or blank rows.
• Save as CSV UTF-8.
• Check the saved file in a text editor before uploading.

Our CSV format explainer covers these in more depth.

Mapping the online export to a clean layout

Because the export's columns vary, we usually reshape it before doing anything else. The steps:

1. Open the CSV with Data > From Text/CSV, so you control the delimiter and column types. Set any reference or number columns to Text so Excel doesn't round them.
2. Identify the date, amount and description columns. If the export has a separate memo field and a payee or subcategory field, decide which one carries the most useful text. Often the memo has the merchant or payer name.
3. Check the sign convention. In many exports, money out is already negative. Confirm with a known direct debit.
4. Build a new table with exactly the columns your destination needs, using formulas that reference the export, then paste as values.
5. Save as CSV UTF-8 and check the file in a text editor.

If you do this every month, record the steps once in Power Query: the next export just gets dropped into a folder and refreshed. Our Power Query guide shows how.

Business accounts with lots of card takings

Shops, cafés and other businesses that take card payments see daily settlements from their card processor, usually net of fees. If you track gross takings, you'll need the processor's statements as well as the bank statement. A common bookkeeping approach is a clearing account: record gross takings and fees from the processor's reports, and record each bank settlement as a transfer from the clearing account to the bank. Ask your accountant whether they want that level of detail; for some small businesses, net settlements are fine.

For any business, a weekly or monthly summary of settlements by date, compared with your till or point-of-sale totals, is a good control. Differences usually mean refunds, chargebacks or a missing settlement worth chasing.

Keeping records for your accountant

UK accountants generally want the statements for the full accounting period, plus anything that explains unusual items. For sole traders, the tax year runs 6 April to 5 April; for limited companies, it's the company's financial year. Collect the statements that cover those dates exactly, convert them, tag transfers and personal items, and add notes for anything you're unsure about. It's general good practice, and your accountant will tell you exactly what they need. Our guide to bank statements for tax preparation describes the process in more detail.

Barclaycard statements

Barclaycard statements follow the credit card pattern: previous balance, payments, purchases, interest and fees, new balance. Verify with:

previous balance + purchases + interest + fees − payments − refunds = new balance

For import into Xero or QuickBooks, the card should be a credit card account. Spending is money out (negative in Xero's convention), payments to the card money in. If the Barclaycard export shows purchases as positive numbers, flip them before importing into Xero. Check a known purchase and a known payment after import. Our credit card statement converter handles card layouts.

The payment from your Barclays current account to Barclaycard appears on both statements. In your books, it's a transfer between accounts, not an expense.

Common UK transaction descriptions

Barclays descriptions, like other UK banks', often include payment types such as direct debits, standing orders, faster payments, card payments and bank giro credits, sometimes as words and sometimes as abbreviations. Keep the type in its own column if your export or conversion provides it. For categorizing, it's a strong signal: direct debits and standing orders are usually fixed costs, card payments are day-to-day spending, and incoming faster payments are often customer receipts or transfers. See Excel formulas for categorizing transactions.

Personal accounts: a simple monthly budget

Not everyone bringing us Barclays statements runs a business. For personal current accounts, the same CSV works for a simple budget. A layout we like:

• Date, Type, Description, Amount from the statement.
• Category from a rules table.
• Month for pivots.

Then a pivot by category and month, excluding transfers to your own savings. Direct debits and standing orders give you your fixed costs; card payments show where flexible money goes. Our expense tracking guide sets out a routine that takes about twenty minutes a month.

If you share a joint account, add a column for who paid shared costs, and a one-line settle-up calculation. It's a surprisingly effective way to keep money conversations short.

When the export and the statement disagree

Occasionally the online export and the PDF statement for the same dates don't match exactly. The usual reasons are pending items in the export, an export range that doesn't line up with the statement period, or a transaction that posted with a different amount from its pending amount. The statement is the official record; when they disagree, reconcile to the statement and use it for anything formal.

A worked example

A sole trader uses Xero and a Barclays business current account. The bank feed broke for six weeks, and she needs to fill the gap.

1. She downloads the two monthly PDF statements covering the gap and converts them.
2. Both statements balance.
3. She notes the feed's last transaction date (14 May) and first transaction date after reconnecting (27 June). She'll import only 15 May to 26 June.
4. She exports a Xero CSV, trims it to those dates, and fills Payee for her eight regular payees.
5. In Xero, she imports into the business current account, choosing dd/mm/yyyy. In the preview, 23/05/2026 shows as 23 May. Good.
6. She checks one direct debit (money out) and one client payment (money in) on the reconcile screen. Both on the right sides.
7. Xero's statement balance now matches the bank's balance for 26 June.
8. She reconciles the gap, using bank rules for the regulars.

No duplicates, because she imported only the dates the feed didn't cover. The whole job took about half an hour, most of it in reconciliation, which is where the time should go.

Troubleshooting

Xero shows dates in the wrong month. Wrong date format chosen on import. Re-import with dd/mm/yyyy.

Everything imports as money in. Money out wasn't negative. Fix signs.

Export opens in one column. Your Excel locale may expect a different delimiter. Use Data > From Text/CSV and choose comma.

Balance doesn't match after import. Overlap with the bank feed, or the start balance in your software is wrong. Check the dates you imported.

Long references turned into numbers like 1.23E+11. Excel converted them. Format the column as text before saving, or edit the file in a text editor.

Upload rejected with no clear reason. Open the file in a text editor and look at the first two lines. A stray title line, a byte order mark or an unexpected delimiter is usually visible immediately.

Security

Use a converter that encrypts uploads and deletes them after processing; see our security page. Don't email statements or CSVs without need; use your accountant's secure portal if they have one.

Our take

With Barclays, the extraction is easy. The value is in the last mile: a CSV built for its destination, with UK dates and correct signs, and a verified balance. Get those right and imports become a two-minute job instead of an afternoon of error messages.

Troubleshooting Barclays conversions

A few issues come up often enough with UK statements, including Barclays, to be worth listing.

The totals are out by a balance-forward line. Statements commonly start with a "Start balance" or "Balance brought forward" row. If it gets treated as a transaction, your sum is off by exactly the opening balance. Remove it from the transaction list and use it only for the check.

Dates sort strangely. UK dates are day-first. If you open a CSV in a spreadsheet set to a US locale, dates up to the 12th are read as month-first and later ones stay as text. Set the locale before importing, or use Text to Columns with DMY.

Card payments show the wrong date. Card transactions often appear on the statement on the date they were processed, a day or two after the purchase. For bookkeeping, use the statement date; it's what the bank feed and the reconciliation will use.

Money in and Money out arrive as separate columns. Merge them into one signed amount for tools that want it (=N(MoneyIn)-N(MoneyOut)), or keep both for Xero, which accepts either layout, as covered in our Xero CSV format guide.

A second worked example: a sole trader's VAT quarter

A sole trader asks for a VAT-quarter spreadsheet from three Barclays business statements.

1. Convert all three statements and check each against its start and end balances.
2. Combine them into one table, sorted by date, and remove the two balance-forward rows.
3. Add a category column and tag income, purchases, and transfers to the personal account.
4. PivotTable by category for the quarter.
5. Hand the spreadsheet and the original PDFs to the accountant, who decides the VAT treatment of each line.

The bank data doesn't tell anyone what VAT applies, but having the quarter in a clean, verified table makes the accountant's job far faster.

Mini checklist

• Statements chain, with no missing months.
• Balance-forward lines removed.
• Dates are real, day-first dates.
• One signed amount column, or both columns, as the destination needs.
• Totals verified per statement.

FAQ

How do I export Barclays transactions to CSV?

In Barclays online banking, look for the option to export or download transactions for an account, choose a date range and the CSV format. For older periods, convert PDF statements instead.

Can I convert a Barclays PDF statement to CSV?

Yes. Upload it to a bank statement converter, check the balance verification and export CSV in the layout your destination needs.

What date format should I use for UK accounting software?

Usually day/month/year, matching your organization's region. Check a date after the 12th of a month in the import preview to confirm it's read correctly.

How do I import a Barclays CSV into Xero?

Use a signed Amount column, Date, Payee and Description, import into the matching bank account, choose the correct date format, and check a sample of lines before reconciling.

Does this work for Barclaycard?

Yes. Verify with previous balance plus purchases, interest and fees minus payments and refunds, and import into a credit card account in your software.

Why does my CSV open in one column in Excel?

Your regional settings may expect a different delimiter. Import with Data > From Text/CSV and choose comma explicitly.