Browse

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.

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. 

Import WIP transactions

Bill your clients

Add bank accounts

Add or update staff information

Activities