Skip to main content

Search

Welcome to our Support

Importing a Trial Balance from excel/csv

Lorraine Keogh
  • Updated

We have streamlined the process of importing a Trial Balance (current and comparative figures), from a csv/excel file generated from other accounting software into BrightAccountsProduction.

We offer default mapping templates for different entity types for the following accounting applications, with additional mapping templates to be added progressively [Note: IRIS Mapping Templates (for Company/LLP/Sole Traders/Academies & Charities) are scheduled to be rolled out in our next two releases].

Current Mapping Templates exist for:

  • Sage
  • TAS
  • Xero
  • QuickBooks

You also have the option to create and add your own mapping template, if desired.

The Trial Balance Import templates are practice and entity type specific.

Prerequisites

For a successful Trial Balance import operation, the Staff Permission Add new journal must be checked.

When importing trial balance (TB) data, it's important to understand the implications, especially concerning bookkeeping licenses. If your TB includes bookkeeping transactions, the system automatically allocates a bookkeeping license to the client. To avoid this, ensure that your TB file does not include such transactions. In case you've mistakenly imported a TB with bookkeeping transactions, you must delete these transactions to change the client's status. Additionally, when filling out your trial balance file, some fields can be left empty, such as 'note' or 'reference', provided they are non-essential for your purpose. Always refer to the import guidelines or reach out for clarification if needed.

Steps

On the Trial Balance page, click TB Import.

To use one of the existing mapping templates

  • Select the application name and click Next.
  • You can customize the template to match the source CSV file, by clicking Columns & Layout.
  • In the Customize the Template for Source product window, perform the following actions, as required and click Update. 
    If you provide a date in the DATE field beyond or post the current Financial Year, it 
    will be counted as invalid and will be updated to the Financial Year End date.

    If you provide multiple date values in more than one DATE field, they will be updated 
    to first date present in the DATE field of the CSV file.
    • Include or exclude columns to match the source CSV file. If there is a discrepancy between the columns declared and the columns present in the source CSV file, there will be mapping errors and the file will not get uploaded.
    • Declare the header row(s) which from which the data will be retrieved from the source CSV file.
    • Select the Include Zero checkbox to upload nominal codes with zero or no value in the source CSV file.
    • Select the Save Source Nominal Description checkbox to replace any nominal code description present in BrightAccountsProduction with the related nominal code description present in the source CSV file.
    It is strongly recommended for the user to customize and manage the template as per their 
    requirement.
  • Click Choose File to upload the source CSV file and click Next.
  • The source CSV file nominal codes will be mapped with existing BrightAccountsProduction nominal codes. If any Bright Code field is blank, the value needs to be entered manually to map with any existing BrightAccountsProduction nominal code.
  • Click Next. The file is successfully uploaded.
  • A pop-up is displayed at the top left corner notifying the successful submission of the Journal import.
  • The pop-up once clicked displays the success message.
  • You can view the published TB journal from Journals on the left panel which is imported using the Trial Balance Import functionality. If the Include Zero option is selected while performing the import, then the nominal codes containing a zero value will be visible. If the same option is not selected while import, you can view the list comprising of valid transactions. You can also view either a Detailed or a Summary view of the Journal inputs.
  • The Chart of Accounts page displays the Annual View, Overview, Periodic View or a Comparative View. To select a defined view, click View drop-down list and select the same. This way you can have a clear understanding with a calculated and supported comparison.
  • The journal imported using the Trial Balance Import functionality also provides with a comparative study of the same with your current and previous years. You can also view the comparison for the last 3,4,5 or 6 years. To select the desired comparison year’s view, click View and select the same.

To create your own mapping template

  • Click the New Template button
  • Enter the Template Name [Note: The name cannot have gaps/spaces or special characters]
  • If you wish to create the new template from an existing Template, then select the existing template name in the Create from Template dropdown list
  • If you wish to create the new template from scratch, then do not select a template name in the Create from Template dropdown list
  • Include or exclude columns to match the source CSV file. If there is a discrepancy between the columns declared and the columns present in the source CSV file, there will be mapping errors and the file will not get uploaded
  • If your source csv has Header Row(s) i.e. rows which contain data that you do not want imported, then enter the number of those here, usually 1
  • Select the Include Zero checkbox to upload nominal codes with zero or no value in the source CSV file
  • Select the Save Source Nominal Description checkbox to replace any nominal code description present in BrightAccountsProduction with the related nominal code description present in the source CSV file
  • Then click Add

To Edit a Template

  • Select the template using the checkbox
  • then click the inline menu dropdown and select Edit
  • You can edit the template name, alter the columns to be included/exclused and the Headers Rows, to Include/Exclude Zero and whether to use the Descriptions of the sourse nominal codes or retain the default descriptions

To Edit the Template Mapping and Import a Trial Balance

  • Select the template using the checkbox
  • and click Next
  • select a csv file
  • and click Next
  • The mapping screen will appear showing the columns as per your selection in the Columns and Layout (example)
    • the code of the source software
    • the description of the code of the source software
    • the BrightAccountsProduction code field, which you can enter or search & select using the magnifying glass
    • the BrightAccountsProduction Description, which will display automatically (if you have not selected to retain the source nominal code description)
    • the Current and Comparative year balance columns
  • Once all the source code have a BrightAccountsProduction code, then you can select Next
  • the system will then validate all the data and queue it for importing
  • a Red notification icon will appear when the import is complete
  • and when you go to Nominal Ledger>Journals, you will be able to see imported Trial Balance