Transcription

How to Convert an Excel file To CSVformat.Extremely Important Instructions:1.2.3.4.5.6.Use the Excel Template from UIF. Do not copy and make a new file.Do not change the structure of the Excel file.Leave the PAYE Number blank, if not applicable. Do not delete the column.Fields marked with * in the spreadsheet are mandatory.All date format should be DD-MMM-YYYY (06-Apr-2020).Id Number should not exceed 13 characters and should be without spaces. Addingspaces will discard the record. The Passport Number can be filled in the ID Nocolumn without spaces.7. Do not add spaces in any field between values.8. The values of remuneration should not be comma separated. Example – 26000.90and not 26,000.929. The UI Reference number is the 8 digit number in the format(1234567/8) and notthe U-number. The uif reference number in side the File should always have ‘/’’.The uir reference number without ‘/’ is not recognized.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 NAMEDATA TYPEMAXIMUM LENGTHUIF REFERENCE NUMBERCHARACTER9 (fixed length, must contain ‘/’ character)SHUTDOWN FROMDATEDD-MMM-YYYYSHUTDOWN TILLDATEDD-MMM-YYYYTRADE NAMECHARACTER120PAYE NUMBERCHARACTER20CONTACT NUMBERCHARACTER20EMAIL ADDRESSCHARACTER120ID NUMBER/PASSPORT NOCHARACTERMax 30 . No Spaces allowed.FIRST NAMECHARACTER120LAST NAME (SURNAME)CHARACTERREMUNERATIONNUMERIC12010,2 (no comma separator OR Space in value)Must be 10.2EMPLOYMENT START DATEDATEDD-MMM-YYYYEMPLOYMENT END DATEDATESECTOR MINIMUM WAGEREMUNERATION RECEIVEDDURING SHUTDOWN PERIODNUMERICDD-MMM-YYYY10,2 (no comma separator or Space in value) Mustbe 10.2NUMERIC10,2 (no comma separator in value) Must be 10.2BANK NAMECHARACTER120BRANCH CODECHARACTER10ACCOUNT TYPENUMERIC1ACCOUNT NUMBERCHARACTER20PREFERRED PAYMENT MEDIUMNUMERIC1

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 Language3. Click Additional setting

4. Change the Coma to pipe in Line separator fieldOnce 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 thefile name in the suggested format UIFreferenceNumber DDMMMYYYY uniqueno.Eg- if the uifreferencenumber is 0000003/4 and date of sending file is 06 APR 2020 and thisis the first file you are sending then use 1 as unique file number.

8. On click of Save, you will be prompted with warning message – the selected file does notsupport 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 featuresthat are not compatible with CSV(as below screen). Click Yes button(Marked with an Arrow)

10. Close the file as indicated by the arrow.11. Save the file as indicate 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 thatrepresents column name. Here the first line that starts with Uifreferencenumber must beremoved.15. Add a header on the first line as indicated below . H Date 06Apr2020 and add a Footer at theend 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 formatUIFREFERENCENUMBER DDMMMYYYY uniquesequence.csv Example 00000021 25MAR2020 01.csv . unique sequence number can be a number whichis not used to send file with same name – so when you send file first timeuniquesequence can be 1, when sent second time it can be 2.2. File should start with a Header - H DATE DDMMMYYYYCOLUMN HEADERS AS UNDER ARE NOT REQUIRED IN THE FILE. ITS JUST TOINDICATE SEQUENCING. REMOVE THE HEADER ONCE FILE IS GENERATED. ONLYH DDMMMYYYY is required on the Top3. The values of remuneration should not be comma separated. Example – 26000.90and not 26,000.924. All the dates in CSV file should follow the date format DD-MMM-YYYY – Example –23-APR-20205. Sector Minimum wage per month value is Mandatory. A blank value will result inerror.6. Account Type value should reflect as below. Please use the Account Type IDinstead of Account Type description.ACCOUNTTYPEIDACCOUNTTYPE1Current Account2Savings Account3Transmission Account7. The Preferred Payment Medium column is mandatory to fill as that information isused to pay to the beneficiaries. Use PaymentmediumId value and not thedescription when you prepare the file.8. ## each file should have footer record as underF 2050 , HERE 2050 IS THE NUMBER OF EMPLOYEE RECORDS IN THE FILE