Help Centre for Employers

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

Over a short period of time, NOW: Pensions has established itself as a respected and credible player in the UK workplace pensions market attracting thousands of employers and hundreds of thousands of members. Joining the team at such a crucial time… — Jocelyn Blackwell, Founder of Dunnett Shaw and Raising Standards in Pensions Administration
I firmly believe in NOW: Pensions' principle that everyone deserves the right to a better retirement. I look forward to being part of the team which aims to achieve this in the UK. — Win Robbins, former Head of European Fixed Income Barclays Global Investors
We decided to appoint NOW: Pensions because of the simplicity and robustness of its scheme. The NOW: Pensions product is both cost effective and straightforward to implement... — Mark Roberts, Managing Director at gap personnel
I'm excited by the opportunity to help bring to the UK auto-enrolment market NOW: Pensions, a customer-friendly and responsive trust-based alternative to NEST and to contract-based offerings. — Chris Daykin, the former Government Actuary
I've known ATP for many years and have enormous respect for the effectiveness of their investment strategy. I'm convinced that NOW: Pensions can become a major player in the UK and look forward to being a part of that success. — Chris Daykin, the former Government Actuary