What you need You need two files: - A CSV file with the data you want to import
- An IMP file that tells Sage 50 Payroll Ireland how to handle the data
Create an IMP file The IMP file lists the database tables and fields you want to import. You can create it using Notepad or WordPad. View available fields - Log in to a payroll.
- Go to Help, then select System Information.
- Select the link beside Program Directory.
- Locate MICW.DDL, then right-click and choose Open with.
- Select Notepad or WordPad.
 TIP: Don’t import employee year-to-date values. These update when you retrieve an RPN or process payments. Enter other year-to-date values manually in the ETP tab.
Example IMP file [tables]
table1=Employee
[Employee]
record=EMPLMAST
key=EMPLOYEE
field1=employee
field2=name
field3=first
field4=addr1
field5=addr2
field6=addr3
field7=addr4
data=C:\EXAMPLE.CSV
create=yes
blank=yes
decimals=yes
audit=Yes
verbose=Yes
Data import example – Fields explained | Field | Meaning | | [tables] | This needs to appear on the first line. It starts the list of tables included in the import. | | table1=Employee | This tells the system which file you want to update. | | [Employee] | This is the database file you want to update. | | record=EMPLMAST | This tells the system you're updating the Employee Master record. | | key=EMPLOYEE | This is the tag key of the file. A tag key determines the order of the records in a data file. The index key, *.cdx defines this order. The Employee Master file uses employee as its search key. The import module reads the search key from the index key. It searches records using this key.When the system can't find a record, it creates the record or ignores it based on the create= setting. |
Field lines - field1=employee: Employee number
- field2=name: Employee surname
- field3=first: Employee first name
- field4=addr1
- field5=addr2
- field6=addr3
- field7=addr4: Employee address lines
| data=C:\EXAMPLE.CSV | This is the location of the CSV file. | | create=yes | This setting tells the system when to create new records. - No: Only updates records that already exist
- Yes: Updates all records, and creates missing ones
- Only: Creates only missing records
| | blank=yes | This setting tells the system how to handle blank values. - No: A blank value in the CSV doesn't change the database
- Yes: A blank value in the CSV updates the database with a blank
| | decimals=yes | This setting tells the system how to read numeric values. - No: Numeric values in the CSV are in cent (110 = 1.10)
- Yes: Numeric values in the CSV use decimal format
| | audit=Yes | This saves import details in the audit file. | | verbose=Yes | This records any errors during the import. |
IMP file validations and limits -
The import stops when the system finds a misspelt or missing keyword - You can use spaces around =
- Don't use blank lines in the first column
- If a table is assigned nothing and appears in the file, the import stops
-
The system ignores a table when you don’t assign a value to it -
If a field doesn't exist, the system treats it as NULL and records it in the log - You can define unlimited tables
- You can define up to 128 fields per table
Create a CSV file The CSV file contains the data to import. Its fields need to match those in the IMP file. Important: The CSV and IMP file names must match exactly. For example: - NewStarters.csv
- NewStarters.imp
If the names differ, the import won’t work. Example CSV file 001,Smith,Sam,Waterstone House,Moycullen,Galway,
002,Jones,Bill,Citywest,Tallaght,Co Dublin,
Data validations and limits - The system validates mandatory fields and ignores invalid entries
- The system creates imported codes, department, cost centre, etc, only in Employee Details, not at payroll level
- The system ignores extra fields in the CSV
- There's a limit of 2048 characters, including commas, for each record line
Import the data - Open Sage 50 Payroll Ireland and log in.
Tip: Take a backup before you start. - Go to Miscellaneous.
- Select Data Import, then Data Import.
- Browse to the IMP file and select Open.
- When prompted, select Yes to view the import log.
- Check the details are correct.
TIP: Always review the import log after each import. |