Billing

Import ledger balances from Excel

You can import ledger balances from an Excel workbook to Onvio and add them to the debtor’s ledger.
This function will help you if you've got active invoices and receipts recorded in a different system that you want to bring over to Onvio.
You could also use it as a way of producing recurring invoices with fixed fees for several clients, such as invoices for Companies House fees.

Pre-import checks

To make sure that your ledger balance can be imported successfully, you will need to do the following.
  1. Check that your ledger includes data in columns that can be mapped to the required Onvio Column Types in the table below (Client IDs, Gross, Date, Type (Invoice/Receipt)).
    note
    The names of column headings in your ledger do not need to match the name of the Onvio Column Type exactly. You will use an import wizard to map your columns to Onvio Column Types during the import procedure.
  2. If your ledger includes invoices, you will need to check that it includes data in columns that can be mapped to the following Onvio Column Types: Net, VAT, Staff ID and Service ID.
  3. If your ledger includes receipts, you will need to check that it includes data in columns that can be mapped to the following Onvio Column Types: Bank Account and Payment Type.
  4. Check that the content and format of the rows in your ledger adhere to the pre-processing validation rules in the table below (e.g. that the date cells are in dd/mm/yy or dd/mm/yyyy format).
  5. Check that Client IDs are set up in Onvio and, if the ledger includes invoices, that the Service IDs and Staff IDs listed in the ledger are also set up in Onvio.
  6. If your ledger contains receipts, check that the Bank Accounts described in the ledger are set up as Bank Account IDs in Onvio.
  7. If your ledger includes Activity IDs, check that these are set up in Onvio.
  8. Check that the ledger you want to import is saved as an Excel workbook with a .xlsx file extension, that the file size is no more than 1MB and that each worksheet has no more than 1000 rows of data.
Table 1: Validation rules
Onvio Column Type
(*always required)
Pre-processing validation
Processing validation
Client ID*
  • Must be mapped.
  • Must be specified for every row.
  • No more than 20 characters.
  • Must exist in Onvio.
Client name
  • Mapping is optional.
N/A
Type (Invoice/Receipt)*
  • Must be mapped.
  • Must be specified for every row.
  • Value must be either: Invoice or Receipt.
N/A
Net
  • Must be mapped if there are invoice rows.
  • Must be specified for every invoice row.
  • Numeric.
  • No more than two decimal places.
N/A
VAT
  • Must be mapped if there are invoice rows.
  • Must be specified for every invoice row.
  • Numeric.
  • No more than two decimal places.
  • If VAT in the ledger differs from the amount calculated by Onvio during the import process, Onvio will adjust the amount according to its calculation.
  • Any adjustments to VAT are shown with a warning in the Successful Import report.
Gross*
  • Must be mapped.
  • Must be specified for every row.
  • Numeric.
  • No more than two decimal places.
  • For an invoice row, Net + VAT must equal Gross.
N/A
Date*
  • Must be mapped.
  • Must be specified for every row.
  • Format can only be either: dd/mm/yy or dd/mm/yyyy.
