Background

The jnet Portal provides an Excel upload facility that allows bulk upload of data to the system. There are a number of uploads available, including:

  • Billing Company Update
  • Employee Update
  • Payroll
  • Sale Payment
  • Time Sheet Upload
  • Expense Receipt Upload
  • Contract Update
  • Custom Field Update

File format

The system can accept Excel spreadsheets of the type 97-2002 (5.0/95) and which have the file extension .xls

This is the standard format used by Excel before the recent introduction of xml based spreadsheets, which are the standard for Excel 2007 onwards (file extension .xlsx). If you are using one of these later versions of Excel, before uploading please first save your spreadsheet in the older format by clicking Save As... and then selecting the correct type from the File Format box.

Formatting of data

The system recognises the contents of the spreadsheet by looking at the column headers. Each upload screen has instructions at the bottom giving information about what columns are required, and the necessary text for each column header. Care should be taken that these column headers are typed exactly as specified or the system will not recognise that the column in question is valid. These instructions can be found by expanding the File Format section at the bottom of the screen (click the two arrows ">>").

Typically each upload will have one or more key columns, and it is these columns that are used to identify the records that need updating. There will also be a number of mandatory columns and some optional ones. Optional columns are only updated if included.

For example, on the Payroll upload, the following columns are all mandatory:

  • Payroll Date
  • Total Gross
  • Employer NI
  • Gross Pay
  • Employee NI
  • Employee PAYE
  • Net Pay

All other columns are optional, however at least one other column is required in order to identify which records to update. This could be any one of:

  • Employee Reference
  • Employee Payroll Reference
  • First Name and Last Name

So providing just Employee Reference would be enough for a successful upload, as would providing Employee Payroll Reference. However if the name is used, both First Name and Last Name are required.

File uploading

After selecting the desired upload type, click Add to select the spreadsheet file on your computer. If the file does not select, it is likely that it is the wrong file type (see above). Once selected, an Upload button will appear. Click to upload, and then click Test First 10 Rows. The system will then report back on which columns it has recognised, and will highlight any errors that need addressing (without actually changing any data). Once the upload is error free, click Process to commit the new data to the system.

The settings box "Treat blank as NA" affects how the system deals with empty cells in the spreadsheet. If ticked, the system will not perform any action for that blank cell. If unticked, the system will make the relevant data field blank/empty.