Importing Trial Balance Data
After a new engagement is created, you must import a trial balance from one of the following:
- Microsoft® Excel® xlsx file
- CSV file
- QuickBooks® Online
Excel® and CSV files are imported using the flexible Data Import Wizard - identified by steps 1 to 4 at the top of the screen.
To import from QuickBooks® Online, or a pre-formatted Excel® file, click Import from QuickBooks Online to go to the Import Trial Balance screen where these formats are supported. This link will also allow you to use the legacy Excel import function with a pre-formatted Trial Balance.
You can import up to 5,000 accounts and associated balances using either import method.
Importing groups and subgroups
If using the Data Import Wizard, you can also import financial and tax groups, and these will be created or assigned as needed. If you do not import groups in the Data Import Wizard, CCH Engagement automatically assigns the financial groups, account type, and account classification to each account if you selected to use system-provided financial groups. CCH Engagement also automatically assigns tax groups based on the tax entity type and year end date. Engagement will also automatically assign groups if you are using the Import Trial Balance screen. See Auto-assignment of groups, types, classifications.
Reimporting
After the initial import, you can import accounts, balances, and groups again. The behavior for re-importing will depend on the method of importing. See Re-importing the Trial Balance data.
Trial Balance Imports for Consolidated Engagements
For consolidated engagements, a separate trial balance must be imported for each subsidiary from the Subsidiary Setup Page. Using the Data Import Wizard, multiple subsidiaries can be imported from a single trial balance data file.
Import Procedures
Click a link below to view specific instructions for importing from different types of source files.
Import from Microsoft® Excel® or CSV files using the Data Import Wizard.
Step 1: Select file
In this step you will upload the file you wish to import from.
-
Select the Excel (.xlsx) or Comma-separated values (.csv) file you wish to import by clicking Browse files or dragging and dropping the file. Only one file can be imported at a time.
-
You may receive a warning that your Excel file upload failed. This may indicate that the file is protected. In this case, open the file in Excel, remove any protection, and save the file before uploading it again.
-
If you are uploading an Excel file, select the Worksheet for import.
-
The Data type Trial Balance should be selected.
-
Click Next to continue.
Step 2: Map
In this step, you will tell the import wizard how your data is formatted.
-
If your engagement is a consolidation:
Choose whether you want to import a Single subsidiary from this file or Multiple subsidiaries.
If importing a single subsidiary, select it from the dropdown box.
-
If your engagement has monthly periods:
Choose whether you want to import a Single period from this file or Multiple periods.
If importing a single period, select it from the dropdown box.
-
Select the option for how amounts are represented in the file – either Debits and credits are in one column or Debits and credits are in separate columns. You can view the file preview on this screen to help you determine this.
-
If you wish to import groups and sub-groups from the file, check the Yes box, if not, leave this unchecked.
-
Select the first row that contains data. This should be the first row that details an account, below any header rows.
-
At the top of each column, select what data each column relates to.
Account name: This is the name of the General Ledger account.
Account number: This is the number of the General Ledger account.
Note: Account number is not a required column, however, you should map this column for every import and reimport if the source accounting system supports it.
Account balance: This is the account balance for a specific period.
If you selected to have Debits and credits in separate columns, select Account Balance – Debits or Account Balance – Credits as appropriate for each column.
Select the year – Current year or Prior year for each account balance column.
If the engagement is a consolidation and you are importing multiple subsidiaries, select the subsidiary that each Account balance column relates to in the second row of dropdown boxes.
If the engagement has interim periods and you are importing multiple periods, select the period that each Account balance column relates to in the second row of dropdown boxes.
If you are importing groups and subgroups, select what group and subgroup data each column relates to.
Account classification description or Account classification abbreviation: This is only required if you wish to create new groups in the engagement. The account classification description or abbreviation in this column must exactly match an existing account classification in the engagement to create a new group.
These options will not be visible for Tax consolidated engagements.
Group index and/or Group name: These will be used to assign accounts to groups. To assign existing groups, only one of these columns is required. To create new groups in the engagement, both columns are required. You will also need Account classification description or Account classification abbreviation columns to create a new group.
These options will not be visible for Tax consolidated engagements.
Sub-group index and/or Sub-group name: These will be used to assign accounts to subgroups. To assign existing subgroups, only one of these columns is required. To create new subgroups, both columns are required. You will also need group name and/or group index to create new subgroups.
These options will not be visible for Tax consolidated engagements.
Tax group index and/or Tax group name: These will be used to assign accounts to tax groups. The tax group index or name in these columns must exactly match an existing tax group in the engagement for the selected entity type and tax year to assign a tax group.
These options will not be visible for Financial consolidated engagements.
If there are columns in your file that you do not wish to import, you can select Do not import, or you can leave them unselected with Please select displayed.
Click Next to continue.
Before proceeding to the next step, checks will be performed on your data. Review any messages that are shown carefully.
Notes:
Current and prior year balances are imported into unadjusted balance columns.
If your Excel file contains any formulas, the formula result will be imported. If the formula has an error, the balance will be imported as 0.
Excel files sometimes include numbers with many decimal places, which may not be visible within Excel, such as 123.0000000001. These decimals will be visible in the file preview but will be rounded to 2 decimal places on import.
Imported groups and sub-groups will be assigned based on the engagement’s default grouping list and any new groups and sub-groups will be created in the default grouping list. Importing into and assignment to custom grouping lists is not currently supported.
Importing of tax sub-groups and property numbers is not currently supported.
Step 3: Validate
In this step, your mapped data will be validated to detect potential issues before importing it into your engagement.
During the validation stage, the engagement will be locked to prevent other users from making any conflicting changes. You will have 5 minutes to progress to the next stage before the engagement is unlocked automatically, and you will need to return to the previous step to try again. If there are any errors that cannot be resolved, the engagement will also be unlocked immediately.
-
Review any Errors that cannot be resolved: These are issues with your data that mean it cannot be imported. Click Previous to check and resolve any erroneous mapping settings, or review and update your uploaded file and try again.
-
Review any Errors to resolve: These are warnings about your data. You should review these and determine whether these are acceptable. To accept these warnings and continue to import, check the Accept box, and the error will be moved to Resolved errors.
-
Review any Validation information: These are the results of other validation checks that have been performed. You should ensure they meet your expectations of the file.
-
If all errors are resolved, or there were no errors in your file, click Next to continue.
Validation checks include the following:
Errors that cannot be resolved:
-
The number of accounts exceeds the limit of 5000 supported by CCH Engagement
-
No records found in the imported file
-
Duplicated Account numbers
-
Duplicated combinations of Account numbers and Account names
-
Missing Account names
-
Missing Account numbers (if Account number is mapped)
-
Locked engagement
-
Deleted engagement
-
Another import in progress
-
New groups or sub-groups exceed the limit supported by CCH Engagement
-
Non-allowed characters in Account number, Group index or Sub-group index
Errors to be resolved:
-
Account balance data already imported
-
Debits and credits not matching
-
New groups or subgroups cannot be added due to missing columns
-
Invalid tax groups
Validation information:
-
No duplicated Account numbers
-
No duplicated Account number/Account name combinations
-
No missing Account names
-
No missing Account numbers (if Account number is mapped)
-
Accounts with a zero balance
-
Total debits equal total credits
-
All groups and sub-groups already exist in the engagement
-
Number of groups that will be created
-
Number of sub-groups that will be created
-
All tax codes are valid for the entity type and year
Step 4: Complete
In this step, your data will be prepared for loading, new groups, subgroups and accounts will be created as required, balances will be added, and groups and sub-groups will be assigned.
Your engagement will continue to be locked during this step to avoid conflicting changes and will be automatically unlocked once the import is completed.
Note: If the import process runs for more than 15 minutes, the import will be automatically canceled, and the engagement will be unlocked. The cancel button will also become enabled, and you can click this to cancel the import and unlock the engagement.
Import from Microsoft® Excel® using Import Trial Balance.
To access Import Trial Balance from the Data Import Wizard, click Import from QuickBooks Online.
To successfully import a trial balance from a Microsoft® Excel® file using Import Trial Balance, your import spreadsheet must:
- Be a Microsoft® Excel® 2013 or newer XLSX file that contains only one worksheet.
- Begin your accounts on Row 2 of the worksheet. Row 1 can be blank or used for header information. Formatting in Row 1 is ignored during the import.
- Have the following information in the first four to six columns of the worksheet, in this order:
-
Account Number (optional)
Note: If you do not use account numbers, then leave column A blank.
-
Account Name (required)
-
Current Period Debit Balance (required)
-
Current Period Credit Balance (required)
-
Prior Period Debit Balance (optional)
-
Prior Period Credit Balance (optional)
- Have only one instance of each account number and account name.
To import data from a Microsoft® Excel® file, do the following:
- Click Microsoft Excel on the import Trial Balance page. This page opens after:
- Creating an engagement, or
- Opening an engagement that has not yet been imported, or
- Clicking Import on the Subsidiary Setup page of a consolidated engagement
-
Browse to the Excel® file and click Open.
Import from QuickBooks® Online.
To access QuickBooks® Online import from Data Import Wizard, click Import from QuickBooks Online.
To import data from QuickBooks® Online, you must be either an accountant or admin user of the QuickBooks® Online company file. Keep in mind the following for these imports:
- If an account has a sub-account in QuickBooks® Online, the account and sub-account names are concatenated with a colon (:) separating them. This will prevent duplicate accounts.
- If you roll forward an engagement and then reimport, sub-accounts with no account number will reimport as new accounts. You must reassign these accounts to groups if they are not automatically assigned during the reimport.
Prior-year data cannot be imported from QuickBooks® Online. However, you can add prior-year balances into the trial balance by doing the following:
- Create the engagement for the prior year.
- Import accounts and balances from QuickBooks® Online.
- Roll forward the engagement to the next year.
- Import accounts and balances from QuickBooks® Online for the current year.
To import data from QuickBooks® Online, do the following:
- Click QuickBooks® Online on the Import Trial Balance page.
- Select Cash or Accrual for the accounting basis.
- Click Confirm.
- Enter your User ID and Password for your Intuit® account, and click Sign In.
- Select the company with the trial balance data you want to import.
- If prompted, search for or select the client you are importing data for.
- Click Next.
- Click Connect to authorize the transfer of data.
Note: If you see strange behavior using Microsoft® Internet Explorer®, switch to a browser QuickBooks® Online supports (such as Google® Chrome™). For further information, see the QuickBooks Help.