Skip to main content
Creating a new NHS England: Health Education England, NHS Digital and NHS England have merged. More about the merger.

A guide to importing .csv files into Excel

Opening the GPDfPR reference file in Excel. 

Excel’s auto formatting rules

It is important to understand that Microsoft Excel applies autoformatting rules when opening files. This often results in data loss due to the display limit.

If the number entered is more than 11 digits, it is automatically expressed in scientific notation.

If the number entered is more than 15 digits, all digits starting from the 16th digit are converted to 0 (causing data loss) and cannot be recovered. This can cause problems when these numbers are codes being used for analysis and linkage.

Excel can accommodate long strings of numbers, but you must format them to display as text to prevent data loss.

The GPDfPR reference file

The GPDfPR reference file contains clinical codes including SNOMED and Read codes which are listed in the dataset without any descriptions or additional context.

Do not open the reference file as a .txt or .csv file using Excel because this can lead to the final digits of SNOMED codes being converted to zeros by auto-rounding rules.

SNOMED codes can be up to 18 digits long and the autoformatting functions in Excel will treat the SNOMED codes as scientific or numeric values rather than text.

The code is then no longer correct and will not link correctly to the GPDfPR data tables.

Data loss opening directly in Excel

The first screenshot shows how Excel displays the SNOMED code and the data loss that occurs when you open the file directly in Excel.

screen shot of data set excel spread sheet

The second screenshot displays the same field, but it has been imported into Excel without data loss.

screen shot of data set excel spread sheet with rows highlighted

This guide will work through the process to import the data into Excel so it loads correctly.

Downloading the file

The clinical codes in the GPDfPR reference file can be downloaded from General Practice Extraction Service - Data for planning and research: a guide for analysts and users of the data

You can navigate to it in the page contents by clicking on the dedicated Download button section as shown in the screenshot.

web page with downloads image highlighted

Option 1 - Opening the file from the browser

When you click on the download you should see the option to Open file from the browser as shown in the first screenshot.

web page with downloads section highlighted and downloads tab above

This will open the zipped folder as shown in the second image. 

image of zip file

Option 2 - Opening the file from downloads

Another way to open the zip file would be using File Explorer, navigate to the Downloads folder as seen below and double click on it/right click and Open.

excel file with open with all WinRAR highlighted

      GDPPR cluster refset image with Microsoft search highlighted

save the .csv file from the zipped file

Open the zipped file from your downloads and save the reference file file in an accessible location.

There are 3 ways to do this explained in the 3 options below. 

Option 1 - Use the extract to button in the zip file application.
Option 2 - Use the right-click to copy files to clipboard and paste it in your chosen location (second screenshot).
Option 3 - Select, drag and drop the file to your chosen location.

Option 1 use the extract to 

1.Select the file by clicking on it once.

2.Click on extract to which will load the options screen, seen in the first image below.

3.Select the location you want to save the file, in this case you can see downloads has been selected.

4.Click ok to complete the process.

image of zip file with cluster file displayed underneath

xtraction path and options highlighted

Option 2 Copy files to clipboard

1.Select the file by clicking on it.

2. Right click and select copy files to clipboard
3. Open the location you want to save the file.
4. Right click and select paste

zip file image with copy files to clipboard highlighted

Downloads file displayed with paste button highlighted

Downloads screen with GDPPR cluster ref set displayed on downloads

Option 3 - Drag and drop

1.Open the location you want to save the file.
2.Select the file by clicking on it and keeping hold of the cursor.
3.Drag the file icon to the folder you want and let go.

Open Excel and begin the import

To import the reference file into Excel go to the Data tab on the menu ribbon > Get and Transform data > From Text/CSV.

Excel file with get and transform data from text CSV screen

Select the file

Browse to your file location and then select the file and click import

desktop with GPPDR image highlighted

Transform the data

The window will open to show a summary of your data columns.

Click the Transform data button.

GPPDR excel spreadsheet with transform data highlighted

Power query editor

The Power Query Editor will load and show the applied steps column on the right-hand section of the screen.

Click on the cross next to the Changed Type applied to delete this step. This is very important for the data to import correctly.

GPPDR excel with changed type highlighted

Final step

Click on the Close and Load in the top left corner to import the data into Excel.

excel screen with close and load highlighted

Import completed

You should now see a file like the screenshot below (you will need to scroll along to the RefsetId and ConceptId with the SNOMED codes visible in the correct format. The import is now complete.

Excel file with close and load highlighted

Other ways to work with SNOMED codes

Formatting cells as text in Excel

You may want to paste SNOMED codes into Excel from another location.

In this instance select the relevant destinations cells (the screenshot below has selected all the destination cells) in Excel and change the format to text before pasting the data.

If you paste it and then change the format it won't work as the data will have been corrupted.

Excel file with ab text highlighted

Pasting values as text into Excel

The data should then be pasted as values matching the destination formatting.

excel file with match destination formatting highlighted

excel file example receiving file

Watch the video

We have also created a video which explains how to import .csv files into excel. 

Last edited: 8 June 2022 3:41 pm