N/A
Invoice number
  • Mapping is optional.
  • Numeric (see the Reference row in this table if you've previously used alphanumeric invoices).
  • No leading zero.
  • Multiple rows with the same number will be added to the same invoice.
  • If blank, Onvio will use the next available number.
  • All rows with no invoice number specified will form one invoice for each row.
Service ID
  • Must be mapped if there are rows of invoices.
  • Must be specified for every invoice row.
  • No more than 20 characters.
  • Must exist in Onvio.
  • If it's not specified, Onvio will show an error.
Activity ID
  • Mapping is optional.
  • No more than 20 characters.
  • Must exist in Onvio.
  • If not mapped or you don't specify it, Onvio will apply the default Open_Bal activity.
Payment type
  • Mapping is optional.
  • If specified, the value must be either: Cash, Cheque, Electronic Funds Transfer, or Credit Card.
  • If mapped but not specified, Onvio will apply a value of Cash.
Staff ID
  • Must be mapped if there are rows of invoices.
  • Must be specified for every invoice row.
  • No more than 20 characters.
  • Must exist in Onvio.
  • If you don't specify it for invoices, Onvio will apply a default staff member called DEBT_BFWD.
Bank Account
  • Must be mapped if there are rows of receipts.
  • Must be specified for every receipt row.
  • Must exist as a Bank ID in Onvio.
Reference
  • Mapping is optional.
  • If specified, must be no longer than 30 characters.
  • If a receipt or invoice was previously alphanumeric, you can enter that value here, which will allow you to use it to filter reports later.
  • If specified and an invoice has more than one entry, then the reference of the first entry processed will be used.
Comments
  • Mapping is optional.
  • Use this column to add notes about receipts.
N/A
Description notes
  • Mapping is optional.
  • Use this column to make notes about invoices.
  • If used and an invoice has more than one entry, then the first entry that's processed will be used.

Select and upload the file to be imported

  1. Choose Setup > Import Ledger from the menu bar.
    note
    You will find Import Ledger under the Utilities heading in the list of Setup menu commands, but it will only be displayed there if your Onvio administrator has given you the necessary permission rights to import ledgers.
  2. The Select file tab of the Ledger Balance Import wizard is displayed. To import an Excel file from your device or network:
    1. Select
      My Computer
       from the Select Source drop-down list.
    2. Either drag a file onto the box, or select the Browse for file button to select a file.
    3. When a file has been added to the box, select the Next button.

Map Excel columns to Onvio Column Types

  1. The Map tab is displayed. Select the name of the Excel worksheet that contains the data you want to import. The first 10 rows of your table are shown. If your table has a header row, select the
    Skip first
     row checkbox.
    note
    If your Excel workbook contains more than one worksheet with data that you want to import, you will need to repeat this procedure from Step 1 for each worksheet.
  2. Select the Edit button at the top of the Excel column that contains Client IDs, choose the Client ID from the Column Type drop-down list, then select the Save button. Repeat this for columns containing: Type (Invoice/Receipt), Gross, and Date.
  3. If the rows contain invoice data, you will also need to map the Net, VAT, Service ID, and Staff ID columns. If the rows contain receipt data, you'll need to map Bank Account and Payment Type.
  4. When you have mapped all the necessary columns in your Excel worksheet, select the Next button at the bottom of the screen.
    note
    The Next button will only be activated once you have mapped at least the required columns.

Fix pre-processing validation errors

  1. The Validation tab is displayed. If there are pre-processing errors, select the ERROR REPORT button to download a CSV file containing all the data in your worksheet, plus a new column that explains how to fix each error. If there are no errors, go to step 6 below.
    The Validation tab only displays up to 10 rows of data, so not all errors are visible. For the full list of errors, always select the ERROR REPORT button.
  2. Open the CSV file in Excel and use it to fix the validation errors.
  3. Save the file as an Excel workbook with an .xlsx file extension.
  4. Choose the Select File tab of the Leger Balance Import wizard. Select the Delete button next to the name of the existing uploaded file.
  5. Upload your new Excel workbook and follow the steps to map the columns and validate the data as above.
  6. If there are no validation errors highlighted, select the Next button.

Import the file

  1. The Import tab is displayed. Review the summary of totals for invoices, receipts and the numbers of each to be imported.
    note
    At this stage, nothing from your Excel ledger has yet been written to Onvio.
  2. When you are ready to import data from your Excel ledger, select the Finish Import button.
  3. The Import Progress table is displayed. The import status of your file will be shown as Pending. Select the Refresh button at the top right.
    note
    Depending on how many rows in your worksheet and how many are written to Onvio, the import status may take some time to change. If the status doesn't change from Pending to Complete after selecting the Refresh button, you can come back to it later by selecting Setup > Import Progress from the main menu and select Refresh again.
  4. When the Status has changed to Complete, the Summary column will show the total value of processed invoices and receipts. From here you can:
    1. Select the link in the Number of rows imported column to download a Successful Import Report as a CSV file. This contains the rows that have been successfully written to Onvio.
      note
      You will see a warning in the Successful Import Report if Onvio has had to adjust any VAT amounts in your ledger rows.
    2. Select the link in the Errors column to download an Error Report as a CSV file. This contains the rows of data that have not been written to Onvio and the details of the processing validation rule that caused each error.
  5. The Import Progress screen holds the data in the Error Report for 30 days so that you have time to review it and fix any processing errors.

Fix processsing errors

  1. Open the Error Report CSV file in Excel and reinstate the header row so that you can identify your data more easily later.
  2. Fix the processing errors. Most of these can be fixed by entering information into Onvio, such as a Staff ID or Bank Account ID.
  3. Save the CSV as an Excel workbook with an .xlsx file extension and with a different file name than the file you uploaded previously.
  4. Select Setup > Import Ledger to return to the first step of the Ledger Balance Import wizard and upload your newly saved Excel file containing the ledger rows with all processing errors fixed. Then follow the wizard through again.

View the processed transactions and allocate receipts

  1. To view processed invoices imported to Onvio, choose Time and Billing > Billing from the main menu, then select the Review tab.
  2. To view processed receipts, choose Time and Billing > Receipts and Adjustments from the main menu, then select the Review tab.
  3. Unallocated receipts are shown in the Open Amount column. Select the Edit button to open the entry and select the Applied Amounts link.
  4. Select the Save button, then Save again.

This article applies to:

  • Product: Onvio
  • Audiences: Staff