Xero Payroll Payment file to CSV format (2025)

19th Jun 2025

19th Jun 2025

13th Jun 2025

How to make payments a core offering in 2025

How to prepare for Basis Period Reform

Protect your client's payment reputation

If you open excel, then data tab, then get data (dropdown), from file, then transform data into excel and you can then save as a CSV.

Hopefully you don't need to do anything other than splitting the columns based on tabs in the txt file so should be quite a quick process.

Replying to Viciuno:

Blimey you know how to pick bad payroll software.

Have you asked you accountant about chosing something decent?

They are probably the worst two out there.

If you look for some decent standalone from April you should consniderably cut your processing time and improve your compliance by using a fully featured modern system and not the scrapings out of the bottom of the software pile.

"the payment file is downloaded as an Bacs 18 standard file (txt) however our bank Santander does not accept this" ... WHY not?
As in are Santander refusing to accept an 'instruction to pay via BACS' using the Bacs 18 standard file?
OR are they saying there's something wrong in the file format generated by Xero?

I assume it's the former (which makes a mockery of having an 'industry agreed standard' but that's life) - based on tracking down this:
https://www.santander.co.uk/content/dam/sites/santander-uk-cb/resources/...

When it comes to performing the transformation, note the comment by lannera (about Excel truncating leading zeroes unless you know what you're doing - the most common failing in this type of process).
The next most common failing is if your input text file has any values that contain a comma (which obviously will need to be stripped out before conversion)!
To my mind this is a serious failing in Santander's spec ... CSV (which they refer to as comma separated values but actually stands for character separated values) should be able to use any specified 'special' character as a separator (such as # or ~), which would avoid the danger of data containing embedded commas.

In summary, if you're going to do these conversions regularly WITHOUT spending time hand-checking them then:
1. you need to 'data cleanse' your input file first;
2. it should then be easy to run an export to CSV routine;
3. but you should always then quickly check the output before submitting it.

[items 1 and 3 can have routines written for them - as in, for instance, automating the Find for commas and changing any value with a leading zero to be text not numeric; or performing simple post-conversion validation checks on totals per column - but this requires a little more knowledge if you're intending to rely on it.]

Replying to FactChecker:

You can stop Excel removing leading Zeroes by going into Options - Data - Automatic data conversion and switching off "Remove leading zeroes...."

Better still, set up Power Query (aka "Get and Transform data" on the Data tab) to cleanse and process your Xero exports into the desired format. Once done, it is the work of a moment to transform future exports. This will circumvent the various quite correctly identified issues of other approaches.