| Description | Please be aware that data import is a complex process. If you have any queries, please contact Sage technical support or your accredited business partner for further assistance. Alternatively, Sage can potentially perform the data import for you, as a chargeable service. For more information about this, please email contact technical support. |
Resolution | Importing data in Sage Payroll requires two separate files to complete the process: a CSV file containing the data you wish to import, and a IMP file which instructs Sage Payroll in how to handle the data in the CSV file.
Create an IMP fileThe *.IMP file contains the database files and payroll fields you want to import into your payroll. You can create the *.IMP file using either Notepad or Wordpad. Import fieldsTo view a full list of the database fields that can be imported into: - Log into a payroll, click Help then System Information.
- Click the link beside the Program Directory.
- Locate file MICW.DDL, and right click on it.
- Click Open with, then select Notepad or Wordpad.
NOTE: We recommend you don't import employee year to date values, as they are set when an RPN is retrieved or when you process payments. Any other year to dates should be entered manually to the ETP tab of the employee record. Example IMP fileThe following is an example of a *.IMP file using just one table, although more than one table can be used. [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 [tables] | Must appear on the first line. |
---|
table1=Employee | Specifies the file you want to update. |
---|
[Employee] | The Database file to be updated. |
---|
record=EMPLMAST | The record to be updated. |
---|
key=EMPLOYEE | The tag key of the file. NOTE: A tag key determines the ordering that the records in a data file should be presented in.The information that this ordering is based upon is called an index key (*.cdx file), and this file defines the index key for each record. This key is assigned to the tag key,"employee" in the above example. In the case of the Employee Master file, the searchkey is simply the “employee”.
The import module reads the search key from the index key, records are searchedlinearly using the defined key in the import file, and if a record is not found in such a search, it is either created or ignored. |
---|
field1=employee | The employee number. |
---|
field2=name | The employee surname. |
---|
field3=first | The employee first name. |
---|
field4=addr1 field5=addr2 field6=addr3 field7=addr4 | The employee address lines 1, 2, 3 and 4. |
---|
data=C:\EXAMPLE.CSV | The location of the .CSV file. |
---|
create=yes | An entry of No/Yes/Only. A create condition:
• No – Only existing records are updated, new records will not be created. • Yes – All records are updated, whether they exist or not. • Only – Only records that did not previously exist are updated. |
---|
blank=yes | An entry of No/Yes. A blank condition:
• No – If a blank condition has been inserted into the .CSV file for a particular field, the value for this field in the database (.DBF) file remains the same, the field is not updated. • Yes – If a blank condition has been inserted into the .CSV file for a particular field, the value for this field in the database (.DBF) file will be updated with a blank/null value. |
---|
decimals=yes | An entry of No/Yes. A decimal condition:
• No – The values that you are importing into numeric fields are displayed in cent in the *.CSV file (i.e. 110 instead of 1.10). • Yes – The values that you are importing into numeric fields are displayed as decimals figures. |
---|
audit=Yes | Will record import info on an audit file. |
---|
verbose=Yes | Should be set to Yes to ensure any errors are recorded. |
---|
IMP file – Data validations and limitations- The import function will generate an error and abort the process if any of the Key words are incorrectly spelled or omitted from the import file.
- Spaces are permitted between assignments of tables/fields (i.e. spaces are allowed before/after the '=' operator).
- Blank lines are not permitted in the first column of this file.
- If a table has been assigned to nothing, but there is a valid selection for it in the import file the program will be aborted.
- If a table has been assigned to nothing, it is ignored by the import – this is the same as assigning it to NULL.
- If a field has been assigned to a value that doesn't appear in the database file this is treated as if it has been assigned a NULL. A message will appear in the monitor file.
- There is no limit to the number of tables that you may define in the import, but for each table you may only define up to 128 fields.
Create a CSV fileThe *.CSV file contains the data you want to import into your payroll. Its fields correspond to those detailed in the .imp file you created earlier. NOTE: The name of the *.CSV file must match the *.IMP file exactly. For example, NewStarters.csv and NewStarters.imp. If the names are different, the import won't work. The following is an example of the contents of a typical CSV file: 001,Smith,Sam,Waterstone House,Moycullen,Galway, 002,Jones,Bill,Citywest,Tallaght,Co Dublin, The above CSV file content must match the fields on the corresponding *.IMP file: field1=employee field2=name field3=first field4=addr1 field5=addr2 field6=addr3 CSV file – Data validations and limitations- All mandatory fields involved in the import process are validated. If an invalid entry is being imported, it will be ignored, and the field will not be updated in the database.
- If a department, cost centre, bank, job or location code has been imported into the Employee Master File, it will have been created exclusively in the Employee Details, and not at payroll level. For example, if you add department ABC to an employee import, it will not be created in Company/Payroll > Departments.
- If the CSV file has more fields per record than is defined in the IMP file, the extra data is ignored by the import process. The import is concerned only with the fields that match the corresponding entries in the import file.
- There is a limit of 2048 characters or digits, including commas for each record line in the CSV file. Once the limit is reached, the rest of the line is ignored.
How do I import data?- Open Sage Payroll and log in to the payroll as normal.
NOTE: Before you proceed, you must take a backup of your payroll data. - On the menu bar, click Miscellaneous, then Data Import then Data Import.
- Browse to and select the *IMP file you want to import, then click Open.
- When prompted to view the import log, click Yes.
- Check the details of the data import are correct.
NOTE: You should view the import log after every data import process.
[BCB:22:IE - Sales message :ECB] |
|