Help Centre for Payroll bureaux

How to open CSV files correctly

Whenever employee numbers contain leading zeros (i.e. 004), trailing zeros (i.e. 4.00) or slashes (i.e. 4/2) this can lead to issues when opening and importing Comma Delimited CSV files: leading and trailing zeros will fall off and slashed can lead to recalculation of the number. As this will change the employee number, the system will read this as a new record, creating a duplicate if the employee number was imported correctly before.

This is not because Comma Delimited CSV files do not save the correct information, but is rather due to a formatting issue when opening Comma Delimited CSV files in Excel. When a Comma Delimited CSV file is opened in Excel, all columns are formatted to “general”, resulting in the removal of leading/trailing zeros and recalculating numbers with slashes.

By opening Comma Delimited CSV files in the way described below, the employee number column can be formatted to “text” preventing a change in the employee number.


Step 1

Download the csv-file but do not open the file


Step 2

Open a new Excel spreadsheet


Step 3

In the Data tab, select From Text (third from the left) and select the downloaded file by going to the downloads folder, sorting by Date Modified and selecting the last file


Step 4

×

Choose delimited, press next


Step 5

×

Tick only Comma, press next


Step 6

×

Highlight all columns by holding Shift down, scrolling to the furthest right it can go and clicking the last column displayed.


Step 7

×

Change the Column data format from General to Text then click Finish


Step 8

×

Select the files you want the file to open in (This should be A1) then click OK

“Redington’s Investment Committee assigned an Approved Rating to the NPI DGF and positive on the fund.” — Redington
ATP comes to the UK pensions world with the highest commendations from the Danish trade unions, employers and government. NOW: Pensions' offering in the UK will be high quality, low cost, and honest and I'm proud to be associated with it. — John Monks, member of House of Lords and former General Secretary of ETUC and TUC
I am backing NOW: Pensions, the ATP project, because I believe it will encourage transparency, low costs and decent returns for millions of British employees. — Nigel Waterson, former Shadow Pensions Minister
…We pride ourselves on our abilities to make the perfect match for both clients and workers. Our decision to appoint NOW: Pensions came as a result of wanting a quality workplace pension scheme that is structured, simple and easy for us to… — Ian Naylor, Legal Director of Randstad
Why do we insist on having a choice of fund manager when the evidence shows there is usually no benefit to be gained…and there is always a negative impact in terms of cost? — Anthony Hilton financial editor of the Evening Standard writing in Pensions World, June 2013