Data format
Data should be extracted in the following formats:
Data
- ASCII i.e. Text
Fixed length or character delimited records (e.g. CSV), where each field is separated by a specific character, are acceptable. If supplying a delimited file, the delimiter should not be in the data unless fields are encapsulated with text qualifiers (usually quotation marks). Likewise the text qualifier should not be in the data. A good choice for a delimiter, instead of the conventional comma, is a: a pipe ( | ); or a tilde (~).
- Excel (see separate note below)
Date fields
ddmmyyyy (preferred, but ddmmyy acceptable). If a date separator is used, it should be either a forward slash ( / ) or a hyphen ( – ). Other date formats are also acceptable i.e. dd-mmm-yyyy (01-JAN-2012); dd month yyyy (01 January 2012); dd/mm/yyyy hh:mm:ss (01/01/2012 10:14:36); etc.
Monetary fields
Monetary amounts can be supplied with or without a £ sign. Monetary amounts must be in the same format throughout the file. If the format is pounds and pence (i.e. £123.45), this should be consistent throughout the file; not supplying some records with just pence (i.e. 12345) as this will be translated as £12345.00. The entire file must be either pounds and pence or just pence.
If monetary values cannot be supplied, the field should be left blank. Please do not insert a zero.
Character fields
If a field type is neither a date nor numeric (ie, it is alpha numeric) this is referred to as a ‘character’ field for the purposes of this exercise. Note that fields such as ‘VAT registration number’ and ‘suppliers invoice number’ (which can often contain a combination of letters and numbers) are characters fields.
Blank fields
should be space filled for fixed-length records. For CSV records, the blank field must still be represented by a delimiter.
Excel
There are a number of risks associated with exporting data into Excel from the host system. However, given that it is by far the most commonly used data handling/recording application, we understand that it would make it easier for many to be able to submit data in Excel format. Therefore, whilst we are prepared to accept Excel files, we recommend that you carry out the basic pre-submission data quality checks to ensure that the formatting issues below do not affect the quality of the data being submitted.
Some of the more common problems that can be encountered when using Excel are listed below:
- Most versions of Excel have a maximum number of rows it can store in a spreadsheet. The below table illustrates the maximum number of rows for the different Excel versions. If this is exceeded then the data will be truncated when saved, resulting in a loss of records.
| Excel Version | Maximum number of rows |
| Excel 2.0 | 16,384 |
| Excel 3.0 | 16,384 |
| Excel 4.0 | 16,384 |
| Excel 5.0 | 16,384 |
| Excel 7.0 | 16,384 |
| Excel 97 (version 8.0) | 65,536 |
| Excel 2000 (version 9.0) | 65,536 |
| Excel 2002 (version 10) | 65,536 |
| Excel 2003 (version 11) | 65,536 |
| Excel 2007 (version 12) | 1,048,576 |
| Excel 2010 (version 14) | 1,048,576 |
- Numeric strings of 16 digits or more are treated as numbers by default and only the first 15 significant figures are stored.
- Leading zeros are removed from numeric strings, try typing ’01062007′ into a cell. This is a very common problem which can affect dates, invoice numbers, bank details etc.
- Excel will automatically transform anything that looks like a date, however remotely, into a date. This causes problems with Bank_Sort_Codes using hyphens (type ’21-11-97′ into a cell) and house numbers if they are supplied in a different column to the street name (I.e. ’05-07′).
File Extensions
As mentioned in the data download page we have introduced a new protocol, which promotes security of personal data while sitting on the host system, whereby data should be password protected at the earliest opportunity after extraction. Standard encryption or compression software usually produces a file with a .zip, .7z or .rar extension.
Regarding the file types of data extracted from the host system, we have produced a list of acceptable extensions:
.csv, .xls, .txt, .xlsx, .doc, .ASC, .dat, .TSV, .lis, .xlsm, .ttx, .docx, .lst, .chr, , .mdb, .EXT,.out.
This list is not exhaustive so if your file data has an extension that is not on the above list please check with our helpdesk
Data should only be submitted using the Data File Upload (DFU) facility within the NFI secure web site. This is now the only acceptable method to supply data. If another submission method is used our policy will be to inform the Director of Finance that data have been put data at risk unnecessarily.