STEP-BY-STEP GUIDE FOR CONVERTING SPREADSHEETS TO CSV FORMAT FOR UIF SUBMISSIONS

Employers who are making use of the opportunity to claim the UIF benefits through the National Bargaining Council for the Electrical Industry (NBCEI) may already have been informed by the NBCEI to submit the spread sheet in a CSV format.

For assistance, please use the following user-friendly guide from UIF on how to convert a document from Excel to a CSV format.

How to Convert an Excel file To CSV format

Extremely Important Instructions

  1. Use the Excel Template from UIF. Do not copy and make a new file.
  2. Do not change the structure of the Excel file.
  3. Leave the PAYE number blank, if not applicable. Do not delete the column.
  4. Fields marked with * in the spreadsheet are mandatory.
  5. All date format should be DD-MMM-YYYY (06-Apr-2020).
  6. ID number should not exceed 13 characters and should be without spaces. Adding spaces will discard the record. The passport number can be inserted in the ID No column without spaces.
  7. Do not add spaces in any field between values.
  8. The values of remuneration should not be comma separated. Example – 26000.90 and not 26,000.92
  9. The UI reference number is the 8-digit number in the format (1234567/8) and not the U-number. The UIF reference number inside the file should always have ‘/’’. The uir reference number without ‘/’ is not recognised.
  10. No spaces should be anywhere in or between values. Trim all the values in the file. Files with spaces are discarded and not processed.
  11. Column requirements:
COLUMN_NAME DATA_TYPE MAXIMUM_LENGTH
UIF REFERENCE NUMBER CHARACTER 9 (fixed length, must contain ‘/’ character)
SHUTDOWN FROM       DATE DD-MMM-YYYY
SHUTDOWN TILL       DATE DD-MMM-YYYY
TRADE NAME          CHARACTER 120
PAYE NUMBER CHARACTER 20
CONTACT NUMBER CHARACTER 20
EMAIL ADDRESS       CHARACTER 120
ID NUMBER/PASSPORT NO. CHARACTER Max 30. No spaces allowed.
FIRST NAME          CHARACTER 120
LAST NAME (SURNAME)  CHARACTER 120
REMUNERATION       NUMERIC 10,2 (no comma separator OR space in value) Must be 10.2
EMPLOYMENT START DATE DATE DD-MMM-YYYY
EMPLOYMENT END DATE   DATE DD-MMM-YYYY
SECTOR MINIMUM WAGE NUMERIC 10,2 (no comma separator or space in value) Must be 10.2
REMUNERATION RECEIVED DURING SHUTDOWN PERIOD       NUMERIC 10,2 (no comma separator in value) Must be 10.2
BANK NAME           CHARACTER 120
BRANCH CODE         CHARACTER 10
ACCOUNT TYPE        NUMERIC 1
ACCOUNT NUMBER CHARACTER 20
PREFERRED PAYMENT MEDIUM     NUMERIC            1
  1. Press Ctl + R on a Windows Machine and type control panel and press ‘Enter’.

Alternatively, go to Windows ‘start’ button and select ‘Control Panel’.

2. Select region and language

3. Click Additional setting.

4. Change the comma to pipe in line separator field.

Once comma is changed to Pipe, click ‘Apply’ button and then ‘OK’ button.

5. Open the Excel file which has all employee information. Check * for all mandatory fields. Complete all mandatory fields for successful loading. Failure will result in No Payment.

6. Go to ‘File’ (top left in the File) and click ‘Save As’.

7. Select the ‘Save’ as type option dropdown and select CSV (comma delimited). Also change the file name in the suggested format UIFreferenceNumber_DDMMMYYYY_uniqueno.

E.g. If the uifreferencenumber is 0000003/4 and date of sending file is 06 APR 2020, and this is the first file you are sending, then use 1 as unique file number.

8. On clicking on ‘Save’, you will be prompted with warning message – the selected file does not support workbook that contain multiple sheets. Click ‘OK’ button (marked with an arrow).

9. You will again be prompted with another warning message that the file may contain features that are not compatible with CSV (as in the screen below). Click the ‘Yes’ button (marked with an arrow).

10. Close the file as indicated by the arrow.

11. Save the file as indicated by the arrow.

12. The file will be saved in CSV format at the location where the Excel file is located.

13. Right click on the file and select ‘Open’ with option to select ‘Notepad’.

14. The file will look like the one below with Pipe (|) delimiters. Remove the header that represents column name. Here the first line that starts with Uifreferencenumber must be removed.

15. Add a header on the first line as indicated below. H|Date 06Apr2020 and add a footer at the end of the file F|Number of records in the file (as seen in the screen below).

16. Save the file using the ‘Save’ option. The CSV file is ready for submission to the UIF.

17. Verify the file by opening in Notepad and verify the following:

The CSV file is mandatory for both Payroll and Non Payroll companies.

  1. ##Filename should be in the following format

UIFREFERENCENUMBER_DDMMMYYYY_uniquesequence.csv Example – 00000021_25MAR2020_01.csv . unique sequence number can be a number which is not used to send file with same name – so when you send a file for the first time uniquesequence can be 1, when sent second time it can be 2.

2. File should start with a header – H|DATE DDMMMYYYY

COLUMN HEADERS AS UNDER ARE NOT REQUIRED IN THE FILE. ITS JUST TO INDICATE SEQUENCING. REMOVE THE HEADER ONCE FILE IS GENERATED. ONLY H|DDMMMYYYY is required on the Top

3. The values of remuneration should not be comma separated. Example – 26000.90 and not 26,000.92

4. All the dates in CSV file should follow the date format DD-MMM-YYYY – Example – 23-APR-2020

5. Sector Minimum wage per month value is Mandatory. A blank value will result in error.

6. Account Type value should reflect as below. Please use the Account Type ID instead of Account Type description.

ACCOUNTTYPEID ACCOUNTTYPE
1 Current Account
2 Savings Account
3 Transmission Account
  1. The Preferred Payment Medium column is mandatory to fill as that information is used to pay to the beneficiaries. Use PaymentmediumId value and not the description when you prepare the file.
  • ## each file should have footer record as under

F|2050 , HERE 2050 IS THE NUMBER OF EMPLOYEE RECORDS IN THE FILE

Random Posts