Find out how to create a CSV file to submit your declaration, what file layout to use and how to resolve problems with file upload failures.
Businesses can submit EC Sales List declarations online to HMRC.
This guide will help you submit EC Sales List declarations electronically by bulk uploading a Comma Separated Variable (CSV) file containing your declaration data.
It’s designed for Microsoft users using standard keyboard and mouse settings but the principles also apply to other software.
A CSV file is a file where data is separated by a comma rather than columns.
You can produce a CSV file with most spreadsheet software or get a pre-formatted Microsoft Excel spreadsheet by logging into VAT online services, visiting the VAT ESL bulk upload page and choosing the Excel or CSV template link.
Format to use for an ESL CSV bulk upload file
The first box on the first row of the spreadsheet is the title and should be:
The second row is the ‘header record’ where you should enter your own details, these are:
- your UK VAT Registration Number (9-digit number)
- your subsidiary identifier if you have one (if you do not have a subsidiary ID enter ‘000’)
- the year for your declaration (for example ‘2018’)
- the period of your declaration which must be a 2-digit numeric representing a month
- for quarterly traders this will be one of: 03, 06, 09 or 12 (corresponding to the old quarter identifiers 1, 2, 3 and 4)
- for monthly traders this will be one of: 01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11 or 12
- the code ‘GBP’ (indicating that your declaration is made in pounds sterling)
- your contact name (mandatory) maximum of 35 characters (do not include the ampersand ‘&’ symbol, for example ‘A & N Other Ltd’ should be changed to ‘A and N Other Ltd’)
- the indicator field (which is ‘0’)
The ‘schedule line record’ are all rows below the ‘header record’ and should include:
- your customer’s country code which is one of: AT, BE, BG, CY, CZ, DE, DK, EE, EL, ES, FI, FR, HR, HU, IE, IT, LT, LU, LV, MT, NL, PL, PT, RO, SE, SI, SK
- your customer’s VAT Registration Number
- the total value of supplies - if you need to show a negative value of supplies (that is, if the value of credit notes is greater than the supplies) put a minus sign before the figure
- an indicator code which is one of:
- ‘0’ (for business to business goods)
- ‘2’ (if the supplier is an intermediary in a triangular transaction)
- ‘3’ (for business to business services)
An example of a small ESL CSV bulk upload file is:
HMRC_VAT_ESL_BULK_SUBMISSION_FILE 123456789,123,2010,04,GBP,Fred Bloggs,0 DE,123456789,1000,0 AT,U12345678,25000,2 IE,1234567X,1250,3 LU,12345678,3760,0
Create a CSV file
Create a file in a spreadsheet, enter your ESL information and save it in the normal way.
When you’re satisfied that your file is complete and accurate save the file as a CSV by selecting:
- ‘save as…’ then choosing a location where you want to save your file
- ‘filename’ then entering the name you want to give your CSV file
- ‘save as type’ then selecting ‘CSV (Comma delimited) (*.csv)’ from the drop-down menu
You may be told that your file contains incompatible features. Select the option to continue to save your file in CSV. After using the ‘save as’ function you should close your file, do not save the changes again if you’re prompted to.
Do not make any further amendments to the CSV file, update the working version of your spreadsheet instead.
Read the guidance on how to log in and submit your ESL using bulk upload.
Problems uploading your file
Some spreadsheets automatically re-format your data after the file is opened and cause your CSV file to be rejected, for example dropping leading zeros from numeric cells such as the branch identifier.
A customer’s VAT number which loses a leading zero will also fail validation causing the line to reject.
You may get an error message explaining why a CSV file failed to upload.
If your file fails to upload:
- check the file has been uploaded as a .csv file and not another file format for example, .xls, .xlt or .xlw)
- make sure the file conforms to the format of an ESL CSV bulk upload
- confirm the title and ‘header records’ have been correctly completed and check to see if any zeros have been dropped from the subsidiary ID
- check there are no blank spaces in the file or any blank lines anywhere
Validation checks are carried out during the upload of the file and any errors identified will be displayed on the ‘Review and Submit’ screen with an ‘X’. You should make a note of any errors or print off the summary for comparison.
You’ll be able to amend data in your CSV file before resubmitting it or you can make an additional declaration of the corrected data at a later date.
An acknowledgement message with a unique Submission Reference Number will be displayed which you can print and keep for your records. You’ll also be able to view previous submissions online.
Data formatting rejections
If your upload is rejected, you can correct it by either:
- amending the file in a text editor
- formatting the spreadsheet as text
Amend the file in a text editor
Amend the file by opening a text editor such as Notepad on your computer.
To open Notepad select:
- ‘All Programs’
You can now open the file, make any changes and save them.
Format the spreadsheet as text
To change a cell containing numbers to a text cell, you should:
- Right-click the cell.
- Select ‘Format cells’.
- Go to the ‘number’ screen, highlight ‘Text’ and select ‘OK’.
- Retype the data.
- Save the file again.
- Select ‘Bulk Upload’ option and re-submit the file.
This will also correct VAT Registration Numbers where the leading zeros have been dropped or where Excel has converted the number to scientific format (for example, SE 123456789012 converted to SE 1.23457E+11).
Keep the working version of your spreadsheet as the default file type for your program (for example for Microsoft Excel this is .xls).
Before you enter data on your spreadsheet make sure the relevant cells are formatted to show leading zeros.
Properly formatted cells saved as CSV can lose their formatting once the file has been re-opened in Excel so only save the file as CSV immediately before you upload it and do not open a CSV file using your spreadsheet software.
To view or to make changes to a CSV file open the file in Notepad or a similar text editor.